A query might look clean but run slow. Execution Plans show you if SQL is using an Index or doing a ‘Table Scan’. If you see ‘Scan’, you need an index.
Category: SQL
SQL: Calculate Running Totals with ROW_NUMBER() and OVER()
Window functions allow you to perform calculations across sets of rows related to the current row. SELECT Name, Salary, SUM(Salary) OVER(ORDER BY HireDate) as RunningTotal FROM Employees;
SQL: Use CTEs (Common Table Expressions) for Readable Queries
Stop using complex subqueries. CTEs make your SQL look like clean code. WITH MonthlySales AS ( SELECT ProductId, SUM(Amount) as Total FROM Sales GROUP BY ProductId ) SELECT * FROM MonthlySales WHERE Total > 1000;
SQL: Use Computed Columns for Automatic Calculated Values
Calculating values in application code requires logic duplication. Computed columns calculate automatically in database. Create Computed Column: CREATE TABLE Orders ( OrderId INT PRIMARY KEY, Quantity INT, PricePerUnit DECIMAL(10,2), — Computed column (calculated automatically) TotalPrice AS (Quantity * PricePerUnit), — Persisted (stored, can be indexed) TotalWithTax AS (Quantity * PricePerUnit * 1.1) PERSISTED ); Automatic […]
SQL: Use Window Functions for Running Totals and Ranking
Calculating running totals or row rankings with self-joins is complex. Window functions do it elegantly. Running Total: SELECT Date, Amount, SUM(Amount) OVER (ORDER BY Date) AS RunningTotal FROM Sales ORDER BY Date; — Output: — 2024-01-01 100 100 — 2024-01-02 150 250 — 2024-01-03 200 450 Rank Within Groups: SELECT Department, EmployeeName, Salary, RANK() OVER […]
SQL: Use STRING_AGG to Concatenate Rows into Comma-Separated List
Collecting multiple rows into one comma-separated string used to require cursors. STRING_AGG does it in one line. Basic Usage: — Get all tags for each post SELECT PostId, STRING_AGG(TagName, ‘, ‘) AS Tags FROM PostTags GROUP BY PostId; — Result: — PostId Tags — 1 CSS, HTML, JavaScript — 2 SQL, Database, Performance With Ordering: […]
SQL: Use Filtered Indexes to Index Only Subset of Rows
Regular index on status column with 95% ‘active’ rows is inefficient. Filter index to index only relevant rows. Problem – Full Index: — Index includes ALL rows (95% active, 5% inactive) CREATE INDEX IX_Orders_Status ON Orders(Status); — Query only needs inactive SELECT * FROM Orders WHERE Status = ‘inactive’; — Reads huge index to find […]
SQL: Use PIVOT to Transform Rows into Columns
Displaying row data as columns manually requires complex CASE statements. PIVOT does it automatically. Source Data: Month Product Sales January Widget 100 January Gadget 150 February Widget 120 February Gadget 180 PIVOT Query: SELECT * FROM ( SELECT Month, Product, Sales FROM SalesData ) AS SourceTable PIVOT ( SUM(Sales) FOR Product IN ([Widget], [Gadget]) ) […]
SQL: Use MERGE OUTPUT to Track What Changed During Upsert
After MERGE, don’t know what was inserted vs updated? OUTPUT clause captures the changes. MERGE with OUTPUT: MERGE INTO Products AS target USING @NewProducts AS source ON target.ProductId = source.ProductId WHEN MATCHED THEN UPDATE SET Price = source.Price WHEN NOT MATCHED THEN INSERT (ProductId, Name, Price) VALUES (source.ProductId, source.Name, source.Price) OUTPUT $action AS Action, INSERTED.ProductId, […]
SQL: Use EXPLAIN to See Query Execution Plan and Find Bottlenecks
Query slow but don’t know why? EXPLAIN shows exactly how database executes it. Basic Usage: EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123; — Output shows: — – Table scan or index usage — – Number of rows examined — – Join order — – Estimated cost PostgreSQL Analyze: EXPLAIN ANALYZE SELECT * FROM […]
SQL: Use CTEs to Make Complex Queries Readable
Nested subqueries are impossible to read. CTEs (Common Table Expressions) break them into named, readable chunks. Messy Nested Query: SELECT * FROM Orders o WHERE o.CustomerId IN ( SELECT CustomerId FROM Customers WHERE Country IN ( SELECT Country FROM TopCountries WHERE Revenue > 1000000 ) ); Clean CTE Version: WITH TopCountries AS ( SELECT Country […]
SQL: Use HAVING to Filter Aggregated Results
WHERE can’t filter aggregated data. HAVING filters after GROUP BY. Wrong – WHERE with Aggregate: SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders WHERE COUNT(*) > 5 — ERROR! Can’t use aggregate in WHERE GROUP BY CustomerID; Correct – HAVING: SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5; — Filters […]
SQL: Use CROSS JOIN to Generate All Possible Combinations
Need all combinations of two sets? CROSS JOIN creates cartesian product. Example – All Size/Color Combinations: — Sizes table: S, M, L — Colors table: Red, Blue, Green SELECT s.size, c.color FROM Sizes s CROSS JOIN Colors c; — Result (9 combinations): — S, Red — S, Blue — S, Green — M, Red — […]
SQL: Use TRY_CAST Instead of CAST to Avoid Conversion Errors
CAST throws error on invalid data. TRY_CAST returns NULL instead – safer for user input. CAST – Throws Error: SELECT CAST(‘123’ AS INT); — Works: 123 SELECT CAST(‘abc’ AS INT); — Error: Conversion failed! — Query crashes, returns nothing TRY_CAST – Returns NULL: SELECT TRY_CAST(‘123’ AS INT); — Returns: 123 SELECT TRY_CAST(‘abc’ AS INT); — […]
SQL: Use ISNULL vs COALESCE Correctly for Best Performance
Both replace NULL values but work differently. ISNULL is faster, COALESCE is more flexible. ISNULL – SQL Server Specific: — Takes exactly 2 arguments, returns first if not null SELECT ISNULL(MiddleName, ”) AS MiddleName FROM Users; — Faster – compiled inline — Data type from first argument COALESCE – ANSI Standard: — Takes 2+ arguments, […]
SQL: Use CHAR Instead of VARCHAR for Fixed-Length Columns to Save Space
Storing 2-letter country codes as VARCHAR(50)? Use CHAR for fixed-length data to optimize storage. When to Use CHAR: — Always exactly 2 characters CountryCode CHAR(2) — ‘US’, ‘UK’, ‘FR’ — Always exactly 1 character IsActive CHAR(1) — ‘Y’ or ‘N’ — Fixed format PhoneArea CHAR(3) — ‘555’, ‘212’, ‘415’ When to Use VARCHAR: — Variable […]
SQL: Use CROSS APPLY Instead of Subqueries for Better Performance
Correlated subqueries run once per row. CROSS APPLY runs more efficiently and is easier to read. Slow Subquery: SELECT c.CustomerName, (SELECT TOP 1 OrderDate FROM Orders o WHERE o.CustomerId = c.Id ORDER BY OrderDate DESC) AS LastOrderDate FROM Customers c; — Runs subquery for EVERY customer Fast CROSS APPLY: SELECT c.CustomerName, o.OrderDate AS LastOrderDate FROM […]
SQL: Use EXISTS Instead of COUNT for Checking Existence
Checking if records exist with COUNT(*) > 0 is slow. EXISTS stops at first match. Slow Way: IF (SELECT COUNT(*) FROM Orders WHERE CustomerId = 123) > 0 BEGIN PRINT ‘Customer has orders’ END — Counts ALL orders even though we only need to know if ANY exist Fast Way: IF EXISTS (SELECT 1 FROM […]
SQL: Use COALESCE to Replace NULL Values Inline
Don’t use CASE WHEN for simple NULL checks. COALESCE is cleaner. Verbose Way: SELECT CASE WHEN Name IS NULL THEN ‘Unknown’ ELSE Name END AS Name FROM Users; Clean Way: SELECT COALESCE(Name, ‘Unknown’) AS Name FROM Users; Multiple Fallbacks: SELECT COALESCE(MobilePhone, WorkPhone, HomePhone, ‘No phone’) AS Phone FROM Users; Returns first non-NULL value.
SQL Window Functions: Analytics Queries That Replace Hundreds of Lines of Code
Writing complex analytical queries with self-joins and subqueries? Window functions perform calculations across rows without grouping. Common Window Function Patterns: — Basic structure SELECT column1, column2, WINDOW_FUNCTION() OVER ( PARTITION BY partition_column ORDER BY sort_column ROWS/RANGE BETWEEN frame_start AND frame_end ) AS calculated_column FROM table_name; — Available functions: — Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() — […]
SQL Server Indexing Secret: How Covered Indexes Can Make Queries 100x Faster
Your SQL queries running slow even with indexes? Discover how covered indexes eliminate key lookups and dramatically improve performance. The Problem: Non-Covered Index — Table structure CREATE TABLE Orders ( Id INT PRIMARY KEY, CustomerId INT, OrderDate DATETIME, TotalAmount DECIMAL(10,2), Status VARCHAR(50), ShippingAddress NVARCHAR(500), — 20 more columns… ); — Common query SELECT CustomerId, OrderDate, […]
SQL: Use MERGE to Upsert (Insert or Update) in One Statement
Inserting if row doesn’t exist, updating if it does? MERGE statement does both in one atomic operation without race conditions. The Problem – Separate Insert/Update: — Check if exists IF EXISTS (SELECT 1 FROM Products WHERE ProductId = @Id) BEGIN — Update UPDATE Products SET ProductName = @Name, Price = @Price WHERE ProductId = @Id; […]
SQL: Use Window Functions to Calculate Running Totals Without Self-Joins
Need running totals, rankings, or moving averages? Window functions do it in one query without complex self-joins or cursors. The Old Painful Way – Self-Join: — Calculate running total of sales SELECT o1.OrderDate, o1.Amount, (SELECT SUM(o2.Amount) FROM Orders o2 WHERE o2.OrderDate
SQL: When CTEs Kill Performance and You Should Use Temp Tables Instead
Common Table Expressions (CTEs) make queries readable but can cause performance disasters when SQL Server evaluates them multiple times. The CTE Performance Trap: — ❌ BAD: CTE evaluated 3 times! WITH RecentOrders AS ( SELECT CustomerId, OrderDate, Total FROM Orders WHERE OrderDate >= DATEADD(month, -1, GETDATE()) — Complex calculation that takes 5 seconds ) SELECT […]
SQL Indexing: Why Your Query Ignores Indexes and Scans 10 Million Rows Instead
Created an index but your query still takes 30 seconds? SQL Server might be ignoring it due to implicit conversions, functions, or wildcard positioning. Index Killer #1 – Functions on Indexed Columns: — Index exists on CreatedDate CREATE INDEX IX_Orders_CreatedDate ON Orders(CreatedDate); — ❌ BAD: Index ignored, full table scan SELECT * FROM Orders WHERE […]
Fix Deadlocks by Understanding Lock Ordering and Using Proper Isolation Levels
Getting “Transaction was deadlocked” errors randomly? Deadlocks happen when two transactions lock resources in opposite order. Here’s how to fix them permanently. Identify Deadlocks – System Health Session: — SQL Server automatically logs deadlocks to system_health session SELECT CAST(target_data AS XML) AS deadlock_xml FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name […]
SQL Server: Find Slow Queries with Query Store (No Third-Party Tools Needed)
Application running slow but can’t figure out which queries are the culprits? Query Store is SQL Server’s built-in performance tracker that records everything. Enable Query Store (SQL Server 2016+): — Enable for your database ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE […]
Why COUNT(*) Gets Slow on “Big Enough” Tables
It’s not the count.It’s locking + metadata access. Reality COUNT must respect: Isolation level Pending writes Allocation metadata Production alternative Use approximate counts for dashboards. Why Accuracy isn’t always worth blocking.
Why Your Index Exists But Is Never Used
The index is fine.The predicate is not. Example WHERE YEAR(OrderDate) = 2024 Why it fails Functions on columns prevent index seeks. Rewrite WHERE OrderDate >= ‘2024-01-01’ AND OrderDate < ‘2025-01-01’ Cause → Effect Function → scan → CPU spike → slow queries.
Why Indexes Sometimes Make Queries Slower
Indexes are not magic. Bad case Low-selectivity columns Over-indexing Diagnosis SET STATISTICS IO ON; Why Wrong index = more lookups than scans.