Supported SQL
editThe query engine translates the SELECT statements of a request to LINQ queries, and runs the INSERT, UPDATE and DELETE statements with the handlers of their query root. It does not run other statements. This page lists what it translates; anything else returns the error 50004 with a message that names the problem.
Statements and clauses
- One or more
SELECT,INSERT,UPDATEandDELETEstatements, separated by;or not. EachSELECTreturns a result set, in order: read them withSqlDataReader.NextResult, or fill aDataSet. All the statements are translated before the first one runs, so an invalid statement returns an error, no result set, and changes nothing. Parameters are shared by all the statements. See INSERT, UPDATE and DELETE. - Each statement can start with common table expressions (
WITH … AS (…)), including column lists. They can be recursive: see Recursive common table expressions. TOP,DISTINCT,SELECT *on a single table, and selected columns and expressions with aliases.FROMa query root or a derived table (FROM (SELECT …) AS t).INNER JOIN,LEFT JOINandRIGHT JOIN, on query roots or derived tables.CROSS APPLYover thenodes()method of an XML column.WHEREwith comparisons,AND,OR,NOT,IS NULL,IS NOT NULL,INandNOT IN(value lists and simple subqueries, which can reference the outer query),EXISTSandNOT EXISTS(simple subqueries, which can reference the outer query), andLIKEandNOT LIKE(see LIKE).GROUP BYon one or more columns,HAVING, and the aggregatesCOUNT(*),SUM,MIN,MAXandAVG.- Scalar subqueries, in the selected columns and in
WHERE, which can reference the outer query. See Scalar subqueries. ORDER BY, withOFFSET … FETCH.UNIONandUNION ALL.- Parameters (
@name), sent by the client with the command.
Expressions and functions
- Arithmetic operators:
+,-,*,/,%. - String concatenation:
+,||andCONCAT. - String functions:
UPPER,LOWER,LEN,LTRIM,RTRIM,TRIM,LEFT,RIGHT,SUBSTRING,REPLACE,TRANSLATE,STUFF,STRING_ESCAPE,FORMAT. NULLand conditional functions:ISNULL,COALESCE,NULLIF,IIF(its condition can useINandEXISTSsubqueries),CHOOSE.- Conversions:
CAST,CONVERT,TRY_CAST,TRY_CONVERT, including casts to typed and untypedxml. - Dates:
GETDATE,SYSDATETIME,DATEADD,DATEDIFF,EOMONTH,YEAR,MONTH,DAY. - Mathematics:
ABS,ROUND,CEILING,FLOOR,POWER,SQRT,EXP,LOG,SIN,COS,TAN,ASIN,ACOS,ATAN,ATN2,COT. - JSON:
ISJSON,JSON_VALUE,JSON_PATH_EXISTS,JSON_QUERY, with thelaxandstrictpath modes. - XML methods:
.query(),.value(),.exist()and.nodes().
UPPER and LOWER use the invariant culture by default. To change the translation of a function, see Run the queries with your provider.
NULL and comparisons
The engine follows the three-valued logic of SQL:
- A comparison with
NULLis never true:<>andNOTdo not match rows where the value isNULL. For instance,WHERE NOT (Name = 'a')excludes the rows whereNameisNULL, as SQL Server does. NOT INover a list that containsNULLmatches no row.UPPER,LOWER,LEN,LTRIM,RTRIM,TRIM,LEFT,RIGHTandREPLACEreturnNULLfor aNULLargument.ROUNDrounds halfway values away from zero:ROUND(2.5, 0)is 3 andROUND(-2.5, 0)is -3.JOIN … ONnever matches aNULLkey, not even with anotherNULLkey. An outer join keeps the row of its outer side withNULLcolumns for the other side.
LIKE
LIKE and NOT LIKE support the wildcards of SQL Server: % matches any string, _ any character, [abc] and [a-c] one character of the set, and [^abc] one character outside of it. ESCAPE '!' makes the character after ! a literal, as in LIKE '100!%' ESCAPE '!'. A value of another type is converted to text, as in Id LIKE '4%'.
- Characters are compared ordinally, as
=is:LIKE 'a%'does not matchAlice. SQL Server compares them with the collation of the column, which ignores case by default. - A
NULLvalue, pattern or escape character matches no row, withLIKEand withNOT LIKE. - A pattern given as a literal or a parameter, made of text with an optional
%at its start and its end, is translated to==,StartsWith,EndsWithorContains, so a LINQ provider can run it. Other patterns call a .NET method, which LINQ to objects runs but other providers may not. - As in SQL Server, a pattern can be at most 8,000 bytes, so 4,000 characters: a longer one returns an error. The match takes a time proportional to the length of the value times the length of the pattern, whatever the pattern.
Scalar subqueries
A subquery in parentheses can be used as a value, for instance SELECT c.Id, (SELECT COUNT(*) FROM orders o WHERE o.CustomerId = c.Id) AS OrderCount FROM customers c.
- It selects one expression, from one
FROMtable expression, with an optionalWHERE, andTOPwith an optionalORDER BY.GROUP BY,HAVINGandDISTINCTare not supported. - The aggregates
COUNT(*),SUM,MIN,MAXandAVGare supported, withoutGROUP BY. As in SQL Server,SUM,MIN,MAXandAVGareNULLwhen no row matches, andCOUNT(*)is 0. - A subquery that returns no row is
NULL. One that returns several rows returns the error 512, as SQL Server does. - An aggregate is translated to the LINQ operator (
Count,Sum, …). A subquery that returns a column calls a .NET method to check that there is at most one row, which LINQ to objects runs but other providers may not.
Recursive common table expressions
A common table expression that reads itself is recursive, for instance to walk a hierarchy:
WITH chain AS (
SELECT Id, Name, 0 AS Level FROM employees WHERE ManagerId IS NULL
UNION ALL
SELECT e.Id, e.Name, c.Level + 1 FROM employees e INNER JOIN chain c ON e.ManagerId = c.Id
)
SELECT Id, Name, Level FROM chain ORDER BY Level, Id- The anchor members come first, combined with
UNIONorUNION ALL, then the recursive members, each afterUNION ALL. The anchor runs once, then the recursive members run on the rows of the previous level until they return no row. - The anchor names the columns, unless the expression has a column list, so the columns of the recursive members do not need names. A column is nullable when the anchor or a recursive member returns a nullable value; its type must otherwise be the same, or the error 240 is returned.
- As in SQL Server, a recursive member reads the expression once, in its
FROMclause and not in a subquery or an outer join, and cannot useDISTINCT,TOP,GROUP BY,HAVINGor aggregates. These statements return the errors 252, 253, 460 to 467 of SQL Server. - The recursion stops with the error 530 after 100 levels.
OPTION (MAXRECURSION n)at the end of the statement sets the limit, from 0 (no limit) to 32767. MaxRecursiveCommonTableExpressionRows limits the number of rows, for an expression whose rows grow exponentially or that has no limit; the recursion also stops when the request is canceled. - The expression is computed in memory: the anchor and the recursive members are enumerated synchronously, and the statement reads the rows with LINQ to objects.
INSERT, UPDATE and DELETE
These statements need the handlers of the query root they change. See Modify data.
INSERT INTO t (columns) VALUES (…), (…),INSERT INTO t (columns) SELECT …andINSERT INTO t DEFAULT VALUES. Without a column list, the values go to the settable properties of the row type, in their declaration order.DEFAULTkeeps the value set by the constructor. The selected columns ofINSERT … SELECTmatch the columns by position.UPDATE t SET column = expression, … WHERE …, with the compound operators+=,-=,*=,/=and%=. Every expression reads the values of the row before the statement:SET A = B, B = Aswaps the values.DELETE FROM t WHERE …, orDELETE t.UPDATEandDELETEacceptTOP (n), and aFROMclause that contains only their target, to give it an alias:UPDATE p SET Price = 0 FROM products AS p WHERE p.Id = 1.- The values are converted to the types of the properties. A conversion that fails returns an error, and
NULLin aNOT NULLcolumn the error 515. - The
WHEREclause and the expressions support what aSELECTsupports, including subqueries, and the statements can start with common table expressions, recursive or not.
XML methods
The XML methods take XPath 1.0 expressions, not XQuery. Common paths such as /root/item[@id="2"]/text() behave the same, but FLWOR expressions (for $x in … return), sql:variable(), sql:column() and the XQuery functions return an error.
Typed XML casts validate against schema collections registered with AddXmlSchemaCollection(name, xsd) on the query engine options.
Not supported
SELECTwithoutFROM.- Scalar subqueries with
GROUP BY,HAVINGorDISTINCT, and subqueries inGROUP BYqueries. MERGE, theOUTPUTclause,INSERT TOP, joins inUPDATEandDELETE, and a common table expression as the target of a statement.- Any statement other than
SELECT,INSERT,UPDATEandDELETE, such asDECLAREandSET. GO: it separates batches in client tools such as SQL Server Management Studio, and is not part of T-SQL.