MSDN SQL Documentation

Transact-SQL (T-SQL) Reference

This section provides comprehensive documentation for Transact-SQL (T-SQL), Microsoft's proprietary extension to SQL used with Microsoft SQL Server and Azure SQL Database. Explore the syntax, functions, and statements that power your database operations.

Introduction to T-SQL

Transact-SQL is a powerful procedural programming language that extends SQL to provide features such as variable declaration, control-of-flow language constructs (like IF and WHILE), and error handling. It's essential for developing robust database applications.

Key Features:

• Procedural extensions to SQL
• Support for transactions
• Error handling and debugging capabilities
• Integration with other Microsoft technologies

T-SQL Data Types

Understanding T-SQL data types is crucial for efficient data storage and manipulation. Here's a summary of common data types:

Data Type	Description	Example
INT	Integer data.	123, -45
VARCHAR(n)	Variable-length non-Unicode character string.	'Hello', 'SQL'
NVARCHAR(n)	Variable-length Unicode character string.	N'你好', N'Database'
DECIMAL(p, s)	Fixed precision and scale numeric data.	123.45, 99.99
DATE	Date value.	'2023-10-27'
DATETIME	Date and time value.	'2023-10-27 10:30:00'
BIT	Integer with a value of 0, 1, or NULL.	0, 1
UNIQUEIDENTIFIER	16-byte GUID.	'A1B2C3D4-E5F6-7890-1234-567890ABCDEF'

T-SQL Operators

Operators are used to perform operations on operands (values and variables).

Arithmetic Operators:

• + (Addition)
• - (Subtraction)
• * (Multiplication)
• / (Division)
• % (Modulo)

Comparison Operators:

• = (Equal to)
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)
• <> or != (Not equal to)

Logical Operators:

• AND
• OR
• NOT

Common Built-in Functions

T-SQL offers a rich set of built-in functions for string manipulation, date/time operations, mathematical calculations, and more.

String Functions:

• LEN(string): Returns the length of a string.
• LEFT(string, number): Returns the left part of a string.
• RIGHT(string, number): Returns the right part of a string.
• SUBSTRING(string, start, length): Extracts a substring.
• UPPER(string): Converts a string to uppercase.
• LOWER(string): Converts a string to lowercase.

Date Functions:

• GETDATE(): Returns the current database system date and time.
• YEAR(date): Returns the year of a date.
• MONTH(date): Returns the month of a date.
• DAY(date): Returns the day of a date.
• DATEADD(datepart, number, date): Adds a specified time interval to a date.

Aggregate Functions:

• COUNT(column): Counts the number of rows.
• SUM(column): Calculates the sum of a numeric column.
• AVG(column): Calculates the average of a numeric column.
• MIN(column): Returns the minimum value.
• MAX(column): Returns the maximum value.

T-SQL Statements

T-SQL provides a full range of SQL statements for data definition, manipulation, and control.

CREATE Statement

Used to create database objects.

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

SELECT Statement

Used to retrieve data from one or more tables.

SELECT FirstName, LastName
FROM Employees
WHERE HireDate > '2020-01-01';
                

INSERT Statement

Used to add new rows to a table.

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (101, 'Jane', 'Doe', '2023-05-15');
                

UPDATE Statement

Used to modify existing records in a table.

UPDATE Employees
SET LastName = 'Smith'
WHERE EmployeeID = 101;
                

DELETE Statement

Used to remove rows from a table.

DELETE FROM Employees
WHERE EmployeeID = 101;
                

DROP Statement

Used to delete database objects.

DROP TABLE Employees;
                

ALTER Statement

Used to modify the structure of existing database objects.

ALTER TABLE Employees
ADD Email VARCHAR(100);
                

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database. They offer benefits like improved performance, reusability, and enhanced security.

CREATE PROCEDURE GetEmployeeCountByDate
    @HireDate DATE
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount
    FROM Employees
    WHERE HireDate = @HireDate;
END;
GO

-- Execute the stored procedure
EXEC GetEmployeeCountByDate @HireDate = '2023-05-15';
                

Views

Views are virtual tables based on the result-set of an SQL statement. They can simplify complex queries and restrict data access.

CREATE VIEW EmployeeContactInfo AS
SELECT EmployeeID, FirstName, LastName, Email
FROM Employees;
GO

SELECT * FROM EmployeeContactInfo;
                

Triggers

Triggers are special stored procedures that execute automatically when an event (INSERT, UPDATE, DELETE) occurs on a table.

CREATE TRIGGER trg_AfterEmployeeInsert
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'A new employee record has been inserted.';
END;
GO