If you have been working with Microsoft Dataverse for a while, querying data usually means reaching for one of a few familiar options.
You might use FetchXML when building more complex Dataverse queries. You might use OData when working directly with the Dataverse Web API. And depending on the scenario, you may also have used the Dataverse TDS endpoint to explore data using SQL-based tools.
But there is now another interesting option for developers working directly with the Dataverse Web API:
You can send a SQL SELECT query through the Dataverse Web API itself.
Yes, SQL inside a Web API request.
That immediately caught my attention because SQL is probably one of the most familiar query languages for developers. Instead of translating a requirement into $select, $filter, $expand, and other OData constructs, some queries can now be expressed using familiar SQL syntax.
Before getting too excited, though, there is an important distinction.
This isn’t SQL Server sitting behind a Dataverse API endpoint, and it doesn’t mean we suddenly have unrestricted SQL access to Dataverse.
Microsoft describes this capability as a read-only SQL interface supporting a subset of T-SQL.
That distinction is important, and throughout this series I want to explore not only what we can do with it, but also where its boundaries are.
What Does SQL Through the Dataverse Web API Mean?
Let’s start with a simple requirement.
Imagine that we want to retrieve an Account called Fourth Coffee.
In SQL, we might naturally write:
SELECT name
FROM account AS a
WHERE a.name LIKE 'Fourth Coffee'
With the Dataverse Web API, the SQL statement can be passed using the sql query option against an entity-set resource.
For the Account table, the entity set is accounts, so conceptually the request looks like this:
GET [Organization URI]/api/data/v9.2/accounts?sql=<SQL QUERY>
Once the SQL has been URL encoded, Microsoft’s example becomes:
GET [Organization URI]/api/data/v9.2/accounts?sql=SELECT%20name%20%0D%0AFROM%20account%20AS%20a%20%0D%0AWHERE%20a.name%20LIKE%20'Fourth%20Coffee' HTTP/1.1
Authorization: Bearer [REDACTED]
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="*"
Accept: application/json
There are two things worth noticing here.
The endpoint is still the familiar Dataverse Web API:
/api/data/v9.2/
And the resource being addressed is still the Account entity set:
accounts
The difference is that our query logic is being supplied using:
?sql=
followed by the SQL SELECT statement.
That means we’re not connecting directly to SQL Server. We are still communicating with Dataverse through its Web API; SQL is being used as the query language for the request.
How Does This Compare with OData?
This is where the feature starts becoming particularly interesting.
Microsoft shows that the SQL query:
SELECT name
FROM account AS a
WHERE a.name LIKE 'Fourth Coffee'
produces a response similar to the equivalent OData query:
/accounts?$select=name&$filter=contains(name,'Fourth Coffee')
Both approaches are asking Dataverse for essentially the same information.
The difference is how we express the request.
With OData, we think in terms of query options such as:
$select$filter
With SQL, the same requirement can be expressed using concepts many developers already understand:
SELECT
FROM
WHERE
That doesn’t automatically make SQL better than OData.
It simply gives us another way of expressing a Dataverse query.
And that is how I think we should approach this capability: another query option in the Dataverse developer toolbox, rather than a replacement for everything we already use.
What Comes Back from Dataverse?
The response remains what you would expect from the Dataverse Web API.
For Microsoft’s Fourth Coffee example, the response contains JSON similar to:
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountid)",
"@Microsoft.Dynamics.CRM.totalrecordcount": -1,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
"@Microsoft.Dynamics.CRM.globalmetadataversion": "173309522",
"value": [
{
"@odata.etag": "W/\"173325408\"",
"accountid": "0bdd4472-981d-f111-8341-0022482aa957",
"name": "Fourth Coffee"
}
]
}
This is another important point.
We’re using SQL to describe the query, but we’re still working with the Dataverse Web API and its response model.
For developers already building integrations against the Web API, that makes this capability particularly interesting because it isn’t introducing an entirely separate data-access mechanism.
So… How Much SQL Do We Actually Get?
This is where we need to control our expectations.
Dataverse supports a subset of T-SQL, not the complete SQL language.
There is still quite a lot available.
We can use selections such as:
SELECT
SELECT DISTINCT
SELECT TOP N
The documented TOP range is between 0 and 5000.
We can work with multiple tables using:
INNER JOIN
LEFT JOIN
Filtering supports operators and constructs including:
=
!=
>
<
>=
<=
LIKE
IN
NOT IN
IS NULL
IS NOT NULL
BETWEEN
AND
OR
We can also perform grouping and aggregation using:
GROUP BY
COUNT(*)
SUM()
AVG()
MIN()
MAX()
And sorting is available using:
ORDER BY
So although this is intentionally restricted SQL, there is enough here to build some genuinely useful Dataverse queries.
But This Is Not SQL Server
This is probably the most important thing to understand before starting to experiment.
Seeing SQL syntax can naturally make us think:
“Great. I’ll just write the same SQL I normally write.”
That assumption will quickly cause problems.
For example, one of the first things many developers instinctively write is:
SELECT *
FROM account
That won’t work here.
SELECT * isn’t supported.
Instead, you must explicitly specify the columns you want:
SELECT name, telephone1, websiteurl
FROM account
This is actually a good practice for APIs anyway. If your application only needs three columns, retrieving every available column simply transfers data you aren’t going to use.
There are much bigger restrictions as well.
Dataverse SQL doesn’t support features such as:
Subqueries
CTEs
HAVING
UNION
RIGHT JOIN
FULL JOIN
CROSS JOIN
CASE
COALESCE
Window functions
And this isn’t a mechanism for modifying Dataverse data using SQL.
Statements such as:
INSERT
UPDATE
DELETE
ALTER TABLE
aren’t supported.
The interface is read-only.
So something like this isn’t what this capability is designed for:
UPDATE account
SET name = 'New Account Name'
WHERE accountid = '...'
For modifications, we continue using the appropriate Dataverse APIs and operations.
Only One SELECT Statement
There is another SQL habit worth mentioning.
You can’t send multiple SQL statements and expect multiple result sets.
For example:
SELECT name
FROM account;
SELECT fullname
FROM contact;
isn’t supported.
Each command must contain a single SELECT statement.
Again, thinking of this as a Dataverse query language rather than unrestricted SQL access makes these restrictions much easier to understand.
Where Things Start Getting More Interesting
A basic SELECT isn’t necessarily enough to make developers rethink how they query Dataverse.
The more interesting capabilities start appearing when relationships are involved.
For example, Dataverse SQL supports INNER JOIN and LEFT JOIN.
That means a query such as this is possible:
SELECT
a.name,
c.fullname,
c.emailaddress1
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
Now we are retrieving Accounts together with their related Contacts using familiar SQL relationship syntax.
Multiple tables can also be joined.
For example:
SELECT
a.name,
c.fullname,
o.name AS opportunity_name
FROM account AS a
INNER JOIN contact AS c
ON a.accountid = c.parentcustomerid
INNER JOIN opportunity AS o
ON a.accountid = o.customerid
For developers accustomed to relational databases, this syntax is immediately understandable.
And this is where I think the capability becomes much more interesting than the headline:
“Dataverse supports SQL.”
The real question becomes:
What kinds of Dataverse queries become easier to express when SQL is available as another query language?
SQL, FetchXML or OData?
That naturally brings us to the bigger architectural question.
We already have mature ways of querying Dataverse.
FetchXML isn’t going anywhere.
OData isn’t going anywhere.
And SQL doesn’t suddenly make those approaches obsolete.
Instead, we now have another option.
A developer working with Dataverse may increasingly need to think about a requirement and decide:
Requirement | +---- FetchXML | +---- OData | +---- SQL
Which one makes sense depends on what we’re trying to achieve and what each query mechanism supports.
For someone already comfortable with SQL, the ability to express filtering, joins and aggregations using familiar SQL syntax could make certain Web API queries easier to understand.
But familiarity shouldn’t be confused with capability.
Dataverse SQL deliberately supports only part of T-SQL, and there are important restrictions around joins, filters, expressions, functions, paging and aggregation that developers need to understand before using it seriously.
