Microsoft SQL Server (T-SQL): Beginner to Intermediate Handbook

Module 1: Server Architecture & Core Objects

SQL Server is layered from the instance down. The instance is the installed SQL Server engine. Inside it you host many databases. Inside each database you create schemas, and inside schemas you create tables, views, procedures, and functions. Think of the instance as the office building, databases as the individual companies renting floors, schemas as departments, and tables as the filing cabinets each department owns.

Schemas create boundaries. Dumping every object into dbo works until the database grows and no one can tell which team owns which object. Custom schemas such as Sales and HR give you cleaner permissions, clearer intent, and fewer naming conflicts. Use two-part naming everywhere so the execution context is explicit.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'Sales')
BEGIN
    EXEC ('CREATE SCHEMA Sales');
END;
GO

CREATE TABLE Sales.Orders (
    OrderID INT IDENTITY(1,1) NOT NULL,
    CustomerEmail NVARCHAR(255) NOT NULL,
    OrderStatus NVARCHAR(50) NOT NULL,
    OrderDate DATETIME2 NOT NULL CONSTRAINT DF_Orders_OrderDate DEFAULT SYSUTCDATETIME(),
    TotalAmount DECIMAL(12, 2) NOT NULL,
    CreatedBy NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_Sales_Orders PRIMARY KEY (OrderID)
);
GO

Module 2: Data Integrity & Basic CRUD

Constraints keep data trustworthy. Primary keys prevent duplicate row identity. Foreign keys ensure related rows actually exist. NOT NULL prevents missing required values. DEFAULT fills common values automatically. The database should reject bad data before your application has a chance to normalize it incorrectly.

CREATE TABLE Sales.Customers (
    CustomerID INT IDENTITY(1,1) NOT NULL,
    CustomerName NVARCHAR(150) NOT NULL,
    Email NVARCHAR(255) NOT NULL,
    IsActive BIT NOT NULL CONSTRAINT DF_Customers_IsActive DEFAULT (1),
    RegisteredAt DATETIME2 NOT NULL CONSTRAINT DF_Customers_RegisteredAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Sales_Customers PRIMARY KEY (CustomerID),
    CONSTRAINT UQ_Sales_Customers_Email UNIQUE (Email)
);
GO

CREATE TABLE Sales.CustomerOrders (
    OrderID INT IDENTITY(1,1) NOT NULL,
    CustomerID INT NOT NULL,
    OrderStatus NVARCHAR(50) NOT NULL,
    TotalAmount DECIMAL(12, 2) NOT NULL,
    OrderDate DATETIME2 NOT NULL CONSTRAINT DF_CustomerOrders_OrderDate DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Sales_CustomerOrders PRIMARY KEY (OrderID),
    CONSTRAINT FK_Sales_CustomerOrders_Customers FOREIGN KEY (CustomerID)
        REFERENCES Sales.Customers (CustomerID)
);
GO

CRUD Operations

Insert and capture the new identity value with SCOPE_IDENTITY(). That returns the last identity value generated in the current scope, which is what you usually need immediately after creating a row.

INSERT INTO Sales.Customers (CustomerName, Email)
VALUES (N'Ava Chen', N'ava.chen@example.com');

DECLARE @NewCustomerID INT;
SET @NewCustomerID = CAST(SCOPE_IDENTITY() AS INT);

SELECT @NewCustomerID AS NewCustomerID;

SELECT CustomerID, CustomerName, Email, IsActive, RegisteredAt
FROM Sales.Customers
WHERE IsActive = 1
ORDER BY RegisteredAt DESC;

UPDATE Sales.Customers
SET CustomerName = N'Ava Chen-Singh'
WHERE CustomerID = 1;

DELETE FROM Sales.Customers
WHERE CustomerID = 1;

Module 3: Filtering & Aggregation

Filtering defines the business slice you care about. If finance asks for Q1 orders over a threshold, the filter is the contract. SQL Server then narrows the rows before any aggregation or reporting logic runs.

SELECT OrderID, CustomerID, OrderStatus, TotalAmount, OrderDate
FROM Sales.CustomerOrders
WHERE OrderStatus = N'Paid';

SELECT OrderID, CustomerID, OrderStatus
FROM Sales.CustomerOrders
WHERE OrderStatus IN (N'Paid', N'Shipped', N'Delivered');

SELECT OrderID, TotalAmount, OrderDate
FROM Sales.CustomerOrders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-03-31';

SELECT CustomerID, CustomerName, Email
FROM Sales.Customers
WHERE CustomerName LIKE N'%Chen%';

Aggregate queries roll detailed rows into business summaries. GROUP BY tells SQL Server the level of the summary, and HAVING filters the grouped result.

SELECT
    c.CustomerID,
    c.CustomerName,
    SUM(o.TotalAmount) AS TotalSalesAmount,
    COUNT(*) AS OrderCount
FROM Sales.Customers AS c
INNER JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID
WHERE o.OrderStatus IN (N'Paid', N'Shipped', N'Delivered')
GROUP BY c.CustomerID, c.CustomerName
HAVING SUM(o.TotalAmount) > 1000.00
ORDER BY TotalSalesAmount DESC;

