Programmability: Views, Functions, Procedures, and Triggers

Reusable logic in the database

Views: saved queries

A view is a named SELECT statement. It does not store data — it runs the query every time you reference it.

CREATE VIEW vw_ActiveCustomers AS
SELECT c.CustomerId, c.FullName, c.Email, COUNT(o.OrderId) AS OrderCount
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE c.Status = 'Active'
GROUP BY c.CustomerId, c.FullName, c.Email;

-- Use it like a table
SELECT * FROM vw_ActiveCustomers WHERE OrderCount > 10;

Indexed views (materialised views)

For expensive views queried frequently, you can materialise the results with a unique clustered index:

CREATE VIEW vw_ProductSales WITH SCHEMABINDING AS
SELECT p.ProductId, p.Name, SUM(oi.Quantity) AS TotalSold, COUNT_BIG(*) AS RowCount
FROM dbo.Products p
INNER JOIN dbo.OrderItems oi ON p.ProductId = oi.ProductId
GROUP BY p.ProductId, p.Name;
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSales
ON vw_ProductSales (ProductId);

Requirements for indexed views: SCHEMABINDING, deterministic functions only, COUNT_BIG(*) for aggregations, no subqueries, outer joins, or UNION.

Scalar functions: one value out

A scalar function takes parameters and returns a single value.

CREATE FUNCTION dbo.fn_CalculateDiscount(
    @Price DECIMAL(10,2),
    @Tier NVARCHAR(20)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CASE @Tier
        WHEN 'Gold' THEN @Price * 0.20
        WHEN 'Silver' THEN @Price * 0.10
        WHEN 'Bronze' THEN @Price * 0.05
        ELSE 0
    END;
END;

-- Use in a query
SELECT Name, Price, dbo.fn_CalculateDiscount(Price, CustomerTier) AS Discount
FROM Products;

💡 Exam tip: Scalar function performance trap

Traditional T-SQL scalar functions are called row by row — they cannot be parallelised. For a table with 10 million rows, a scalar function in the SELECT list runs 10 million times sequentially.

Mitigation strategies:

• Use inline table-valued functions instead (the optimiser can expand them)
• Use computed columns for simple calculations
• In SQL Server 2019+, scalar UDF inlining can automatically convert eligible scalar functions into inline expressions

The exam may present a slow query that uses a scalar function — the fix is often replacing it with an inline TVF or computed column.

Table-valued functions: a table out

Table-valued functions return a result set. There are two types:

Inline TVF (preferred)

-- Inline TVF: single SELECT statement, optimiser can expand it
CREATE FUNCTION dbo.fn_GetCustomerOrders(@CustomerId INT)
RETURNS TABLE
AS
RETURN (
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId
);

-- Use with CROSS APPLY
SELECT c.FullName, o.OrderDate, o.TotalAmount
FROM Customers c
CROSS APPLY dbo.fn_GetCustomerOrders(c.CustomerId) o;

Multi-statement TVF

-- Multi-statement TVF: declares a table variable, populates it with logic
CREATE FUNCTION dbo.fn_GetOrderSummary(@StartDate DATE, @EndDate DATE)
RETURNS @Summary TABLE (
    ProductId INT,
    ProductName NVARCHAR(200),
    TotalQuantity INT,
    TotalRevenue DECIMAL(12,2)
)
AS
BEGIN
    INSERT INTO @Summary
    SELECT p.ProductId, p.Name, SUM(oi.Quantity), SUM(oi.Quantity * oi.UnitPrice)
    FROM Products p
    INNER JOIN OrderItems oi ON p.ProductId = oi.ProductId
    INNER JOIN Orders o ON oi.OrderId = o.OrderId
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductId, p.Name;
    RETURN;
END;

Function types — the exam favours inline TVFs for performance

Feature	Inline TVF	Multi-Statement TVF	Scalar Function
Returns	Table (single SELECT)	Table (populated with logic)	Single value
Performance	Best — optimiser inlines it	Slower — treated as a black box	Slowest — row-by-row execution
Can modify data?	No	No	No
Parallelism	Yes	Limited	No (unless inlined)
Use when	Parameterised views, reusable queries	Complex multi-step logic returning rows	Simple calculations on single values

Stored procedures: full operations

Stored procedures can do everything: SELECT, INSERT, UPDATE, DELETE, call other procedures, use transactions, and return multiple result sets.

CREATE PROCEDURE dbo.usp_ProcessOrder
    @CustomerId INT,
    @ProductId INT,
    @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Check stock
        DECLARE @Stock INT;
        SELECT @Stock = StockLevel FROM Products WHERE ProductId = @ProductId;

        IF @Stock < @Quantity
            THROW 50001, 'Insufficient stock.', 1;

        -- Create order
        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, GETUTCDATE(), 'Pending');

        DECLARE @OrderId INT = SCOPE_IDENTITY();

        -- Add line item and reduce stock
        INSERT INTO OrderItems (OrderId, ProductId, Quantity)
        VALUES (@OrderId, @ProductId, @Quantity);

        UPDATE Products SET StockLevel = StockLevel - @Quantity
        WHERE ProductId = @ProductId;

        COMMIT TRANSACTION;
        SELECT @OrderId AS NewOrderId;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Triggers: automatic reactions

