T-SQL Language Reference

This document provides a comprehensive reference for the Transact-SQL (T-SQL) programming language used with Microsoft SQL Server.

Introduction

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL standard. It integrates a rich set of procedural programming constructs, including control-of-flow language, local variables, and support for program error and transaction management. This reference details the syntax, semantics, and usage of T-SQL elements.

Overview

T-SQL is used for a wide range of database operations, from simple data retrieval and manipulation to complex business logic implementation within the database. It is the primary language for interacting with SQL Server.

SELECT Statement

The SELECT statement is used to retrieve data from one or more tables.

Syntax

SELECT
    [ ALL | DISTINCT ]
    
[ INTO new_table ]
[ FROM
    
    [,  ... ]
]
[ WHERE
    
]
[ GROUP BY
    
    [ WITH { CUBE | ROLLUP } ]
]
[ HAVING
    
]
[ ORDER BY
     [ ASC | DESC ]
    [, ...n ]
]
[ OFFSET { integer_expression } { ROWS | NEXT }
    [ FETCH { FIRST | NEXT } { integer_expression } { ROWS | 
        ARTICLE } [ ONLY ] ]
;

Example

Basic SELECT with WHERE clause

SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'USA'
ORDER BY CompanyName;

INSERT Statement

The INSERT statement adds one or more rows to a table.

Syntax

INSERT [ INTO ] { <object> [ ( <column_list> ) ] }
    { VALUES ( <insert_values> [, ...n ] )
    | <select_statement>
    | DEFAULT VALUES
    }
;

Example

INSERT using VALUES

INSERT INTO Products (ProductID, ProductName, UnitPrice)
VALUES (101, 'New Gadget', 49.99);

UPDATE Statement

The UPDATE statement modifies existing rows in a table.

Syntax

UPDATE [ <object> ]
    SET
    [ <column> = { <expression> | DEFAULT }
    [, ...n ]
]
[ FROM
    <from_clause>
]
[ WHERE
    <search_condition>
]
;

Example

UPDATE with WHERE

UPDATE Employees
SET Salary = Salary * 1.05
WHERE JobTitle = 'Sales Representative';

DELETE Statement

The DELETE statement removes rows from a table.

Syntax

DELETE [ FROM ] { <object> }
[ WHERE
    <search_condition>
]
;

Example

DELETE with WHERE

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

CREATE Statement

The CREATE statement is used to create database objects such as tables, views, indexes, and stored procedures.

Syntax (Table)

CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
(
    { <column_definition>
    | <table_constraint>
    } [ ,...n ]
)
[ ; ]

Example (Table)

CREATE TABLE

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

Functions

T-SQL supports various built-in functions for data manipulation, aggregation, and system information.

Aggregate Functions

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

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

Example

Calculating Average Salary

SELECT AVG(Salary) AS AverageSalary
FROM Employees;

Scalar Functions

Scalar functions return a single value for each row processed.

  • String Functions (e.g., LEN(), SUBSTRING(), UPPER())
  • Date and Time Functions (e.g., GETDATE(), DATEPART(), DATEDIFF())
  • Mathematical Functions (e.g., ABS(), ROUND())
  • ISNULL()
  • CAST() and CONVERT()

Example

Extracting Year from Date

SELECT ProductName, DATEPART(year, IntroductionDate) AS IntroYear
FROM Products;

Operators

Operators are symbols that specify the type of computation or comparison to be performed.

Arithmetic Operators

  • +, -, *, /, %

Comparison Operators

  • =, <, >, <>, !=, <=, >=

Logical Operators

  • AND, OR, NOT

String Operators

  • + (concatenation)

Keywords

T-SQL has a rich set of reserved keywords that have specific meanings and cannot be used as identifiers for database objects unless properly quoted.

Common Keywords

  • SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
  • AND, OR, NOT, IN, LIKE, BETWEEN
  • AS, ON, JOIN, LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL JOIN
  • GROUP BY, HAVING, ORDER BY
  • BEGIN, END, IF, ELSE, WHILE, DECLARE, SET
  • NULL, IS NULL, IS NOT NULL
Back to Top