Microsoft Docs

Your comprehensive source for Microsoft product documentation.

Transact-SQL Language Reference

Transact-SQL (T-SQL) is Microsoft's proprietary extension of SQL (Structured Query Language) used for relational database management systems, primarily with Microsoft SQL Server.

Syntax Conventions

This documentation uses the following conventions:

  • UPPERCASE: Indicates T-SQL keywords.
  • [ ]: Indicates optional elements.
  • { }: Indicates required elements.
  • |: Indicates a choice between elements.
  • ...: Indicates repetition of the preceding element.

Data Types

T-SQL supports a wide range of data types for storing various kinds of information, including:

  • Numeric Types: INT, DECIMAL, FLOAT, MONEY
  • Date and Time Types: DATE, TIME, DATETIME, DATETIME2
  • String Types: VARCHAR, NVARCHAR, CHAR, NCHAR
  • Binary Types: VARBINARY, BINARY
  • Large Object Types: VARCHAR(MAX), VARBINARY(MAX), NVARCHAR(MAX)
  • Other Types: BIT, UNIQUEIDENTIFIER, XML, GEOGRAPHY, GEOMETRY

For detailed information on each data type, refer to the specific documentation pages.

Statements

T-SQL includes various types of statements to manage and query data:

Data Manipulation Language (DML)

Used for managing data within schema objects:

  • SELECT: Retrieves data from a database.
  • INSERT: Adds new rows of data into a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes rows of data from a table.
  • MERGE: Performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Data Definition Language (DDL)

Used for defining and managing database objects:

  • CREATE: Creates new database objects such as tables, views, indexes, stored procedures, and functions.
  • ALTER: Modifies existing database objects.
  • DROP: Deletes database objects.
  • TRUNCATE TABLE: Removes all rows from a table quickly.

Data Control Language (DCL)

Used for managing permissions and access to data:

  • GRANT: Grants permissions to database users.
  • REVOKE: Revokes previously granted permissions.
  • DENY: Denies permissions to database users.

Control Flow Language

Used for controlling the execution flow of T-SQL batches and stored procedures:

  • BEGIN...END: Groups T-SQL statements.
  • IF...ELSE: Executes statements conditionally.
  • WHILE: Executes statements repeatedly.
  • WAITFOR: Suspends execution until a specified time or condition is met.
  • GOTO: Transfers control to a labeled statement.
  • TRY...CATCH: Handles errors in T-SQL code.
  • THROW: Raises an error or exception.

Functions

T-SQL provides built-in functions for various operations:

Aggregate Functions

Perform calculations on a set of rows and return a single value:

  • AVG(): Calculates the average value.
  • COUNT(): Counts the number of rows.
  • MAX(): Returns the maximum value.
  • MIN(): Returns the minimum value.
  • SUM(): Calculates the sum of values.

Scalar Functions

Return a single value for each row processed:

  • ABS(): Returns the absolute value.
  • GETDATE(): Returns the current database system timestamp.
  • LEN(): Returns the length of a string.
  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.
  • ISNULL(): Replaces NULL with a specified replacement value.

Table-Valued Functions

Return a table as a result:

  • Inline Table-Valued Functions: Defined with a single RETURN statement.
  • Multi-Statement Table-Valued Functions: Defined with a BEGIN...END block to build the result table.

Operators

T-SQL uses operators for comparisons, calculations, and logical operations:

  • Arithmetic Operators: +, -, *, /, %
  • Bitwise Operators: &, |, ^, ~
  • Comparison Operators: =, >, <, >=, <=, <>, !=, IS NULL, IS NOT NULL, LIKE, BETWEEN, IN
  • Logical Operators: AND, OR, NOT, XOR
  • String Concatenation Operator: +
  • Assignment Operator: =

System Stored Procedures

Predefined stored procedures provided by SQL Server for performing administrative and diagnostic tasks. Examples include:

  • sp_help: Returns information about a database object.
  • sp_configure: Returns or changes configuration values.
  • sp_tables: Returns information about tables.

Error Handling

T-SQL provides mechanisms to handle errors gracefully:

  • @@ERROR Global Variable: Returns the error number of the last executed Transact-SQL statement.
  • RAISERROR Statement: Raises a custom error message.
  • TRY...CATCH Blocks: Provides structured error handling.

This section provides a high-level overview. For in-depth details, please navigate to specific topics using the sidebar.