T-SQL Reference

Introduction to T-SQL

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL standard, used with Microsoft SQL Server and Azure SQL Database.

It provides a rich set of procedural programming elements, enhanced data manipulation, and query capabilities.

Data Types

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

Numeric Types

  • INT, BIGINT, SMALLINT, TINYINT
  • DECIMAL, NUMERIC
  • FLOAT, REAL
  • MONEY, SMALLMONEY

Date and Time Types

  • DATE, TIME
  • DATETIME, DATETIME2
  • SMALLDATETIME
  • DATETIMEOFFSET

String Types

  • VARCHAR, NVARCHAR
  • CHAR, NCHAR
  • TEXT, NTEXT (deprecated)
  • VARBINARY, BINARY

Other Types

  • BIT
  • UNIQUEIDENTIFIER
  • XML
  • GEOMETRY, GEOGRAPHY

Operators

T-SQL provides various operators for comparisons, arithmetic, logical operations, and string manipulation.

Comparison Operators

  • =, <, >, <=, >=, <> (or !=)
  • BETWEEN, LIKE, IN, IS NULL, IS NOT NULL

Arithmetic Operators

  • +, -, *, /, %

Logical Operators

  • AND, OR, NOT

Bitwise Operators

  • &, |, ^, ~

String Operators

  • + (concatenation)
  • || (concatenation, if enabled)

Statements

T-SQL includes a comprehensive set of statements for managing and querying databases.

Data Definition Language (DDL) Statements

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

Data Manipulation Language (DML) Statements

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • MERGE

Control Flow Statements

  • BEGIN...END
  • IF...ELSE
  • WHILE
  • WAITFOR
  • GOTO
  • RETURN
  • THROW, RAISERROR

Functions

T-SQL provides numerous built-in functions for calculations, string manipulation, date/time operations, and more.

Aggregate Functions

  • COUNT()
  • SUM()
  • AVG()
  • MIN()
  • MAX()
  • STDEV(), VARIANCE()

Scalar Functions

  • String Functions: LEN(), SUBSTRING(), LEFT(), RIGHT(), REPLACE(), UPPER(), LOWER(), TRIM()
  • Numeric Functions: ABS(), ROUND(), CEILING(), FLOOR()
  • Date and Time Functions: GETDATE(), DATEPART(), DATEDIFF(), DATEADD()
  • System Functions: @@ROWCOUNT, DB_NAME(), USER_NAME()

Table-Valued Functions

User-defined functions that return a table result set.

CREATE FUNCTION dbo.GetCustomersByCity (@City NVARCHAR(100))
RETURNS TABLE
AS
RETURN
(
    SELECT CustomerID, CompanyName, ContactName
    FROM Customers
    WHERE City = @City
);

System Views and Functions

SQL Server provides system catalog views, dynamic management views (DMVs), and system functions to query metadata and monitor server activity.

Catalog Views

  • sys.tables
  • sys.columns
  • sys.procedures

Dynamic Management Views (DMVs)

  • sys.dm_exec_sessions
  • sys.dm_exec_requests
  • sys.dm_db_index_usage_stats

System Functions (Examples)

  • @@VERSION
  • DB_ID()
  • OBJECT_NAME()