SQL Server Management Studio Documentation

Overview

T‑SQL (Transact‑SQL) is Microsoft’s proprietary extension to the SQL standard. It adds procedural programming, local variables, and various functions that simplify data manipulation in SQL Server.

Keywords

SQL Server reserves a set of words that have special meaning. They must not be used as identifiers unless quoted.

SELECT, FROM, WHERE, INSERT, UPDATE, DELETE,
JOIN, ON, GROUP BY, HAVING, ORDER BY,
DECLARE, BEGIN, END, IF, ELSE,
TRY, CATCH, MERGE, UNION, INTERSECT,
EXISTS, ANY, ALL, TOP, OFFSET, FETCH

Data Types

Common scalar data types include:

int, bigint, smallint, tinyint
decimal(p,s), numeric(p,s), money, smallmoney
float, real
char(n), varchar(n|max), nchar(n), nvarchar(n|max)
binary(n), varbinary(n|max)
date, datetime, datetime2, datetimeoffset, smalldatetime, time
bit, uniqueidentifier, xml, json

Expressions

Expressions combine literals, identifiers, operators, and functions to return a value.

-- Arithmetic
SELECT Price * Quantity AS TotalCost FROM Sales;

-- String concatenation
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

-- Conditional
SELECT CASE WHEN Score >= 90 THEN 'A'
            WHEN Score >= 80 THEN 'B'
            ELSE 'C' END AS Grade
FROM Exams;

Functions

Built‑in functions are categorized. See the Functions reference for a complete list.

Scalar Functions

GETDATE()          -- current date & time
LEN(@string)       -- length of a string
DATEADD(day, 5, GETDATE())   -- add interval
ISNULL(@col, 0)    -- replace NULL

Aggregate Functions

COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)

Operators

Common operators used in T‑SQL:

Arithmetic:  +  -  *  /  %
Comparison: =  <>  !=  >  <  >=  <=
Logical:    AND  OR  NOT
Bitwise:    &  |  ^  ~  <<  >>
String:     + (concatenation)

Example Queries

Simple SELECT

SELECT EmployeeID, FirstName, LastName, HireDate
FROM dbo.Employee
WHERE Department = 'Sales'
ORDER BY HireDate DESC;

INSERT with OUTPUT

INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount)
OUTPUT inserted.OrderID, inserted.OrderDate
VALUES (123, GETDATE(), 250.00);

UPDATE with TRY/CATCH

BEGIN TRY
    UPDATE dbo.Inventory
    SET Quantity = Quantity - @Qty
    WHERE ProductID = @ProductID;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Common Table Expression (CTE)

WITH SalesCTE AS (
    SELECT SalesPersonID, SUM(Amount) AS TotalSales
    FROM dbo.Sales
    GROUP BY SalesPersonID
)
SELECT sp.Name, c.TotalSales
FROM SalesCTE c
JOIN dbo.SalesPerson sp ON sp.ID = c.SalesPersonID
WHERE c.TotalSales > 100000;