T-SQL Fundamentals: Everything You Need to Know to Go from Beginner to Pro

Leave a Comment / Articles, Scripts / By

Introduction

Transact-SQL (T-SQL) is Microsoft’s extension of the SQL language and the backbone of Microsoft SQL Server and Azure SQL Database. It powers querying, data manipulation, schema definition, and business logic. Whether you’re building analytics dashboards, powering web applications, or managing large-scale data systems, T-SQL is an essential skill.

This guide is designed to take you from beginner to advanced practitioner. We’ll start with the fundamentals, move through complex queries and performance tuning, then dive into programmability and new features introduced in SQL Server 2022 and the 2025 preview. Each section includes real-world examples, inline references, and a hands-on project you can build yourself.

Thinking in Sets: The T-SQL Mindset

T-SQL is a declarative, set-based language, which means you describe what you want, not how to get it. The SQL engine decides the most efficient way to retrieve your data.

Procedural logic (not T-SQL style):

-- Procedural thinking
FOR EACH employee
  IF department = 'Sales'
    PRINT name

Set-based T-SQL approach:

SELECT Name
FROM Employees
WHERE Department = 'Sales';

This set-based mindset is essential to writing efficient queries.
Microsoft Docs: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16

Core Query Basics

The SELECT Statement

SELECT retrieves data from one or more tables:

SELECT FirstName, LastName, Department
FROM Employees;

Key clauses:

  • SELECT – columns to return
  • FROM – source tables
  • WHERE – filter rows
  • ORDER BY – sort results

Example with filters and sorting:

SELECT FirstName, LastName, Department
FROM Employees
WHERE Department = 'Sales'
ORDER BY LastName ASC;

SELECT Reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16

Filtering with WHERE

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate <= '2025-12-31';

Use comparison (=, <, >) and logical (AND, OR, NOT) operators.
WHERE Clause: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16#where-clause

Sorting with ORDER BY

SELECT ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC;

ORDER BY Documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16

Intermediate: Combining and Aggregating Data

Joining Tables

Combine data from multiple tables with joins:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
  ON e.DepartmentID = d.DepartmentID;
  • INNER JOIN – matching rows
  • LEFT JOIN – all left rows + matches
  • RIGHT JOIN – all right rows + matches
  • FULL JOIN – all rows from both sides

Join Types: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16#join

Aggregating with GROUP BY

SELECT DepartmentID, COUNT(*) AS NumEmployees, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;

Aggregate functions: COUNT, SUM, AVG, MIN, MAX
GROUP BY Reference: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

Subqueries and CTEs

Subquery:

SELECT FirstName, LastName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Common Table Expression (CTE):

WITH HighEarners AS (
  SELECT EmployeeID, FirstName, LastName, Salary
  FROM Employees
  WHERE Salary > 100000
)
SELECT * FROM HighEarners;

CTE Documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

Advanced Query Techniques

Window Functions

Window functions compute values across sets of rows without grouping them:

SELECT
  EmployeeID,
  Salary,
  AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvg,
  RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

