T-SQL Fundamentals: The Building Blocks of SQL Server

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language used by Microsoft SQL Server. Understanding the fundamental concepts of T-SQL is crucial for anyone working with SQL Server databases, from querying data to developing complex stored procedures.

What is T-SQL?

T-SQL extends SQL with additional features such as:

Procedural Programming: Variables, control-flow logic (IF, WHILE), error handling (TRY...CATCH), and cursors.
Transaction Control: BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION.
Data Definition Language (DDL): CREATE, ALTER, DROP statements for schema management.
Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE for data operations.
System Functions and Stored Procedures: Built-in functions and procedures for various tasks.

Key T-SQL Concepts

1. Data Types

Understanding T-SQL data types is fundamental for storing data efficiently and correctly. Common data types include:

INT, BIGINT, SMALLINT, TINYINT (Numeric)
DECIMAL, NUMERIC, FLOAT, REAL (Approximate Numeric)
CHAR, VARCHAR, NCHAR, NVARCHAR (Character Strings)
DATE, TIME, DATETIME2, DATETIME (Date and Time)
BIT (Boolean)
UNIQUEIDENTIFIER
VARBINARY

2. Variables and Assignment

You can declare and assign values to variables using the DECLARE and SET or SELECT statements.

DECLARE @MyVariable INT;
SET @MyVariable = 10;

DECLARE @CustomerName VARCHAR(100);
SELECT @CustomerName = Name FROM Customers WHERE CustomerID = 1;

PRINT @CustomerName;

3. Control Flow Language

T-SQL offers constructs for conditional execution and loops.

IF...ELSE Statement

Execute different code blocks based on a condition.

IF EXISTS (SELECT * FROM Products WHERE ProductID = 7)
BEGIN
    PRINT 'Product with ID 7 exists.';
END
ELSE
BEGIN
    PRINT 'Product with ID 7 does not exist.';
END

WHILE Loop

Repeat a block of code as long as a condition is true.

DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
    PRINT 'Iteration: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END

4. Basic SQL Statements

The core of T-SQL involves standard SQL operations.

SELECT

Retrieve data from one or more tables.

SELECT CustomerID, Name, Email
FROM Customers
WHERE City = 'London';

INSERT

Add new rows to a table.

INSERT INTO Products (ProductName, Price)
VALUES ('New Gadget', 49.99);

UPDATE

Modify existing data in a table.

UPDATE Customers
SET Email = 'updated.email@example.com'
WHERE CustomerID = 5;

DELETE

Remove rows from a table.

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

5. Joins

Combine rows from two or more tables based on a related column.

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN

SELECT
    o.OrderID,
    c.Name AS CustomerName
FROM
    Orders AS o
INNER JOIN
    Customers AS c ON o.CustomerID = c.CustomerID;

Next Steps

Continue learning about T-SQL by exploring topics like:

Aggregations (COUNT, SUM, AVG, MIN, MAX)
GROUP BY and HAVING clauses
Subqueries
Common Table Expressions (CTEs)
Window Functions
Error Handling and Transactions
Stored Procedures and Functions

Practice these fundamental concepts regularly to build a strong foundation in T-SQL and SQL Server development.

Microsoft Docs