[object Object]

Virtual tables let Dataverse expose external data as if it were native rows. No copy, no sync job, no storage cost. They look like a magic bullet for integration. They are not.

What they actually do

A virtual table maps to a data provider that implements the RetrieveMultiple, Retrieve, Create, Update, and Delete operations. Microsoft ships providers for OData v4, SQL Server, and now SharePoint. You can write a custom provider in C# for anything else. Dataverse calls the provider live whenever a query hits the virtual table.

The killer feature: live data, native UX

A virtual table appears in views, advanced find, model-driven forms, and the Web API just like a native table. Users do not know the data lives elsewhere. For a “show recent ERP invoices on the account form” use case where invoices change minute by minute, this is unbeatable.

The killer limitation: no joins to native tables

You cannot write a single FetchXML query that joins a virtual table to a native table. The Dataverse query engine cannot push a join across the provider boundary. Workarounds: subquery on the native side, denormalize the join key onto both tables, or accept that the lookup column on a virtual table is for display only.

Latency is the user’s problem now

Every form load that includes a virtual table triggers a live external call. If your ERP responds in 800ms on a good day and 4 seconds on a bad day, your account form behaves the same way. Add a server-side cache layer in your provider or accept the variance.

When virtual tables win

  • Reference data with low write volume (countries, product catalog).
  • Data the user sees but never writes (read-only ERP transactions).
  • Data that changes too fast for a sync to keep up (live inventory).

When to avoid them

  • Data your users will filter or sort large result sets on. The provider will not be smart enough.
  • Data that needs to participate in plugins or workflows on the native side.
  • Data subject to row-level security in the source. Virtual table providers cannot easily map external ACLs to Dataverse security roles.

Custom provider tips

Implement paging correctly. Return a PagingCookie that your provider can decode. Skip this and Dataverse will request 5,000 rows on every query.

public override EntityCollection RetrieveMultiple(...) {
    var cookie = ParseCookie(query.PageInfo.PagingCookie);
    var result = ExternalApi.Get(cookie.Skip, query.PageInfo.Count);
    return new EntityCollection(result.Rows) {
        MoreRecords = result.HasMore,
        PagingCookie = SerializeCookie(cookie.Skip + result.Rows.Count)
    };
}

What to do this week

If you have inherited virtual tables, profile every form that contains one. The slowest form on your CRM is probably the one with a virtual lookup that is making a sequential external call per row.

[object Object]
Share