Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article describes the differences between Dataverse SQL and Transact-SQL. Dataverse SQL is a subset of Transact-SQL.
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)
- binary
- image
- sql_variant
- timestamp
- varbinary
- virtual
- partylist
- HierarchyId
- managedproperty
- file
- xml
- multiselectpicklist
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
- DDL
- DML
- TCL
- SCS
- DCL
- Stored Procedure
- DQL
- DQL XML function
- DQL JSON function
- CUBE and ROLLUP
- Duplicate columns
- Without unique column name and alias
- General
- WHILE LOOP
- IF THEN ELSE
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?
- ODBC Scalar
- Analytic
- Cryptographic
- Cursor
- Data type
- IDENT_CURRENT
- IDENT_INCR
- IDENT_SEED
- IDENTITY(Function)
- SQL_VARIANT_PROPERTY
- JSON
- Ranking
- Replication
- Rowset
- Security
- System Statistical
- Text & Image
- Trigger
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)
- $PARTITION
- @@ERROR
- @@IDENTITY
- @@PACK_RECEIVED
- @@TRANCOUNT
- BINARY_CHECKSUM
- CHECKSUM
- CONNECTIONPROPERTY
- CONTEXT_INFO
- CURRENT_REQUEST_ID
- CURRENT_TRANSACTION_ID
- DECOMPRESS
- ERROR_LINE
- ERROR_MESSAGE
- ERROR_NUMBER
- ERROR_PROCEDURE
- ERROR_SEVERITY
- ERROR_STATE
- GET_FILESTREAM_TRANSACTION_CONTEXT
- HOST_ID
- HOST_NAME
- MIN_ACTIVE_ROWVERSION
- SESSION_CONTEXT
- SESSION_ID
- XACT_STATE
The following scalar functions return information about the environment and environment objects.
More information: Metadata Functions (Transact-SQL)
- @@PROCID
- APP_NAME
- APPLOCK_MODE
- APPLOCK_TEST
- ASSEMBLYPROPERTY
- COL_LENGTH
- COL_NAME
- COLUMNPROPERTY
- DATABASEPROPERTYEX
- DB_ID
- DB_NAME
- FILE_ID
- FILE_IDEX
- FILE_NAME
- FILEGROUP_ID
- FILEGROUP_NAME
- FILEGROUPPROPERTY
- FILEPROPERTY
- FULLTEXTCATALOGPROPERTY
- FULLTEXTSERVICEPROPERTY
- INDEX_COL
- INDEXKEY_PROPERTY
- INDEXPROPERTY
- NEXT VALUE FOR
- OBJECT_DEFINITION
- OBJECT_ID
- OBJECT_NAME
- OBJECT_SCHEMA_NAME
- OBJECTPROPERTY
- OBJECTPROPERTYEX
- ORIGINAL_DB_NAME
- PARSENAME
- SCHEMA_ID
- SCHEMA_NAME
- SCOPE_IDENTITY
- SERVERPROPERTY
- STATS_DATE
- TYPE_ID
- TYPE_NAME
- TYPEPROPERTY
- VERSION
The Dataverse SQL endpoint supports the following language elements.
More information: Language Elements (Transact-SQL)
- Control-of-Flow
- Transactions
- EXECUTE
- RAISERROR
- CHECKPOINT
- KILL
- KILL QUERY NOTIFICATION SUBSCRIPTION
- KILL STATS JOB
- RECONFIGURE
- SHUTDOWN
- USE
Use these statements to query data from the Dataverse SQL endpoint.
More information: Queries
- AT TIME ZONE
- OPTION Clause
- OUTPUT Clause
- PREDICT
- READTEXT
- Table Value Constructor
- UPDATETEXT
- WITH common_table_expression
- WRITETEXT
Retrieves rows from a Dataverse environment and enables the selection of one or many rows or columns from one or many tables.
- FOR
- INTO
- OVER
- GROUP BY ROLLUP
- GROUP BY CUBE ( )
- GROUP BY GROUPING SETS ( )
- GROUP BY ()
- GROUP BY [ ALL ] column-expression [ ,...n ]
- WITH (DISTRIBUTED_AGG)
- PIVOT
- UNPIVOT
Hints aren't supported.
- FREETEXT