Microsoft SQL Server Query Cookbook

Microsoft's enterprise relational database system powered by T-SQL dialect. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Select Top N Records with TOP

Restrict result set size using TOP clause.

SELECT TOP (10) order_id, customer_id, total_amount
FROM dbo.Orders
ORDER BY order_date DESC;

#top#limit#tsql

2. Correlated Subquery with CROSS APPLY

Join top correlated record per parent row.

SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate
FROM dbo.Customers c
CROSS APPLY (
  SELECT TOP (1) OrderID, OrderDate
  FROM dbo.Orders
  WHERE CustomerID = c.CustomerID
  ORDER BY OrderDate DESC
) o;

#cross_apply#apply#tsql

3. Safe Type Conversion with TRY_CAST

Convert text strings to numbers safely without throwing runtime errors.

SELECT RawValue,
       TRY_CAST(RawValue AS INT) AS CleanInteger
FROM dbo.StagingData
WHERE TRY_CAST(RawValue AS INT) IS NOT NULL;

#try_cast#conversion#error_handling

4. Aggregate Text with STRING_AGG

Combine group text values into delimited string.

SELECT DepartmentID,
       STRING_AGG(FirstName, ', ') WITHIN GROUP (ORDER BY FirstName) AS EmployeeList
FROM dbo.Employees
GROUP BY DepartmentID;

#string_agg#tsql#aggregate

5. Create & Drop Local Temp Table (#table)

Store temporary intermediate results.

CREATE TABLE #MonthlyStats (
  MonthID INT,
  TotalRev DECIMAL(18,2)
);

INSERT INTO #MonthlyStats VALUES (1, 50000.00);
SELECT * FROM #MonthlyStats;
DROP TABLE #MonthlyStats;

#temp_table#session#tsql

6. Synchronize Data with MERGE

Execute UPSERT logic matching source and target tables.

MERGE INTO dbo.TargetUsers AS t
USING dbo.SourceUsers AS s ON (t.UserID = s.UserID)
WHEN MATCHED THEN
  UPDATE SET t.Email = s.Email
WHEN NOT MATCHED THEN
  INSERT (UserID, Email) VALUES (s.UserID, s.Email);

#merge#upsert#tsql

7. Check Index Fragmentation Levels

Retrieve DMV fragmentation statistics to optimize slow indexes.

SELECT object_name(object_id) AS TableName, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC;

#dba#index#fragmentation

8. Dynamic Reporting with PIVOT

Rotate unique row values into column headers.

SELECT Year, [1] AS Jan, [2] AS Feb
FROM (
  SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, Total
  FROM dbo.Orders
) AS Src
PIVOT (
  SUM(Total) FOR Month IN ([1], [2])
) AS pvt;

#pivot#analytics#reporting

9. Compare Next Order with LEAD()

Fetch subsequent record value within customer partition.

SELECT CustomerID, OrderDate, TotalAmount,
       LEAD(OrderDate, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate
FROM dbo.Orders;

#window#lead#analytics

10. Allow Explicit Identity Insert

Override automatic identity constraint for data migration.

SET IDENTITY_INSERT dbo.Customers ON;
INSERT INTO dbo.Customers (CustomerID, CompanyName) VALUES (999, 'Migrated Co');
SET IDENTITY_INSERT dbo.Customers OFF;

#identity#insert#tsql

Test your Microsoft SQL Server architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study Microsoft SQL Server Flashcards →