3. Advanced SQL Constructs

This section covers advanced SQL constructs that allow developers to build sophisticated, modular, and high-performing database applications.

A. Stored Procedures: Benefits and Best Practices

• Encapsulation of Business Logic:
  Stored procedures allow you to encapsulate complex business logic on the server side. This centralizes the logic and can reduce network traffic by executing multiple operations in a single call.

  CREATE PROCEDURE GetEmployeeDetails
      @DepartmentID INT
  AS
  BEGIN
      SELECT employee_id, first_name, last_name
      FROM employees
      WHERE department_id = @DepartmentID;
  END;
  

• Performance and Security Benefits:

  • Precompiled Execution: Stored procedures are often precompiled or cached, which speeds up their execution.
  • Reduced risk of SQL injection because the input parameters are handled safely.
  • Easier to maintain and update business logic as it's centralized in the procedure rather than across multiple applications.

B. Triggers: Automating Tasks and Maintaining Data Integrity

• Use Cases:
  Triggers automatically execute in response to specific events (INSERT, UPDATE, DELETE). They are useful for auditing changes or enforcing business rules.

  CREATE TRIGGER trg_AuditEmployeeUpdate
  ON employees
  AFTER UPDATE
  AS
  BEGIN
      INSERT INTO employee_audit (employee_id, change_date, changed_by)
      SELECT employee_id, GETDATE(), SYSTEM_USER
      FROM inserted;
  END;
  

• Best Practices:

  • Keep triggers lightweight. Heavy logic can slow down the DML operations.
  • Avoid cascading triggers that call other triggers unless absolutely necessary.
  • Document triggers well so that their side-effects are clear to developers and DBAs.

C. Window Functions: Running Totals, Moving Averages, and Ranking

• Definition and Use-Cases:

  Window functions (also known as analytic functions) allow you to perform calculations across a set of related rows, or a “window” of rows, without collapsing them into a single output row. Unlike aggregate functions (e.g., SUM, AVG) used with GROUP BY, window functions preserve each row and add analytical context to it.

  They are especially useful in reporting, analytics, and trend analysis, where row-by-row comparisons and cumulative computations are common.

• Syntax Breakdown:
  <function_name>() OVER ( [PARTITION BY column] [ORDER BY column] [ROWS BETWEEN ...] )

  • PARTITION BY: Divides the result set into partitions to perform the window function independently in each.

  • ORDER BY: Determines the order of rows within the window.

  • ROWS BETWEEN: Specifies a window frame relative to the current row (e.g., 2 rows before to current).

• Common Use-Cases and Examples:

  1. Running Total

  Query:

  SELECT order_id, order_date, amount,
         SUM(amount) OVER (ORDER BY order_date) AS RunningTotal
  FROM orders;
  

  Input:

  order_id	order_date	amount
  1	2024-01-01	100
  2	2024-01-02	200
  3	2024-01-03	150

  Output:

  order_id	order_date	amount	RunningTotal
  1	2024-01-01	100	100
  2	2024-01-02	200	300
  3	2024-01-03	150	450

  ---

  2. Moving Average (3-Day Window)

  Query:

  SELECT order_id, order_date, amount,
         AVG(amount) OVER (
             ORDER BY order_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
         ) AS MovingAvg
  FROM orders;
  

  Input:

  order_id	order_date	amount
  1	2024-01-01	100
  2	2024-01-02	200
  3	2024-01-03	150
  4	2024-01-04	250

  Output:

  order_id	order_date	amount	MovingAvg
  1	2024-01-01	100	100.0
  2	2024-01-02	200	150.0
  3	2024-01-03	150	150.0
  4	2024-01-04	250	200.0

  Moving average is calculated over the current row and two before it (3 values total, or fewer if not available).

  ---

  3. Ranking by Salary

  Query:

  SELECT employee_id, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS SalaryRank
  FROM employees;
  

  Input:

  employee_id	department	salary
  101	Sales	5000
  102	Sales	7000
  103	Sales	5000
  201	HR	6000
  202	HR	6500

  Output:

  employee_id	department	salary	SalaryRank
  102	Sales	7000	1
  101	Sales	5000	2
  103	Sales	5000	2
  202	HR	6500	1
  201	HR	6000	2

  RANK() assigns the same rank to equal salaries and skips the next rank accordingly.

  ---

  4. Lag and Lead (Comparing with Previous and Next Rows)

  Query:

  SELECT order_id, order_date, amount,
         LAG(amount) OVER (ORDER BY order_date) AS PrevAmount,
         LEAD(amount) OVER (ORDER BY order_date) AS NextAmount
  FROM orders;
  

  Input:

  order_id	order_date	amount
  1	2024-01-01	100
  2	2024-01-02	200
  3	2024-01-03	150

  Output:

  order_id	order_date	amount	PrevAmount	NextAmount
  1	2024-01-01	100	NULL	200
  2	2024-01-02	200	100	150
  3	2024-01-03	150	200	NULL

• Benefits:

  • • Reduces the need for self-joins or subqueries.
    • Enhances performance by letting the DBMS optimize the window operations.