t-sql

name

t-sql

description

Comprehensive T-SQL language reference for implementing a T-SQL engine. Covers data types, type conversion, operators, expressions, control flow, queries (SELECT, JOIN, CTE, window functions), DML statements (INSERT, UPDATE, DELETE, MERGE), DDL statements (CREATE/ALTER/DROP TABLE, INDEX, VIEW, PROCEDURE, FUNCTION, TRIGGER), built-in functions (aggregate, string, math, date/time, conversion, JSON), transactions, error handling, and session settings. Use when implementing T-SQL parsing, evaluation, query processing, or SQL Server compatibility.

T-SQL Language Reference

Complete reference for the Transact-SQL language used by Microsoft SQL Server.

Source https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver17

Reference Files

• data-types.md — All data types (exact/approximate numeric, date/time, character, binary, special), storage sizes, ranges, precision/scale rules, type precedence, type synonyms
• type-conversion.md — CAST/CONVERT with style codes, TRY_CAST/TRY_CONVERT, implicit/explicit conversion matrix, decimal arithmetic precision rules, truncation vs rounding behavior
• language-elements.md — Operators (arithmetic, comparison, logical, bitwise, string, compound, unary), operator precedence, expressions, CASE, LIKE wildcards, BETWEEN/IN/EXISTS/ALL/ANY, variables (DECLARE/SET/SELECT), control flow (IF/ELSE, WHILE, BEGIN/END, GOTO, RETURN, WAITFOR), cursors, reserved keywords, NULL and three-valued logic
• queries.md — Logical processing order, SELECT clause, FROM/JOIN types and semantics (INNER/LEFT/RIGHT/FULL/CROSS, APPLY, PIVOT), WHERE/search conditions, GROUP BY (ROLLUP/CUBE/GROUPING SETS), HAVING, ORDER BY with OFFSET/FETCH, TOP, OVER clause / window frames (ROWS vs RANGE), CTEs (recursive and non-recursive), set operations (UNION/EXCEPT/INTERSECT), subqueries, OUTPUT clause, SELECT INTO, table value constructor, hints, AT TIME ZONE
• functions.md — Aggregate functions (COUNT, SUM, AVG, MIN, MAX, STRING_AGG, STDEV, VAR), ranking/window functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENT_RANK, CUME_DIST, PERCENTILE_CONT/DISC), string functions (SUBSTRING, LEN, REPLACE, CONCAT, UPPER, LOWER, TRIM, LEFT, RIGHT, CHARINDEX, PATINDEX, REVERSE, REPLICATE, STUFF, SPACE, CHAR, NCHAR, ASCII, UNICODE), math functions (ABS, CEILING, FLOOR, ROUND, POWER, SQRT, SIGN), date/time functions (GETDATE, DATEADD, DATEDIFF, DATEPART, DATENAME, DATEFROMPARTS, FORMAT), logical functions (IIF, CHOOSE, ISNULL, COALESCE, NULLIF), system functions (NEWID, DB_NAME, OBJECT_ID, @@ERROR, @@ROWCOUNT, SCOPE_IDENTITY, @@TRANCOUNT), JSON functions (JSON_VALUE, JSON_QUERY, JSON_MODIFY, ISJSON, OPENJSON), error functions (ERROR_MESSAGE/NUMBER/SEVERITY/STATE/LINE/PROCEDURE), ISNUMERIC, ISDATE, DATALENGTH
• statements-ddl.md — CREATE/ALTER/DROP TABLE (column definitions, constraints, IDENTITY, computed columns, foreign key actions), CREATE INDEX (clustered/nonclustered, UNIQUE, filtered, INCLUDE, options), CREATE VIEW (SCHEMABINDING, CHECK OPTION, updatable views), CREATE PROCEDURE (parameters, OUTPUT, RECOMPILE), CREATE FUNCTION (scalar, inline TVF, multi-statement TVF), CREATE TRIGGER (AFTER/INSTEAD OF, DML/DDL, inserted/deleted tables), CREATE DATABASE, CREATE SCHEMA, GRANT/DENY/REVOKE permissions
• statements-dml.md — INSERT (VALUES, SELECT, EXEC, DEFAULT VALUES, OUTPUT), UPDATE (SET, compound operators, FROM clause for multi-table, .WRITE for LOB), DELETE (FROM...FROM pattern, join-based), MERGE (MATCHED/NOT MATCHED, $action), TRUNCATE TABLE (vs DELETE, IDENTITY reset, restrictions), BULK INSERT (format options, constraints, triggers)
• transactions-and-error-handling.md — Transaction modes (autocommit/explicit/implicit), BEGIN/COMMIT/ROLLBACK TRANSACTION, SAVE TRANSACTION/savepoints, @@TRANCOUNT semantics, XACT_STATE(), TRY...CATCH (severity rules, error functions, uncommittable transactions), THROW vs RAISERROR, SET statements (ANSI_NULLS, QUOTED_IDENTIFIER, NOCOUNT, XACT_ABORT, IDENTITY_INSERT, TRANSACTION ISOLATION LEVEL, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and more), isolation levels

