How Dataverse SQL differs from Transact-SQL
This article describes the differences between Dataverse SQL and Transact-SQL. Dataverse SQL is a subset of Transact-SQL.
Data types
In an SQL database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
More information: Data types (Transact-SQL)
Statements
A SQL statement is an atomic unit of work and either completely succeeds or completely fails. A SQL statement is a set of instruction that consists of identifiers, parameters, variables, names, data types, and SQL reserved words that compile successfully.
More information: Transact-SQL statements
- DQL
- SELECT column
- SELECT expression
- SELECT STAR
- SELECT distinct
- SELECT TOP
- SELECT SET Variable
- All JOIN types
- All WHERE conditions
- All nested queries (SELECT, FROM, WHERE)
- Union
- PIVOT and UNPIVOT
- GROUP BY/Having
- General
- DECLARE variable
Functions
Learn about the categories of built-in functions you can use with Dataverse environments through the SQL endpoint.
More information: What are the SQL database functions?
System functions
The following system functions perform operations on and return information about values, objects, and settings in the Dataverse environment.
More information: System Functions (Transact-SQL)
- @@ROWCOUNT
- FORMATMESSAGE
- GETANSINULL
- ISNULL
- ISNUMERIC
- NEWID
- NEWSEQUENTIALID
- ROWCOUNT_BIG
Metadata functions
The following scalar functions return information about the environment and environment objects.
More information: Metadata Functions (Transact-SQL)
No metadata functions are supported at this time.
Language elements
The Dataverse SQL endpoint supports the following language elements.
More information: Language Elements (Transact-SQL)
- Expressions
- General
- Variables
Language elements General
- Reserved Keywords
- Syntax Conventions
- -- (Comment)
- Slash Star (Block Comment)
- NULL and UNKNOWN
- Backslash (Line Continuation)
- GO
Queries
Use these statements to query data from the Dataverse SQL endpoint.
More information: Queries
General
- Search Condition
- TOP
SELECT
Retrieves rows from a Dataverse environment and enables the selection of one or many rows or columns from one or many tables.
- General (SELECT and SELECT Clause)
- GROUP BY
- HAVING
- ORDER BY
SELECT GROUP BY
- GROUP BY column-expression [ ,...n ]
FROM plus JOIN, APPLY, PIVOT
- JOIN
- APPLY
WHERE
- WHERE
- MATCH
Hints
Hints aren't supported.
Predicates
- CONTAINS
- IS NULL