Triggers fire automatically when a DML event (INSERT, UPDATE, DELETE) occurs. They use the special INSERTED and DELETED virtual tables.

CREATE TRIGGER trg_AuditPriceChange
ON Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(Price)
    BEGIN
        INSERT INTO PriceAuditLog (ProductId, OldPrice, NewPrice, ChangedAt, ChangedBy)
        SELECT d.ProductId, d.Price, i.Price, GETUTCDATE(), SYSTEM_USER
        FROM INSERTED i
        INNER JOIN DELETED d ON i.ProductId = d.ProductId
        WHERE i.Price <> d.Price;
    END;
END;

💡 Exam tip: When NOT to use triggers

Triggers are powerful but dangerous:

• They run inside the same transaction (a slow trigger slows every INSERT/UPDATE)
• They can fire other triggers (cascading trigger chains)
• They are invisible to application developers (hard to debug)
• They cannot be easily unit tested

Better alternatives:

• Temporal tables replace audit triggers
• Change Data Capture (CDC) or Change Event Streaming (CES) replace data sync triggers
• Stored procedures with explicit logic replace business rule triggers

Use triggers only when you need guaranteed, engine-level enforcement that cannot be bypassed.

Question

What is the performance difference between inline TVFs and multi-statement TVFs?

Click or press Enter to reveal answer

Answer

Inline TVFs are a single SELECT statement that the query optimiser can expand and optimise like a subquery. Multi-statement TVFs are treated as black boxes — the optimiser cannot see inside them, leading to poor cardinality estimates and slower execution.

Click to flip back

Question

What are the INSERTED and DELETED virtual tables in a trigger?

Click or press Enter to reveal answer

Answer

INSERTED contains the new row values (for INSERT and UPDATE triggers). DELETED contains the old row values (for UPDATE and DELETE triggers). For an UPDATE, INSERTED has the new values and DELETED has the old values — comparing them lets you detect which columns changed.

Click to flip back

Question

What is SCHEMABINDING on a view?

Click or press Enter to reveal answer

Answer

SCHEMABINDING prevents the underlying tables from being altered or dropped while the view exists. It is required for indexed (materialised) views and ensures the view definition stays valid.

Click to flip back

Knowledge Check

Ingrid at Nordic Shield Insurance has a query that calculates the risk score for each policy using a complex formula involving 6 columns. The query runs on 5 million rows and is unacceptably slow because the risk calculation is in a scalar function. What should Ingrid do?

ARewrite the scalar function as a multi-statement table-valued functionBRewrite the scalar function as an inline table-valued function and use CROSS APPLYCAdd an index to speed up the scalar functionDCache the results in a temporary table

Check Answer

Knowledge Check

Dev at PixelForge Studios needs to automatically log every deletion from the Projects table, capturing who deleted the record and when. The log must be guaranteed — no application can skip it. Which approach is most appropriate?

AA temporal table with system versioningBAn AFTER DELETE trigger that inserts into an audit log tableCApplication-level logging in the delete API endpointDA CHECK constraint that prevents deletions

Check Answer

🎬 Video coming soon

Next up: Advanced T-SQL: CTEs, Windows, and Correlated Queries — write powerful queries with common table expressions, window functions, and correlated subqueries.