MSDN Documentation

Transact-SQL (T-SQL) Language Reference

This section provides a comprehensive reference for the Transact-SQL (T-SQL) language used with Microsoft SQL Server and Azure SQL Database.

Introduction to T-SQL

Transact-SQL (T-SQL) is Microsoft's proprietary extension of SQL (Structured Query Language) that is used for managing and manipulating databases in Microsoft SQL Server and Azure SQL Database.

Language Overview

T-SQL combines standard SQL with procedural programming constructs, local variables, and support for character, date, time, XML, and spatial data. It is a powerful language for:

• Defining database schemas and objects.
• Manipulating data within tables.
• Controlling access to data.
• Implementing complex business logic through stored procedures and functions.

T-SQL Statements

T-SQL statements are the fundamental building blocks for interacting with a database. They can be categorized into several types:

Data Definition Language (DDL) Statements

DDL statements are used to define, alter, and drop database objects.

• CREATE DATABASE
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE INDEX
• CREATE VIEW

Data Manipulation Language (DML) Statements

DML statements are used to retrieve, insert, update, and delete data in database tables.

• SELECT: Retrieves data from one or more tables.
• INSERT: Adds new rows to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes rows from a table.

Data Control Language (DCL) Statements

DCL statements are used to manage permissions and access to database objects.

• GRANT: Grants permissions to users or roles.
• REVOKE: Removes permissions from users or roles.
• DENY: Denies specific permissions.

T-SQL Functions

Functions return a scalar value, a table, or a set of values. They are crucial for data processing and manipulation.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Scalar Functions

Scalar functions return a single value based on the input values.

• String Functions: LEN(), SUBSTRING(), REPLACE()
• Date and Time Functions: GETDATE(), DATEADD(), DATEDIFF()
• Mathematical Functions: ABS(), ROUND(), POWER()

Table-Valued Functions (TVFs)

TVFs return a table result, which can be used in the FROM clause of queries.

T-SQL Operators

Operators are used in expressions to perform operations on values. These include:

• Arithmetic Operators: +, -, *, /, %
• Comparison Operators: =, <, >, <>, <=, >=
• Logical Operators: AND, OR, NOT
• Bitwise Operators: &, |, ^, ~
• Assignment Operator: =

T-SQL Data Types

T-SQL supports a wide range of data types for storing different kinds of information:

• Numeric Types: INT, DECIMAL, FLOAT
• Character Types: VARCHAR, NVARCHAR, CHAR
• Date and Time Types: DATE, TIME, DATETIME, DATETIME2
• Binary Types: BINARY, VARBINARY
• Other Types: BIT, UNIQUEIDENTIFIER, XML, GEOGRAPHY, GEOMETRY

T-SQL Reserved Words

Certain words are reserved in T-SQL and cannot be used as identifiers (e.g., table names, column names) unless they are delimited by square brackets ([...]) or double quotes ("...").

Examples include: SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TABLE, INDEX, VIEW, FUNCTION, PROCEDURE.

For detailed syntax and examples of each statement, function, and operator, please refer to the specific documentation pages.