T-SQL Syntax Reference

This section provides detailed information on the Transact-SQL (T-SQL) syntax used in Microsoft SQL Server. T-SQL is a powerful extension of SQL that adds procedural programming, local variables, string and date processing functions, and command-line editing.

T-SQL Statements

Statements are the fundamental building blocks of T-SQL. They are used to perform actions on the database, such as creating tables, inserting data, querying information, and managing the database.

Data Definition Language (DDL) Statements

DDL statements are used to define and manage database objects.

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

Data Manipulation Language (DML) Statements

DML statements are used to manage data within database objects.

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds new rows of data to 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 a join with a source table.

Control-of-Flow Language Statements

These statements allow you to control the execution flow of T-SQL code.

• BEGIN...END: Groups a set of T-SQL statements.
• IF...ELSE: Executes statements conditionally.
• WHILE: Repeats a set of statements while a condition is true.
• WAITFOR: Pauses execution until a specified time or time interval.
• GOTO: Transfers control to a label within a batch or procedure.
• RETURN: Exits a stored procedure or function.
• THROW: Raises a run-time error.
• TRY...CATCH: Catches errors that occur during execution.

T-SQL Expressions

Expressions are combinations of values, operators, and functions that evaluate to a single value.

Scalar Expressions

Scalar expressions return a single value.

• Literals: Constant values like numbers (123, 3.14), strings ('Hello'), and dates ('2023-10-27').
• Column Names: References to data in a table column.
• Variables: User-defined or system-defined variables (@myVariable).
• Function Calls: Invokes built-in or user-defined functions.

Table Expressions

Table expressions return a result set (a table).

• Table Names: References to tables in the database.
• Subqueries: A query nested within another query.
• Table-Valued Functions: Functions that return a table.
• VALUES clause: Generates a table from a list of row values.

T-SQL Operators

Operators are special symbols or keywords that perform operations on one or more expressions.

Arithmetic Operators

Comparison Operators

Logical Operators

String Concatenation Operator

The + operator is used for string concatenation in T-SQL.

SELECT 'Microsoft' + ' ' + 'SQL Server';
            

T-SQL Data Types

T-SQL supports a wide range of data types to store different kinds of information.

Numeric Data Types

• INT, BIGINT, SMALLINT, TINYINT: For whole numbers.
• DECIMAL, NUMERIC: For exact fixed-point numbers.
• FLOAT, REAL: For approximate floating-point numbers.
• MONEY, SMALLMONEY: For currency values.

Date and Time Data Types

• DATE: Stores date values.
• TIME: Stores time values.
• DATETIME, DATETIME2: Stores both date and time values.
• SMALLDATETIME: Stores date and time values with less precision.
• DATETIMEOFFSET: Stores date and time with time zone offset.

Character String Data Types

• CHAR, VARCHAR: Fixed-length and variable-length non-Unicode strings.
• NCHAR, NVARCHAR: Fixed-length and variable-length Unicode strings.
• TEXT: Older data type for large text data (deprecated, use VARCHAR(MAX)).
• NTEXT: Older data type for large Unicode text data (deprecated, use NVARCHAR(MAX)).

Binary Data Types

• BINARY, VARBINARY: Fixed-length and variable-length binary data.
• IMAGE: Older data type for large binary data (deprecated, use VARBINARY(MAX)).

Other Data Types

• BIT: Stores 0, 1, or NULL.
• UNIQUEIDENTIFIER: Stores a globally unique identifier (GUID).
• XML: Stores XML data.
• GEOMETRY, GEOGRAPHY: Spatial data types.
• SQL_VARIANT: Stores values of various .NET Framework data types.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) DEFAULT 0.00,
    StockQuantity SMALLINT,
    ManufacturedDate DATE,
    Description NVARCHAR(MAX),
    IsActive BIT DEFAULT 1
);
            

This section covers the core syntax elements of T-SQL. For more advanced topics and specific function references, please refer to the relevant sub-sections.