{"id":46215,"date":"2026-09-21T18:16:04","date_gmt":"2026-09-21T12:46:04","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=46215"},"modified":"2026-09-21T18:27:27","modified_gmt":"2026-09-21T12:57:27","slug":"sql-meets-dataverse-web-api-query-microsoft-dataverse-data-using-sql","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2026\/09\/sql-meets-dataverse-web-api-query-microsoft-dataverse-data-using-sql\/","title":{"rendered":"SQL Meets Dataverse Web API: Query Microsoft Dataverse Data Using SQL"},"content":{"rendered":"

\"SQL<\/p>\n

Microsoft Dataverse provides several ways to retrieve data, including\u00a0 OData and FetchXML<\/strong>. While these approaches are well suited to Dataverse, developers coming from a relational database background may naturally think in terms of SQL.<\/p>\n

In this article, we will look at how Dataverse data querying traditionally works, what SQL querying through the Web API introduces, how it changes the developer experience, and where this approach can be useful.<\/p>\n

Previously: Querying Dataverse Data<\/strong><\/p>\n

Below are the examples of the how developers are retrieving the data from the Dataverse using the OData Query<\/strong> and FetchXML<\/strong><\/p>\n

OData Query: <\/strong><\/p>\n

 GET \/api\/data\/v9.2\/accounts?$select=name, telephone1&$filter=statecode eq 0<\/pre>\n
FetchXML:<\/strong><\/p>\n
<fetch>\r\n\r\n<entity name=\"account\">\r\n\r\n<attribute name=\"name\" \/>\r\n\r\n<attribute name=\"telephone1\" \/>\r\n\r\n<filter>\r\n\r\n<condition attribute=\"statecode\"\r\n\r\noperator=\"eq\"\r\n\r\nvalue=\"0\" \/>\r\n\r\n<\/filter>\r\n\r\n<\/entity>\r\n\r\n<\/fetch><\/pre>\n
Now: SQL Querying Through the Dataverse Web API<\/strong><\/p>\n
With SQL querying through the Dataverse Web API, developers can pass supported SQL SELECT statements through the sql <\/strong>query option of a Dataverse Web API entity set. Dataverse processes the supported SQL syntax and returns the results as JSON.<\/p>\n
For example, an Account query can be written using familiar SQL syntax<\/strong>:<\/p>\n
SELECT name, telephone1, websiteurl<\/p>\n
FROM account<\/p>\n
The query can then be sent through the Dataverse Web API:<\/p>\n
GET https:\/\/<organization>.crm.dynamics.com\/api\/data\/v9.2\/accounts?sql=<encoded<\/strong> SQL query><\/strong><\/p>\n
Here, you can think how to encode the SQL query, <\/strong>but not to worry when the query sent through the Dataverse Web API then SQL query<\/strong> is automatically encoded, check the below image.<\/p>\n
\"Query<\/p>\n
Hands-On Demonstration<\/strong><\/p>\n
In this section, we will demonstrate how supported SQL queries can be executed against Microsoft Dataverse through the Web API. The perquisite is you should be login to your environment, and you require any OOB \/ Custom entities for executing your SQL queries for retrieving the data.
\nBut before writing the SQL queries you should check the below mentioned table.<\/p>\n
Note:<\/strong> Dataverse Support Only Below listed SQL Features.<\/p>\n\n\n\n\n\n\n\n\n
Feature<\/strong><\/td>\nSupported syntax<\/strong><\/td>\n<\/tr>\n
Select<\/strong><\/td>\nSELECT, SELECT DISTINCT, SELECT TOP N (0\u20135000)<\/td>\n<\/tr>\n
Joins<\/strong><\/td>\nINNER JOIN, LEFT JOIN (multi-table)<\/td>\n<\/tr>\n
Filtering<\/strong><\/td>\nWHERE with =, !=, >, <, >=, <=, LIKE, IN, NOT IN, IS NULL, IS NOT NULL, BETWEEN, AND, OR, nested parentheses<\/td>\n<\/tr>\n
Grouping and aggregation<\/strong><\/td>\nGROUP BY, COUNT(*), SUM(), AVG(), MIN(), MAX()<\/td>\n<\/tr>\n
Sorting and paging<\/strong><\/td>\nORDER BY [ASC\\|DESC], OFFSET n ROWS FETCH NEXT m ROWS ONLY<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Table: Supported SQL Feature<\/em><\/p>\n
For the demonstration of the writing of the SQL queries we are directly writing the SQL queries in the sql\u00a0 <\/strong>query parameter in the URL, which is provided by the Dataverse Web Api. Below is the Example.<\/p>\n
\"Query Basic Select Query<\/strong> Retrieve selected columns from the Dataverse Account table using SQL through the Web API. Sql Query \/ Request:<\/strong> SELECT name, telephone1, websiteurl FROM account \"Query \"Query Output \/ Response:<\/strong> \"Query \"Query Figure : Retrieving selected Account columns using SQL through the Dataverse Web API.<\/em><\/p>\n
INNER JOIN \u2013 Account + Contact<\/strong><\/p>\n
Retrieve Account information together with its related Contacts.<\/p>\n
Sql Query \/ Request:<\/strong><\/p>\n
SELECT\r\n\r\na.name AS account_name,\r\n\r\nc.fullname AS contact_name,\r\n\r\nc.emailaddress1\r\n\r\nFROM account AS a\r\n\r\nINNER JOIN contact AS c\r\n\r\nON a.accountid = c.parentcustomerid<\/pre>\n
\"Query \"Query Output \/ Respone:<\/strong> \"Query \"Query<\/p>\n
Figure : Retrieving Account Information Together with its related contacts.<\/em><\/p>\n
GROUP BY + COUNT<\/strong><\/h3>\n
Determine how many Contacts are associated with each Account.<\/p>\n
Sql Query \/ Request: <\/strong><\/p>\n
SELECT\r\n\r\na.name,\r\n\r\nCOUNT(*) AS contact_count\r\n\r\nFROM account AS a\r\n\r\nINNER JOIN contact AS c\r\n\r\nON a.accountid = c.parentcustomerid\r\n\r\nGROUP BY a.name\r\n\r\nORDER BY a.name<\/pre>\n
\"Query\"Query Output \/ Respone:<\/strong> \"Query<\/p>\n
Figure : <\/em>Determine how many Contacts are associated with each Account.<\/em><\/p>\n
ORDER BY \u2013 Sorting Results\r\n\r\nHere we demonstrate sorting by name on the account entity.\r\n\r\nSql Query \/ Request:<\/strong>\r\n\r\nSELECT\r\n\r\nname,\r\n\r\ntelephone1\r\n\r\nFROM account\r\n\r\nORDER BY name ASC<\/pre>\n
\"Query \"Query Output \/ Respone:<\/strong> \"Query Figure : <\/em>Sorting Account records using ORDER BY.<\/em>\u00a0<\/em><\/p>\n
Limitation \/ Unsupported SQL Features<\/strong><\/h3>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Unsupported Feature<\/strong><\/td>\nExample<\/strong><\/td>\nAlternative \/ Recommendation<\/strong><\/td>\n<\/tr>\n
SELECT *<\/strong><\/td>\nSELECT * FROM account<\/td>\nExplicitly specify the columns you need<\/td>\n<\/tr>\n
Subqueries<\/strong><\/td>\nWHERE accountid IN (SELECT …)<\/td>\nUse supported JOIN operations or separate queries<\/td>\n<\/tr>\n
CTEs<\/strong><\/td>\nWITH Accounts AS (…)<\/td>\nRewrite using supported SELECT and JOIN syntax<\/td>\n<\/tr>\n
HAVING<\/strong><\/td>\nHAVING COUNT(*) > 5<\/td>\nApply filtering using WHERE before aggregation<\/td>\n<\/tr>\n
UNION<\/strong><\/td>\nSELECT … UNION SELECT …<\/td>\nExecute separate supported queries<\/td>\n<\/tr>\n
RIGHT JOIN<\/strong><\/td>\nRIGHT JOIN contact …<\/td>\nUse LEFT JOIN by changing the query direction where appropriate<\/td>\n<\/tr>\n
FULL JOIN<\/strong><\/td>\nFULL JOIN …<\/td>\nUse separate queries or supported joins<\/td>\n<\/tr>\n
CROSS JOIN<\/strong><\/td>\nCROSS JOIN …<\/td>\nRedesign the query using supported relationships<\/td>\n<\/tr>\n
CASE<\/strong><\/td>\nCASE WHEN … END<\/td>\nHandle conditional presentation logic in the application layer<\/td>\n<\/tr>\n
COALESCE<\/strong><\/td>\nCOALESCE(name, ‘Unknown’)<\/td>\nHandle null values in application code<\/td>\n<\/tr>\n
Window functions<\/strong><\/td>\nROW_NUMBER(), RANK()<\/td>\nProcess the result in the application layer<\/td>\n<\/tr>\n
Non-SELECT statements<\/strong><\/td>\nINSERT, UPDATE, DELETE, ALTER TABLE<\/td>\nUse Dataverse Web API CRUD operations<\/td>\n<\/tr>\n
Unsupported functions<\/strong><\/td>\nUnsupported string\/date\/math functions<\/td>\nUse only functions documented as supported<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Table : Unsupported SQL Featuers <\/em><\/p>\n
Although SQL querying through the Dataverse Web API provides a familiar SQL-based syntax, it is not a full SQL Server implementation<\/strong>. Dataverse supports a defined subset of SQL primarily for querying data. Developers should therefore validate the supported syntax before converting an existing SQL Server query. Features such as subqueries, CTEs, HAVING, UNION, unsupported joins, window functions, and non-SELECT statements<\/strong> are not supported.<\/p>\n
Conclusion<\/strong><\/p>\n
Microsoft Dataverse provides developers with multiple ways to retrieve data through the Web API. SQL querying adds another option by allowing developers to express supported queries using familiar SQL syntax. This can be particularly useful when working with multiple related tables, filtering data, and performing aggregation-based queries.<\/p>\n
FAQs<\/strong><\/h3>\n
What is SQL querying in Microsoft Dataverse?<\/span><\/strong>
\nSQL querying in Microsoft Dataverse allows developers to retrieve data using supported SQL syntax<\/span><\/strong> through the Dataverse Web API<\/span><\/strong>.<\/span><\/p>\n
How can SQL be used with the Dataverse Web API?<\/span><\/strong>
\nDevelopers can pass supported SQL queries<\/span><\/strong> through the sql query option<\/span><\/strong> of a Dataverse Web API entity set to retrieve Dataverse data.<\/span><\/p>\n
What SQL features are supported by Dataverse?<\/span><\/strong>
\nDataverse supports several SQL features, including SELECT, JOIN, WHERE, GROUP BY, aggregation, ORDER BY, and paging<\/span><\/strong>.<\/span><\/p>\n
Is SQL fully supported in the Dataverse Web API?<\/span><\/strong>
\nNo. The Dataverse Web API<\/span><\/strong> supports a defined subset of SQL for querying data and does not provide full SQL Server functionality.<\/span><\/p>\n
When can SQL querying be useful in Dataverse?<\/span><\/strong>
\nSQL querying can be useful when developers want to retrieve and work with Dataverse data<\/span><\/strong> using familiar SQL syntax<\/span><\/strong>, particularly for related data, filtering, and aggregation.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Microsoft Dataverse provides several ways to retrieve data, including\u00a0 OData and FetchXML. While these approaches are well suited to Dataverse, developers coming from a relational database background may naturally think in terms of SQL. In this article, we will look at how Dataverse data querying traditionally works, what SQL querying through the Web API introduces,\u2026 Read More: SQL Meets Dataverse Web API: Query Microsoft Dataverse Data Using\u2026 »<\/a><\/span><\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2354,2361],"tags":[3430],"class_list":["post-46215","post","type-post","status-publish","format-standard","hentry","category-dataverse","category-technical","tag-sql-meets-dataverse-web-api"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t