Fix SQL Server stored procedures, views, and T-SQL anti-patterns. Covers non-SARGable predicates, implicit conversions, cursor replacement, DELETE+INSERT anti-pattern, CTE vs temp table decisions, and query hints.
SQL Server Query Optimization
Use this skill when you have a specific stored procedure, view, or T-SQL batch that needs to be rewritten or improved. This is about fixing the code — for hardware/index problems, start with sqlserver-diagnostics and sqlserver-indexes.
Stored Procedure Anti-Patterns
1. DELETE + INSERT Per Request (Most Common)
Problem: A stored procedure deletes and re-inserts the same row every time it runs, even when nothing changed.
-- ❌ ANTI-PATTERNCREATEPROCEDURE usp_UpsertUserPreference
@UserIDINT, @Key NVARCHAR(50), @Value NVARCHAR(500)
ASDELETEFROM UserPreferences WHERE UserID =@UserIDAND PreferenceKey =@Key;
INSERT INTO UserPreferences (UserID, PreferenceKey, PreferenceValue)
VALUES (@UserID, @Key, );
GO
@Value
Problems:
Two DML statements = two log writes, two index operations
Triggers fire on DELETE even when nothing really changed
Breaks foreign keys that point to this row
Causes unnecessary index fragmentation
Fix — MERGE:
-- ✅ CORRECT — MERGE (upsert)CREATEPROCEDURE usp_UpsertUserPreference
@UserIDINT, @Key NVARCHAR(50), @Value NVARCHAR(500)
ASMERGE UserPreferences AS target
USING (VALUES (@UserID, @Key, @Value)) AS source (UserID, PreferenceKey, PreferenceValue)
ON target.UserID = source.UserID AND target.PreferenceKey = source.PreferenceKey
WHEN MATCHED AND target.PreferenceValue != source.PreferenceValue THENUPDATESET target.PreferenceValue = source.PreferenceValue,
target.UpdatedAt = GETDATE()
WHENNOT MATCHED THENINSERT (UserID, PreferenceKey, PreferenceValue, CreatedAt)
VALUES (source.UserID, source.PreferenceKey, source.PreferenceValue, GETDATE());
GO
Alternative — staleness check:
-- ✅ CORRECT — Only update if value actually changed
IF EXISTS (SELECT1FROM UserPreferences WHERE UserID =@UserIDAND PreferenceKey =@Key)
BEGINUPDATE UserPreferences
SET PreferenceValue =@Value, UpdatedAt = GETDATE()
WHERE UserID =@UserIDAND PreferenceKey =@KeyAND PreferenceValue !=@Value; -- Skip if no changeENDELSEBEGININSERT INTO UserPreferences (UserID, PreferenceKey, PreferenceValue, CreatedAt)
VALUES (@UserID, @Key, @Value, GETDATE());
END
2. Cursor Loops (Replace with Set-Based Operations)
-- ❌ ANTI-PATTERN — processing rows one at a timeDECLARE@OrderIDINT;
DECLARE cur CURSORFORSELECT OrderID FROM Orders WHERE Status ='Pending';
OPEN cur;
FETCH NEXT FROM cur INTO@OrderID;
WHILE @@FETCH_STATUS=0BEGINEXEC usp_ProcessOrder @OrderID;
FETCH NEXT FROM cur INTO@OrderID;
ENDCLOSE cur; DEALLOCATE cur;
Fix — set-based:
-- ✅ CORRECT — process all rows at onceUPDATE Orders
SET ProcessedAt = GETDATE(), Status ='Processing'WHERE Status ='Pending';
If usp_ProcessOrder has complex logic that can't be inlined, consider:
Rewriting the proc to accept a table-valued parameter (TVP)
Using a temp table as a queue and processing via a single bulk statement
Using STRING_AGG / FOR XML PATH for string concatenation (not cursors)
3. Non-SARGable Predicates
A SARGable predicate (Search ARGument able) allows SQL Server to use an index seek. Non-SARGable predicates force scans.
-- ❌ NON-SARGable — function wraps the column, index can't be usedWHEREYEAR(OrderDate) =2024WHERECONVERT(VARCHAR, OrderDate, 101) ='01/15/2024'WHERE LEN(CustomerName) >10WHERE OrderTotal +100>5000-- ✅ SARGable equivalentsWHERE OrderDate >='2024-01-01'AND OrderDate <'2025-01-01'WHERE OrderDate ='2024-01-15'WHERE CustomerName LIKE'A%'-- LIKE with suffix wildcard is SARGableWHERE OrderTotal >4900-- ❌ NON-SARGable — leading wildcard, can't use indexWHERE CustomerName LIKE'%Smith%'-- ✅ If full-text search needed, use SQL Server Full-Text or a search table
4. Implicit Conversions
When a column and a parameter have different data types, SQL Server converts the entire column — forcing an index scan.
-- Table column: CustomerCode NVARCHAR(20)-- ❌ Passing VARCHAR causes implicit conversion of the whole columnWHERE CustomerCode ='ACME123'-- VARCHAR literal, column is NVARCHAR-- ✅ Use N'' prefix for NVARCHAR columnsWHERE CustomerCode = N'ACME123'-- Table column: Status TINYINT-- ❌ Passing string causes type conversionWHERE Status ='1'-- ✅ Pass the correct typeWHERE Status =1
Detect implicit conversions in execution plans:
Look for a CONVERT_IMPLICIT function in the predicate tooltip
Or query: sys.dm_exec_query_stats + sys.dm_exec_sql_text and filter on CONVERT_IMPLICIT
5. SELECT * in Production Code
-- ❌ Never use SELECT * in stored procedures or viewsSELECT*FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
-- ✅ Always name columns explicitlySELECT
o.OrderID, o.OrderDate, o.TotalAmount, o.Status,
c.CompanyName, c.ContactEmail
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;
Why it matters:
Breaks if a column is added, renamed, or reordered in the base table
Fetches columns the application doesn't need (extra I/O, network)
Prevents SQL Server from using covering index optimization
View Optimization
When Views Expand Inline
By default, SQL Server expands a view definition inline during query compilation. The optimizer sees the full base table query — this is usually fine.
-- This view is expanded inline — optimizer sees the base tablesCREATEVIEW vw_ActiveOrders ASSELECT o.*, c.CompanyName
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.Status ='Active';
Indexed Views (Materialized)
An indexed view stores the result set physically. Dramatically speeds up aggregation queries.
-- Requirements for indexed views:-- 1. SCHEMABINDING-- 2. UNIQUE CLUSTERED INDEX on the view-- 3. Strict SET options (ANSI_NULLS ON, QUOTED_IDENTIFIER ON, etc.)CREATEVIEW vw_SalesByRegion
WITH SCHEMABINDING ASSELECT RegionID, COUNT_BIG(*) AS OrderCount, SUM(TotalAmount) AS TotalSales
FROM dbo.Orders
GROUPBY RegionID;
GO
CREATEUNIQUE CLUSTERED INDEX IX_vw_SalesByRegion ON vw_SalesByRegion (RegionID);
On Enterprise Edition, SQL Server automatically uses the indexed view even when the query references the base table. On Standard Edition, use WITH (NOEXPAND) hint:
SELECT*FROM vw_SalesByRegion WITH (NOEXPAND) WHERE RegionID =5;
CTE vs Temp Table vs Table Variable
Scenario
Use
Why
Readability / recursive queries / used once
CTE
Inline, no physical storage, optimizer sees through it
Large result set used multiple times in same batch
Temp Table
Has statistics, can be indexed, persists for the session
Small result set (< few hundred rows)
Table Variable
Low overhead; no statistics (can be a problem if used in joins)
Need an index on intermediate results
Temp Table
Table variables can only have a PK constraint
Need statistics for optimizer accuracy
Temp Table
Table variables have no statistics — optimizer assumes 1 row
-- CTE (single-use, readability)WITH RankedOrders AS (
SELECT*, ROW_NUMBER() OVER (PARTITIONBY CustomerID ORDERBY OrderDate DESC) AS rn
FROM Orders
)
SELECT*FROM RankedOrders WHERE rn =1;
-- Temp Table (multiple uses, large set)SELECT OrderID, CustomerID, TotalAmount
INTO #LargeOrderSet
FROM Orders
WHERE TotalAmount >10000;
CREATE INDEX IX_Temp_CustomerID ON #LargeOrderSet (CustomerID); -- Can add index!SELECT o.*, c.CompanyName FROM #LargeOrderSet o
JOIN Customers c ON o.CustomerID = c.CustomerID;
DROPTABLE #LargeOrderSet;
-- Table Variable (small set)DECLARE@SmallSetTABLE (OrderID INT, CustomerID INT);
INSERT INTO@SmallSetSELECT TOP 10 OrderID, CustomerID FROM Orders;
Statistics Management
-- Update statistics for a specific table (full scan = accurate, slower)UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- Update all statistics in the current databaseEXEC sp_updatestats;
-- Check if auto-update statistics is enabled (should be ON)SELECT name, is_auto_update_stats_on, is_auto_create_stats_on
FROM sys.databases
WHERE name = DB_NAME();
-- Check statistics for a specific table
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerID');
When to run manually:
After a large bulk load (auto-update threshold is 20% of rows modified — can lag on large tables)
After index creation (statistics are created automatically but may not be full scan)
When execution plans look wrong (row estimates far off from actuals)
Query Hints — When Justified
Query hints override optimizer decisions. Use sparingly — they lock in a behavior regardless of data changes.
-- NOLOCK — dirty reads (accepts uncommitted data). Use for reporting only; NEVER for financial/transactional dataSELECT*FROM Orders WITH (NOLOCK) WHERE CustomerID =42;
-- OPTION (RECOMPILE) — recompile this statement every execution; solves parameter sniffingSELECT*FROM Orders WHERE CustomerID =@CustomerID OPTION (RECOMPILE);
-- OPTION (OPTIMIZE FOR UNKNOWN) — use average statistics, not sniffed parameter valueSELECT*FROM Orders WHERE CustomerID =@CustomerID OPTION (OPTIMIZE FOR (@CustomerIDUNKNOWN));
-- MAXDOP — limit parallelism for this querySELECTSUM(TotalAmount) FROM Orders OPTION (MAXDOP 2);
-- FORCE ORDER — force join order as written (rarely needed; usually means missing statistics)SELECT*FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID OPTION (FORCE ORDER);
