Edit

How Dataverse SQL differs from Transact-SQL

  • Article

This article describes the differences between Dataverse SQL and Transact-SQL. Dataverse SQL is a subset of Transact-SQL.

Data types

In a 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)

  • bigint
  • bit
  • char
  • datetime
  • decimal
  • float
  • int
  • money
  • nchar
  • numeric
  • nvarchar*
  • nvarchar(max)      # multi-line text
  • real
  • smalldatetime
  • smallint
  • smallmoney
  • tinyint
  • uniqueidentifier
  • varchar*
  • picklist
  • lookup
  • primarykey
  • customer
  • owner
  • state
  • status

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 compiles 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
    • IF THEN ELSE
    • 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?

  • Aggregate
  • Collation
  • Configuration
  • Conversion
  • Data type
    • DATALENGTH
  • Date & time
  • Mathematical
  • Logical
  • Metadata
  • String
  • System

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)

Language elements General

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 are not supported.

Predicates

  • CONTAINS
  • IS NULL

See also

Use SQL to query data