Advanced Transact-SQL (T-SQL) Queries

Transact-SQL (T-SQL) is a powerful extension of SQL used in Microsoft SQL Server. Mastering advanced T-SQL queries can significantly enhance your ability to manipulate and retrieve data efficiently. In this post, we'll explore some advanced T-SQL techniques and provide practical examples to help you get the most out of your SQL Server.

Why Advanced T-SQL?

Advanced T-SQL queries allow you to perform complex data manipulations, optimize performance, and implement sophisticated business logic directly within your database. This can lead to more efficient and maintainable code.

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are a powerful feature in T-SQL that allows you to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example: Recursive CTE

A recursive CTE can be used to perform hierarchical queries, such as retrieving an organizational chart.

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Window Functions

Window functions perform calculations across a set of table rows related to the current row. They are useful for running totals, moving averages, and ranking.

Example: Ranking Employees by Salary

SELECT EmployeeID, EmployeeName, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

Dynamic SQL

Dynamic SQL allows you to construct and execute SQL statements dynamically at runtime. This is useful for scenarios where the SQL query needs to be generated based on varying conditions.

Example: Dynamic SQL for Conditional Filtering

DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(50) = 'Sales';

SET @sql = 'SELECT * FROM Employees WHERE Department = @filter';
EXEC sp_executesql @sql, N'@filter NVARCHAR(50)', @filter;

Pivot and Unpivot

Pivot and Unpivot operations transform rows into columns and vice versa. This is particularly useful for reporting and data analysis.

Example: Pivoting Sales Data

SELECT ProductID, [2023] AS Sales2023, [2024] AS Sales2024
FROM (
    SELECT ProductID, Year, Sales
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN ([2023], [2024])
) AS PivotTable;

Advanced Joins

Advanced joins, such as CROSS APPLY and OUTER APPLY, allow you to join a table with a table-valued function or a subquery.

Example: Using CROSS APPLY

SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
FROM Employees e
CROSS APPLY (
    SELECT DepartmentName
    FROM Departments d
    WHERE d.DepartmentID = e.DepartmentID
) AS dept;

Indexed Views

Indexed views can improve the performance of complex queries by storing the result set of the view in the database.

Example: Creating an Indexed View

CREATE VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON SalesSummary(ProductID);

Table-Valued Parameters

Table-Valued Parameters (TVPs) allow you to pass multiple rows of data to a stored procedure or function.

Example: Using Table-Valued Parameters

1.Create a User-Defined Table Type

   CREATE TYPE dbo.EmployeeType AS TABLE
   (
       EmployeeID INT,
       EmployeeName NVARCHAR(50)
   );

2.Create a Stored Procedure that Accepts TVP

   CREATE PROCEDURE InsertEmployees
   @Employees dbo.EmployeeType READONLY
   AS
   BEGIN
       INSERT INTO Employees (EmployeeID, EmployeeName)
       SELECT EmployeeID, EmployeeName FROM @Employees;
   END;

3.Execute the Stored Procedure with TVP

   DECLARE @NewEmployees dbo.EmployeeType;

   INSERT INTO @NewEmployees (EmployeeID, EmployeeName)
   VALUES (1, 'John Doe'), (2, 'Jane Smith');

   EXEC InsertEmployees @NewEmployees;

JSON Data Handling

SQL Server provides functions to parse and manipulate JSON data, making it easier to work with JSON directly in your database.

Example: Parsing JSON Data

DECLARE @json NVARCHAR(MAX) = N'[
    {"EmployeeID": 1, "EmployeeName": "John Doe"},
    {"EmployeeID": 2, "EmployeeName": "Jane Smith"}
]';

SELECT EmployeeID, EmployeeName
FROM OPENJSON(@json)
WITH (
    EmployeeID INT,
    EmployeeName NVARCHAR(50)
);

Temporal Tables

Temporal tables automatically track the history of data changes, making it easier to perform time-based analysis.

Example: Creating a Temporal Table

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    DepartmentID INT,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

Benefits of Advanced T-SQL

• Efficiency: Perform complex data manipulations directly in the database.
• Maintainability: Centralize business logic within the database.
• Performance: Optimize query performance with advanced techniques.

Conclusion

Mastering advanced T-SQL queries can greatly enhance your ability to work with SQL Server. By leveraging techniques such as CTEs, window functions, dynamic SQL, pivot operations, advanced joins, indexed views, table-valued parameters, JSON data handling, and temporal tables, you can write more efficient and powerful queries. Start experimenting with these examples and see how they can improve your database operations.