(Window Functions: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

Set Operators

SELECT CustomerID FROM Orders2024
UNION
SELECT CustomerID FROM Orders2025;
  • UNION – removes duplicates
  • UNION ALL – includes duplicates
  • INTERSECT – common rows
  • EXCEPT – rows in first not in second

Set Operators Guide: https://learn.microsoft.com/en-us/sql/t-sql/queries/set-operators-transact-sql?view=sql-server-ver16

Modifying Data

INSERT, UPDATE, DELETE

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES ('Alice', 'Wong', 3, 75000);

UPDATE Employees
SET Salary = Salary * 1.05
WHERE DepartmentID = 3;

DELETE FROM Employees
WHERE EmployeeID = 99;

INSERT Reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16

MERGE

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.Key = S.Key
WHEN MATCHED THEN
  UPDATE SET T.Value = S.Value
WHEN NOT MATCHED THEN
  INSERT (Key, Value) VALUES (S.Key, S.Value)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

MERGE Documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

Programmability

Stored Procedures

CREATE PROCEDURE GetSalesByRegion @Region NVARCHAR(50)
AS
BEGIN
  SELECT c.Region, SUM(ol.Quantity * ol.UnitPrice) AS TotalSales
  FROM Orders o
  JOIN Customers c ON o.CustomerID = c.CustomerID
  JOIN OrderLines ol ON o.OrderID = ol.OrderID
  WHERE c.Region = @Region
  GROUP BY c.Region;
END;

Stored Procedures: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver16

User-Defined Functions

CREATE FUNCTION GetFullName(@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
  RETURN @First + ' ' + @Last;
END;

UDF Reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16

Triggers

CREATE TRIGGER trg_AuditInsert
ON Employees
AFTER INSERT
AS
BEGIN
  INSERT INTO AuditLog (Action, DateTime)
  SELECT 'INSERT', GETDATE();
END;

Triggers: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16

Transactions and Concurrency

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT;

Transactions Guide:https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-ver16
Isolation Levels:https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

Isolation levels: READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE (Isolation Levels)

Performance Tuning Essentials

Indexing

CREATE INDEX IX_Employees_DepartmentID ON Employees(DepartmentID);

Index Design Guide: https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver16

Execution Plans

Analyze query plans to diagnose performance issues.
Execution Plans: https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver16

Modern Features (2022–2025)

Novice-to-Expert Roadmap

Novice: SELECT, WHERE, ORDER BY
Intermediate: JOINs, GROUP BY, CTEs
Advanced: Window functions, indexing, transactions
Expert: Stored procedures, triggers, temporal tables, vector search

Workshop: Build a Sales Reporting System

Step 1: Create Schema

CREATE TABLE Customers (
  CustomerID INT IDENTITY PRIMARY KEY,
  CustomerName NVARCHAR(100),
  Region NVARCHAR(50)
);
CREATE TABLE Products (
  ProductID INT IDENTITY PRIMARY KEY,
  ProductName NVARCHAR(100),
  Category NVARCHAR(50),
  UnitPrice DECIMAL(10,2)
);
CREATE TABLE Orders (
  OrderID INT IDENTITY PRIMARY KEY,
  OrderDate DATE,
  CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
);
CREATE TABLE OrderLines (
  OrderLineID INT IDENTITY PRIMARY KEY,
  OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),
  ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
  Quantity INT,
  UnitPrice DECIMAL(10,2)
);

Step 2: Insert Data

INSERT INTO Customers VALUES ('Contoso', 'West'), ('Northwind', 'East');
INSERT INTO Products VALUES ('Laptop', 'Electronics', 1200), ('Mouse', 'Accessories', 25);
INSERT INTO Orders VALUES ('2025-10-01', 1), ('2025-10-02', 2);
INSERT INTO OrderLines VALUES (1, 1, 2, 1200), (1, 2, 5, 25);

Step 3: Query the Data

Total Sales by Region:

SELECT c.Region, SUM(ol.Quantity * ol.UnitPrice) AS TotalSales
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderLines ol ON o.OrderID = ol.OrderID
GROUP BY c.Region;

Top Customers:

SELECT TOP 5 c.CustomerName, SUM(ol.Quantity * ol.UnitPrice) AS TotalSpent
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderLines ol ON o.OrderID = ol.OrderID
GROUP BY c.CustomerName
ORDER BY TotalSpent DESC;

Step 4: Create a Stored Procedure

CREATE PROCEDURE usp_GetSalesByDateRange
  @StartDate DATE,
  @EndDate DATE
AS
BEGIN
  SELECT c.CustomerName, SUM(ol.Quantity * ol.UnitPrice) AS TotalSales
  FROM Orders o
  JOIN Customers c ON o.CustomerID = c.CustomerID
  JOIN OrderLines ol ON o.OrderID = ol.OrderID
  WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
  GROUP BY c.CustomerName;
END;

Execute it:

EXEC usp_GetSalesByDateRange @StartDate = '2025-01-01', @EndDate = '2025-12-31';

Step 5: Optimize with Indexes

CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);
CREATE INDEX IX_OrderLines_OrderID ON OrderLines(OrderID);

Step 6: View Execution Plans

Use Actual Execution Plan in SSMS to see how your queries run and improve performance (Execution Plan Guide).

Final Thoughts

T-SQL is more than just a query language — it’s a complete platform for building, managing, and scaling data systems. Mastering it requires understanding both the syntax and the engine behind it. With these fundamentals, advanced techniques, and workshop experience, you’re equipped to build real-world database solutions.

References

✅ You’re now ready to go from beginner to pro with T-SQL. Practice often, analyze execution plans, and keep exploring new features in SQL Server 2025 to stay ahead.

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

Related Posts

Leave a Reply

Contact us
Contact us

Discover more from SQLYARD

Subscribe now to keep reading and get access to the full archive.

Continue reading