Library of Useful (Difficult) SQL scripts

Script arsenal to tackle SQL tasks

In SQL, we frequently stumble upon challenges, particularly when it comes to data deduplication, querying hierarchical structures, and performance optimization.

Vanquishing data duplicates

Maintain data integrity through effective deduplication approaches. Here's a snippet that removes duplicate rows while preserving the original entry:

WITH RankedDuplicates AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY DuplicateField ORDER BY CreationDate) AS rn
  FROM TableName
)
DELETE FROM RankedDuplicates
WHERE rn > 1; -- "There can be only one!" Highlander would be proud


Taming hierarchical queries

Hierarchical data structures can be deftly navigated with recursive CTEs, as shown:

WITH RecursiveCTE (ParentID, ChildID, Level) AS (
  SELECT ParentID, ChildID, 0 AS Level
  FROM hierarchyTable
  WHERE ParentID IS NULL
  UNION ALL
  SELECT h.ParentID, h.ChildID, Level + 1
  FROM hierarchyTable h
  INNER JOIN RecursiveCTE r ON h.ParentID = r.ChildID
)
SELECT * FROM RecursiveCTE; -- "It's a long way to the top if you want to SQL!"


Cranking up performance

Performance optimization is often achieved via effective indexing strategies. The following script is a neat tool for index analysis:

SELECT 
  OBJECT_NAME(i.OBJECT_ID) AS TableName, 
  i.name AS IndexName, 
  user_updates AS Updates, 
  user_seeks + user_scans + user_lookups AS Reads
FROM 
  sys.dm_db_index_usage_stats ius
JOIN 
  sys.indexes i ON ius.OBJECT_ID = i.OBJECT_ID 
  AND ius.index_id = i.index_id
WHERE 
  OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
ORDER BY 
  TableName, IndexName;


Breaking down complex tasks

Decompose complex tasks into simpler components for easier script maintenance and debugging.

Modular approach

Modularize your SQL scripts using CTEs to create temporary result sets that can be reused within a query.

Flexibility with Dynamic SQL

Dynamic SQL enables creation of flexible and adaptable queries:

DECLARE @TableName NVARCHAR(128) = N'Employees';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName);

EXEC sp_executesql @SQL; -- Presto! Your SQL query is now Harry Houdini!


By using sp_executesql, your dynamically generated SQL is perfectly executed and parameterization protects against potential SQL injection issues.

Handy SQL snippets

Store frequently used code blocks as templates or snippets in tools like SQL Server Management Studio (SSMS) or Azure Data Studio for consistent and efficient SQL scripting.

Advanced SQL Scripting

Advanced SQL scripting techniques can drastically optimize both performance and flexibility of your queries.

Pivots and Unpivots

Transform your data between the row-level and column-level perspectives using PIVOT and UNPIVOT functionality.

SELECT *
FROM 
  (SELECT ProductID, Month, SalesAmount FROM Sales) AS SourceTable
PIVOT
  (SUM(SalesAmount) FOR Month IN ([Jan], [Feb], [Mar], ..., [Dec])) AS PivotTable; -- "Round and round we go!" Pivot your data, not your chair.


Time-oriented data management

Temporal tables allow tracking data changes over time with system-versioned tables:

CREATE TABLE Employees
(
  EmployeeID INT PRIMARY KEY,
  Name NVARCHAR(100),
  Position NVARCHAR(100),
  Department NVARCHAR(100),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));


Beyond basic JOINS

Experiment with Cross Apply and Outer Apply to create correlated subqueries.

-- Extracting the latest order per customer
SELECT c.CustomerName, o.OrderID, o.OrderDate, o.TotalAmount
FROM Customers c
CROSS APPLY (
  SELECT TOP 1 OrderID, OrderDate, TotalAmount
  FROM Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) o; -- "The only way of catching a train…" Make sure you're on the right platform with proper JOINS!