Module 4: The Power of Joins

Normalization splits business facts into separate tables. Customers, orders, and products are stored independently so each fact is maintained in one place. Joins reconstruct the business picture when you query it.

INNER JOIN

SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Sales.Customers AS c
INNER JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID;

LEFT JOIN

SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID,
    o.OrderStatus
FROM Sales.Customers AS c
LEFT JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID;

RIGHT JOIN and FULL OUTER JOIN

These are valid but less common. Most teams prefer LEFT JOIN because it reads naturally from the preserved table. FULL OUTER JOIN is useful for reconciliation work where you need unmatched rows from both sides.

SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID
FROM Sales.Customers AS c
RIGHT JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID;

SELECT
    c.CustomerID,
    c.CustomerName,
    o.OrderID
FROM Sales.Customers AS c
FULL OUTER JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID;

Find Registered Customers Who Never Placed an Order

SELECT
    c.CustomerID,
    c.CustomerName,
    c.Email,
    c.RegisteredAt
FROM Sales.Customers AS c
LEFT JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID
WHERE o.OrderID IS NULL
ORDER BY c.RegisteredAt DESC;

Module 5: Views for Abstraction

A view is a virtual table. It packages a query behind a stable object name, so your API or reporting layer can consume one clean object instead of rewriting joins everywhere.

CREATE OR ALTER VIEW Sales.vCustomerOrderSummary
AS
SELECT
    c.CustomerID,
    c.CustomerName,
    c.Email,
    COUNT(o.OrderID) AS OrderCount,
    ISNULL(SUM(o.TotalAmount), 0.00) AS LifetimeValue,
    MAX(o.OrderDate) AS LastOrderDate
FROM Sales.Customers AS c
LEFT JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName, c.Email;
GO

SELECT *
FROM Sales.vCustomerOrderSummary
WHERE LifetimeValue >= 500.00;
GO

Module 6: Temporary Structures (Crucial T-SQL)

Local Temp Tables (#TempTable)

Use local temp tables when the intermediate dataset is sizeable or needs indexes. They are scoped to the current session and are usually the best default for non-trivial temporary work.

CREATE TABLE #RecentOrders (
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(12, 2) NOT NULL,
    OrderDate DATETIME2 NOT NULL
);

INSERT INTO #RecentOrders (OrderID, CustomerID, TotalAmount, OrderDate)
SELECT OrderID, CustomerID, TotalAmount, OrderDate
FROM Sales.CustomerOrders
WHERE OrderDate >= DATEADD(DAY, -30, SYSUTCDATETIME());

SELECT *
FROM #RecentOrders;

DROP TABLE #RecentOrders;

Global Temp Tables (##TempTable)

Global temp tables are visible beyond the current session. They are rarely appropriate in modern applications because names can collide and different sessions can interfere with each other. Reserve them for controlled administrative scenarios.

CREATE TABLE ##SharedImportBuffer (
    RowID INT IDENTITY(1,1) PRIMARY KEY,
    Payload NVARCHAR(4000) NOT NULL
);

DROP TABLE ##SharedImportBuffer;

Table Variables (@TableVar)

Table variables are lightweight but not magic. They are best for very small datasets. For larger intermediate results, especially those that benefit from indexing or accurate row estimates, prefer #TempTables.

DECLARE @TopCustomers TABLE (
    CustomerID INT NOT NULL,
    CustomerName NVARCHAR(150) NOT NULL,
    LifetimeValue DECIMAL(12, 2) NOT NULL
);

INSERT INTO @TopCustomers (CustomerID, CustomerName, LifetimeValue)
SELECT TOP (5)
    c.CustomerID,
    c.CustomerName,
    SUM(o.TotalAmount) AS LifetimeValue
FROM Sales.Customers AS c
INNER JOIN Sales.CustomerOrders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName
ORDER BY LifetimeValue DESC;

SELECT *
FROM @TopCustomers;

Module 7: Basic T-SQL Programming Constructs

Variables and Flow Control

T-SQL is procedural when needed. You can declare variables, branch with IF...ELSE, and group statements with BEGIN...END. This is useful in scripts, stored procedures, and controlled maintenance logic.

DECLARE @CustomerID INT;
DECLARE @OrderCount INT;

SET @CustomerID = 42;

SELECT @OrderCount = COUNT(*)
FROM Sales.CustomerOrders
WHERE CustomerID = @CustomerID;

IF @OrderCount = 0
BEGIN
    PRINT N'Customer has not placed any orders yet.';
END
ELSE
BEGIN
    PRINT N'Customer already has order history.';
END;

Error Handling with TRY...CATCH

Wrap risky operations in a TRY...CATCH block. That gives you a controlled failure path instead of letting the batch die without context.

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Sales.CustomerOrders (CustomerID, OrderStatus, TotalAmount)
    VALUES (42, N'Paid', 129.99);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine;
END CATCH;

Reference Links

• Docs SQL Server documentation
• T-SQL T-SQL language reference
• TempDB tempdb reference
• Errors TRY...CATCH