SQL Server T-SQL Documentation

Transact-SQL (T-SQL) Fundamentals

Transact-SQL (T-SQL) is Microsoft's proprietary extension to SQL (Structured Query Language) used with Microsoft SQL Server. It provides a powerful set of commands for database management, data manipulation, and data definition.

Core Concepts

Data Manipulation Language (DML): Used to retrieve, insert, update, and delete data. Commands include SELECT, INSERT, UPDATE, and DELETE.
Data Definition Language (DDL): Used to define and modify database objects like tables, indexes, and views. Commands include CREATE, ALTER, and DROP.
Data Control Language (DCL): Used to manage permissions and access to data. Commands include GRANT and REVOKE.
Transaction Control Language (TCL): Used to manage transactions. Commands include BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.

Basic SELECT Statement

The SELECT statement is the cornerstone of data retrieval in T-SQL.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

INSERT Statement

To add new rows to a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE Statement

To modify existing data in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE Statement

To remove rows from a table.

DELETE FROM table_name
WHERE condition;

Common T-SQL Functions

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

String Functions: LEN(), SUBSTRING(), LEFT(), RIGHT(), UPPER(), LOWER()
Date Functions: GETDATE(), DATEADD(), DATEDIFF(), YEAR(), MONTH(), DAY()
Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()

Tip: Always use a WHERE clause with UPDATE and DELETE statements to avoid unintended modifications or deletions of data across the entire table.

Joins in T-SQL

Joins are used to combine rows from two or more tables based on a related column between them.

INNER JOIN: Returns rows when there is a match in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in one of the tables.

SELECT orders.OrderID, customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Stored Procedures

Stored procedures are precompiled SQL statements that can be executed repeatedly. They improve performance, security, and modularity.

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

To execute a stored procedure:

EXEC GetCustomerOrders @CustomerID = 101;

Views

Views are virtual tables based on the result-set of a SQL statement. They simplify complex queries and can be used for security purposes.

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName
FROM Customers
WHERE IsActive = 1;

Querying a view:

SELECT CustomerName
FROM ActiveCustomers;