Transact-SQL (T-SQL) Reference

Transact-SQL (T-SQL) is Microsoft's proprietary extension to SQL (Structured Query Language) used by Microsoft SQL Server and Azure SQL Database.

Key Concepts

Syntax: Understanding the structure and rules of T-SQL statements.
Data Types: Exploring the various data types available for storing information.
Statements: Learning about common T-SQL statements like SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP.
Control Flow: Implementing conditional logic and loops with IF...ELSE, WHILE, and CASE statements.
Functions: Utilizing built-in functions for string manipulation, date/time operations, mathematical calculations, and more.
Error Handling: Managing errors and exceptions using TRY...CATCH blocks.

Common T-SQL Statements

SELECT Statement

Used to retrieve data from one or more tables.

                
SELECT column1, column2,

       column3

FROM table_name

WHERE condition;

INSERT Statement

Used to add new rows of data to a table.

                
INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

UPDATE Statement

Used to modify existing data in a table.

                
UPDATE table_name

SET column1 = value1,

    column2 = value2

WHERE condition;

DELETE Statement

Used to remove rows from a table.

                
DELETE FROM table_name

WHERE condition;

Control Flow Statements

IF...ELSE

Executes a block of T-SQL statements based on a condition.

                
IF condition

BEGIN

    -- Statements to execute if condition is true

END

ELSE

BEGIN

    -- Statements to execute if condition is false

END

WHILE

Executes a block of T-SQL statements repeatedly as long as a condition is true.

                
WHILE condition

BEGIN

    -- Statements to execute

    -- Update condition variables to eventually terminate the loop

END

Example:

This example demonstrates selecting customers from a specific region and updating their status if they have placed an order.

                    
SELECT CustomerID, CompanyName, Region

FROM Customers

WHERE Region = 'USA';

DECLARE @CustomerID INT;

SET @CustomerID = 101;

IF EXISTS (SELECT 1 FROM Orders WHERE CustomerID = @CustomerID)

BEGIN

    UPDATE Customers

    SET CustomerStatus = 'Active' WHERE CustomerID = @CustomerID;

    PRINT 'Customer status updated to Active.';

END

ELSE

BEGIN

    PRINT 'No orders found for this customer.';

END

Built-in Functions

T-SQL provides a rich set of built-in functions for various purposes:

String Functions: LEN(), SUBSTRING(), REPLACE(), CONCAT()
Date and Time Functions: GETDATE(), DATEADD(), DATEDIFF(), FORMAT()
Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()
Conversion Functions: CAST(), CONVERT()

Error Handling

The TRY...CATCH block allows you to handle errors gracefully.

                
BEGIN TRY

    -- Code that might cause an error

    SELECT 1/0;

END TRY

BEGIN CATCH

    -- Error handling code

    PRINT 'An error occurred: ' + ERROR_MESSAGE();

END CATCH