Virtual Table in Dataverse

Virtual tables in Dataverse provide a way to access external data in real-time without storing it in Dataverse. They allow external data sources, such as SQL Server, SharePoint, etc to be represented as native Dataverse tables.

Key Features of Virtual Tables in Dataverse

No Data Duplication: Data remains in the external system while being accessible in Dataverse

Real-Time Access: The data is retrieved dynamically whenever accessed

Standard Dataverse Features: Virtual tables behave like regular Dataverse tables, allowing CRUD.

Improved Integration: No need for custom integrations, reducing development effort.

How Virtual Tables Work in Dataverse

Virtual tables in Dataverse act as a bridge between external data sources and Dataverse, allowing data to be accessed in real time without being stored in Dataverse. They use data providers to communicate with external systems and present the data as native Dataverse tables.

Core Components of a Virtual Table

Data Provider

  A plug-in that handles communication between Dataverse and the external system.

  It includes logic to fetch, create, update, and delete data

 Example: OData v4 Data Provider, SQL Data Provider

Data Source Row

The data source is a table row in Dataverse, which includes metadata that represents the schema of the connection parameters

Virtual Table

A Dataverse table that acts as a representation of the external data

It maps fields from the external source into Dataverse

Each virtual table is linked to a data source that determines how the data is fetched

Scenario: I have a table in an Azure SQL Database that I will integrate with Dataverse. After creating the Dataverse virtual table, I will first perform CRUD operations on the source table in SQL and then on the destination table in the Dataverse virtual table. Any updates from either side will sync data between the source and destination

Steps to Create a Virtual Table in Dataverse and Connect to Azure SQL Database

Navigate to Power Apps → Solutions

Tables->New->Table->Virtual Table

Build Connection with SQL Server

In the Table creation screen, select Connect to an external data source.

Choose SQL Server as the data source.

Click on New Connection.

Configure the SQL Server Connection

Provide the following details

SQL Server Name

Database Name

Authentication Type

SQL Server Authentication → Enter Username & Password

Click Create.

After successful authentication, the connection to Azure SQL will be established

Select the Table from SQL Server

Choose the table from your SQL Database.

Enter a Virtual Table Name (e.g., ).

Define the Primary Name Column (e.g., ).

Map Dataverse Fields to SQL Table Columns

Add columns that match the SQL table structure.

Ensure each Dataverse column maps to a corresponding SQL column

Validate the Mappings

Click Finish to create the virtual table

The Virtual Table will now appear in Dataverse, showing real-time data from SQL Server.

Performing CRUD Operations in SQL Table and Reflecting in Dataverse Virtual Table

Adding a Row in the Source (SQL) Table

If you insert a new row into the SQL table, the Dataverse Virtual Table will immediately reflect the change.

Delete a row in SQL table whose id is 3 and refresh the dataverse table will reflect latest data

Performing CRUD Operations in Dataverse Virtual Table and Reflecting Changes in Azure SQL Table

when you add a new row in the Dataverse Virtual Table, it will be inserted into the SQL table.

When a row is deleted in Dataverse, it will also be deleted from SQL Server.

Adding duplicate record from dataverse throw error as unique name constraint in in SQL table

Common Use Cases

Integrating with SQL Server : Using SQL tables in Dataverse as if they were native tables.

Accessing External ERP Data: Fetching real-time inventory from an ERP system into Dataverse.

Limitations of virtual tables

By default, virtual tables contain only a Name and Id column. No other system managed columns, such as Status or Created On/Modified On are supported

Virtual tables don't support custom columns with the Currency, Image, or Customer data types

Only organization-owned tables are supported. The security filtering applied to user-owned tables isn't supported. Access to the virtual table data can be turned on or off for individual users based on their security role. Field-level security isn't supported.

Charts and dashboards aren't supported for virtual tables

Once created, a virtual table can't be changed to be a standard (nonvirtual) table. The reverse is also true whereas a standard table can't be converted into a virtual table.

A virtual table can't represent an activity and don't support business process flows.

A column on a virtual table can't be calculated or rollup.  Any desired calculations must be done on the external side, possibly within or directed by the data provider

Many features that affect table rows cannot be enabled with virtual tables. Examples include queues, knowledge management, SLAs, duplicate detection, change tracking, mobile offline capability, column security, Dataverse search, and Power Pages solutions.