Implementation — @mssqlite/tsql + transpile + engine

The language pipeline lives in three packages:

• packages/tsql — lexer + parser to a typed AST. Its Readme lists the exact supported surface (queries with joins/ CTEs/set ops/window OVER, full expression precedence and predicates, DML, DDL with constraints, DECLARE/SET, control flow, transactions, EXEC, THROW).
• packages/transpile — AST → SQLite SQL with function mapping and CONVERT style support.
• packages/engine — interprets what SQLite cannot: variables, IF/WHILE, @@TRANCOUNT nesting, sp_executesql, SELECT INTO, error-number mapping. Its server-facing async execution checks an AbortSignal between statements and on every interpreted loop iteration; Attention is control flow, not a catchable T-SQL error.

Parsing notes discovered implementing

• Reserved words must be rejected as bare identifiers (else SELECT FROM "succeeds" selecting a column named FROM) but allowed as function names directly before ( — LEFT(x, 1), RIGHT(...).
• TOP 10 * is ambiguous with multiplication: binary-operator parsing must rewind when the right operand fails so * can be the select star. Bare TOP n only takes a constant; expressions require TOP (expr).
• CASE x WHEN … vs CASE WHEN …: the optional operand parser must not swallow the WHEN keyword as a column reference.
• GO is a client-side batch separator — it never arrives over TDS and is deliberately not in the grammar.
• Statements separate on semicolons or juxtaposition; both appear in real client traffic.
• @@ERROR reads as the previous statement's error number — it resets after the statement referencing it, and it persists across batches.

Implementation notes — later additions

• TRY/CATCH parses in beginBlockOrTransaction (BEGIN TRAN → BEGIN TRY → block, in that order); bodies terminate on the two-word END TRY / END CATCH, so nested plain BEGIN … END blocks parse through statementRef and never confuse the terminator scan.
• RAISERROR takes a parenthesized argument list plus optional WITH option[, …] (options parsed, lowercased, NOWAIT/LOG ignored).
• Batch execution classifies failures after each top-level statement. Constraint violations (515/547/2601/2627), conversion/arithmetic classes, RAISERROR severity 11-19, and cursor/sequence runtime errors emit an ERROR and continue; syntax/compile failures, explicit THROW, unsupported operations, and severity 20+ abort. @@ERROR exposes the immediately prior failure and @@ROWCOUNT is 0 after it. XACT_ABORT ON rolls back and aborts for qualifying runtime errors, but—as on SQL Server—does not change RAISERROR behavior. TRY/CATCH intercepts errors before this outer classification. Integer CAST/CONVERT truncates numeric/decimal inputs toward zero and treats empty or whitespace-only character input as zero. Other invalid text and type bounds retain 245/8115; TRY variants convert either failure to NULL.
• Integer + - * / % uses checked evaluation: NULL propagates, integer division truncates toward zero, zero divisors raise 8134, and inferred int/bigint bounds raise 8115. SUM defaults to SQL Server's int-width accumulator; explicitly casting its argument to BIGINT selects a 64-bit accumulator. Both ARITHABORT OFF and ANSI_WARNINGS OFF are required to turn an arithmetic failure into NULL; otherwise the error is catchable and honors XACT_ABORT. Integer AVG shares the checked accumulator, truncates the quotient toward zero, ignores NULLs, and retains int/bigint result width; COUNT_BIG always retains bigint metadata. DECIMAL/NUMERIC uses fixed-scale strings with scaled-BigInt casts and arithmetic, SQL Server operator precision/scale formulas (including the precision-38 reduction rules), half-away-from-zero rounding, and 8115/8134 errors.
• Mixed known types use the SQL Server precedence table before evaluation. Arithmetic, comparisons, BETWEEN, IN, simple and result CASE, set operations, multi-row VALUES, and DML/MERGE target assignments share that coercion path. Declared columns, variables, procedure parameters, and RPC TYPE_INFO all participate; invalid conversions preserve 245/241/8114/8169/8115 and incompatible pairs raise 206/402 instead of inheriting SQLite affinity.
• String boundary functions use dedicated UDFs where SQLite differs: SUBSTRING starts before one by shortening the returned prefix, negative LEFT/RIGHT/SUBSTRING lengths raise 536, negative REPLICATE/SPACE counts return NULL, and QUOTENAME accepts the documented delimiter pairs, doubles closing delimiters, and returns NULL for input longer than 128 characters. Their result descriptors follow SQL Server width rules: literal SUBSTRING/LEFT/RIGHT/SPACE counts narrow widths, REPLICATE multiplies and caps them at 8,000 bytes, REPLACE/TRANSLATE/STRING_AGG expose their family maximum, and STUFF accounts for removed and replacement widths.

Not yet implemented (raise clean errors)

WAITFOR, GOTO, BULK INSERT ... FROM file, source columns in MERGE OUTPUT, COLLATE as expression operator, AT TIME ZONE.

ALTER TABLE ... ALTER COLUMN accepts declared type arguments, optional COLLATE, and explicit/omitted nullability (omitted means NULL). Character changes without COLLATE reset to the database default. The engine permits the SQL Server-compatible indexed variable-length widening case and otherwise rejects incompatible PK/index/FK/CHECK/DEFAULT/computed dependencies with 4922 before rebuilding and converting the stored column.

CROSS/OUTER APPLY retain left-to-right lateral dependencies in the source AST. The resolved derived source carries projection metadata so arbitrary SELECT or VALUES right sides can preserve aliases, stars, nullability, and wire types. Derived execution supports predicates, aggregates, TOP/ORDER, nesting, and zero/one/many rows; a non-table APPLY source that cannot be lowered raises the specific compatibility error 40000 rather than becoming parser error 102.

Compatibility audit findings

The live TDS audit at commit bcad53b found additional semantic gaps tracked in todo/. Unique constraints and explicit unique indexes now treat repeated NULL-containing tuples as duplicates, preserving collation keys and reporting 2627/2601 by origin. SELECT INTO now derives exact expression/source types, nullability, collation, and eligible identity before row execution. IDENTITY allocation findings from that audit are implemented with database-owned counters, custom signed definitions, rollback gaps, session IDENTITY_INSERT, and trigger-aware scope. It also confirmed general APPLY lowering; strict OPENJSON paths, VALUES-derived tables, and the audited MERGE terminator and arm validation rules are now implemented. The remaining result-metadata, completion-token, and runtime error-stream differences are indexed by TODO.md. Treat those individual briefs as the executable scope and ground-truth checklist.

Result inference preserves integer nullability through catalog and scalar projections: proven non-null tinyint/smallint/int/bigint results use fixed TDS integer families, nullable results retain INTN, @@TRANCOUNT is non-null int, and XACT_STATE() advertises nullable smallint width.