Advanced SQL Topics

Dive deeper into the capabilities of SQL with these advanced topics. Mastering these concepts can significantly enhance your ability to manage, query, and optimize complex databases.

Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. This is similar to the type of calculation that can be done with aggregate functions. However, window functions do not cause rows to be grouped into a single output row. Instead, they retain the separate rows. The SQL standard defines several types of window functions:

• Ranking functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
• Analytic functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()
• Aggregate functions: SUM(), AVG(), COUNT(), MIN(), MAX() (when used with an OVER() clause)

The general syntax involves the OVER() clause, which specifies how the window is defined:

SELECT
    column_name,
    window_function(arguments) OVER (
        [PARTITION BY partition_expression]
        [ORDER BY sort_expression]
        [frame_clause]
    ) AS alias
FROM
    table_name;

Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). CTEs can simplify complex queries by breaking them down into more manageable, logical parts.

Syntax:

WITH cte_name (column1, column2, ...) AS (
    -- SELECT statement that defines the CTE
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main query that uses the CTE
SELECT *
FROM cte_name
WHERE another_condition;

CTEs can be recursive, allowing you to query hierarchical data like organizational charts or bill of materials.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: Select the top-level employee
    SELECT EmployeeID, EmployeeName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: Select employees whose ManagerID matches an EmployeeID from the previous step
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Stored Procedures and Functions

Stored procedures and functions are precompiled SQL code blocks that can be executed on the database server. They offer benefits like improved performance, code reusability, and enhanced security.

• Stored Procedures: Can perform complex operations, return multiple result sets, and modify database state. They are invoked using EXECUTE or CALL.
• Functions: Designed to return a single value (scalar function) or a table (table-valued function). They can be used within SQL statements like regular functions.

Example Stored Procedure (Conceptual):

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

Example Scalar Function (Conceptual):

CREATE FUNCTION CalculateDiscountedPrice (@Price DECIMAL(10,2), @DiscountRate DECIMAL(3,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountRate);
END;

Triggers

Triggers are special stored procedures that automatically execute in response to certain events on a particular table or view. They are commonly used for:

• Enforcing complex business rules.
• Maintaining data integrity.
• Auditing changes.
• Automating related tasks.

Triggers can be defined to fire BEFORE or AFTER an INSERT, UPDATE, or DELETE operation.

CREATE TRIGGER trg_ProductPrice_Update
ON Products
AFTER UPDATE OF Price
AS
BEGIN
    IF UPDATE(Price)
    BEGIN
        -- Log the price change to an audit table
        INSERT INTO ProductPriceAudit (ProductID, OldPrice, NewPrice, ChangeDate)
        SELECT
            i.ProductID,
            d.Price AS OldPrice,
            i.Price AS NewPrice,
            GETDATE()
        FROM
            inserted i
        INNER JOIN
            deleted d ON i.ProductID = d.ProductID
        WHERE
            i.Price <> d.Price;
    END
END;

Indexing Strategies

Indexes are crucial for database performance, especially in large tables. They work like an index in a book, allowing the database to find rows quickly without scanning the entire table. Advanced indexing strategies involve:

• Clustered Indexes: Determine the physical order of data in the table. A table can have only one clustered index.
• Non-clustered Indexes: Store pointers to the actual data rows. A table can have multiple non-clustered indexes.
• Covering Indexes: Include all columns required for a specific query, eliminating the need to access the base table.
• Filtered Indexes: Index only a subset of rows in a table, useful for frequently queried partitions.
• Full-Text Indexes: For searching text data.

Transactions and Concurrency

Transactions are sequences of operations performed as a single logical unit of work. They must adhere to the ACID properties:

• Atomicity: All operations within a transaction are completed successfully, or none are.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction is committed, its changes are permanent.

Understanding transaction isolation levels (e.g., READ COMMITTED, REPEATABLE READ, SERIALIZABLE) is vital for managing concurrency and preventing issues like dirty reads, non-repeatable reads, and phantom reads.

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
COMMIT TRANSACTION; -- Or ROLLBACK TRANSACTION; if an error occurs.