SARGability (Search ARGument Able) is the property of a WHERE clause predicate that allows SQL Server to use an index seek. A SARGable predicate compares a column to a value directly — SQL Server can navigate the B-tree to the matching rows. A non-SARGable predicate applies a function or transformation to the column — SQL Server cannot use the index and must scan every row. Rewriting non-SARGable predicates into SARGable ones is often the single fastest query optimisation.
SARGability and Query Rewrites
-- ── SARGable vs non-SARGable predicates ──────────────────────────────────
-- ❌ Non-SARGable: function on the indexed column (forces scan)
WHERE YEAR(p.PublishedAt) = 2024
WHERE LOWER(u.Email) = 'admin@example.com'
WHERE LEN(p.Title) > 5
WHERE p.ViewCount + 100 > 500
-- ✅ SARGable rewrites (allow index seek)
WHERE p.PublishedAt >= '2024-01-01' AND p.PublishedAt < '2025-01-01'
WHERE u.NormalizedEmail = 'ADMIN@EXAMPLE.COM' -- store pre-normalised
WHERE p.ViewCount > 400 -- move constant to the right
-- LEN predicate has no simple rewrite — consider a computed column
-- ── Parameter sniffing problem ────────────────────────────────────────────
-- First execution compiles plan optimised for @CategoryId = 1 (1000 rows)
-- Subsequent executions with @CategoryId = 99 (5 rows) use the same plan
-- The plan for 1000 rows is wrong for 5 rows → poor performance
EXEC usp_GetPostsByCategory @CategoryId = 1; -- plan compiled for many rows
EXEC usp_GetPostsByCategory @CategoryId = 99; -- reuses plan — wrong for few rows
-- Fix 1: OPTION (RECOMPILE) — recompile on every execution (slight overhead)
SELECT p.Id, p.Title FROM dbo.Posts p
WHERE p.CategoryId = @CategoryId
ORDER BY p.PublishedAt DESC
OPTION (RECOMPILE);
-- Fix 2: OPTIMIZE FOR — compile for a typical parameter value
SELECT p.Id, p.Title FROM dbo.Posts p
WHERE p.CategoryId = @CategoryId
ORDER BY p.PublishedAt DESC
OPTION (OPTIMIZE FOR (@CategoryId = 3)); -- category 3 is a typical mid-size category
-- Fix 3: Local variable trick — break parameter sniffing
DECLARE @LocalCategoryId INT = @CategoryId;
SELECT p.Id, p.Title FROM dbo.Posts p
WHERE p.CategoryId = @LocalCategoryId -- optimizer doesn't know the value
ORDER BY p.PublishedAt DESC;
-- ── Implicit type conversion — hidden non-SARGable predicates ─────────────
-- Posts.Slug is VARCHAR(200); comparing to NVARCHAR forces conversion scan
WHERE p.Slug = N'my-slug' -- ❌ N prefix makes it NVARCHAR — converts Slug to NVARCHAR
WHERE p.Slug = 'my-slug' -- ✅ VARCHAR literal — no conversion needed
-- ── OR conditions — rewrite as UNION ALL ─────────────────────────────────
-- ❌ OR can prevent index usage for both conditions
SELECT Id, Title FROM dbo.Posts
WHERE Status = 'draft' OR Status = 'review';
-- ✅ UNION ALL — each branch can use the index independently
SELECT Id, Title FROM dbo.Posts WHERE Status = 'draft'
UNION ALL
SELECT Id, Title FROM dbo.Posts WHERE Status = 'review';
-- ── EXISTS vs COUNT for existence checks ──────────────────────────────────
-- ❌ COUNT(*) > 0: counts all matching rows even though we only need to know if one exists
IF (SELECT COUNT(*) FROM dbo.Comments WHERE PostId = @PostId) > 0 ...
-- ✅ EXISTS: stops as soon as one matching row is found
IF EXISTS (SELECT 1 FROM dbo.Comments WHERE PostId = @PostId) ...Note: Query Store (SQL Server 2016+) captures query plans and performance metrics over time — enabling you to detect plan regressions after schema changes. When a new index is added or statistics update, the optimizer may choose a new plan that is actually slower than the previous one (a plan regression). Query Store lets you identify the regression (
sys.query_store_plan), compare the old and new plans, and force the old plan back (sp_query_store_force_plan) while investigating the root cause. Enable it: ALTER DATABASE BlogApp SET QUERY_STORE = ON.Tip: Use
OPTION (RECOMPILE) judiciously for stored procedures with highly variable parameter distributions — like a category filter where some categories have 10,000 posts and others have 10. RECOMPILE generates a new plan for each execution based on the actual parameter values, preventing the wrong plan from being reused. The overhead is the compilation cost (typically 1-10ms) — acceptable for complex queries where the right plan is 100x faster than the wrong one.Warning: Implicit type conversions are invisible in application code but devastating in SQL Server. When EF Core sends an
NVARCHAR value for a VARCHAR column (or vice versa), SQL Server converts every row’s column value to match the parameter’s type — turning an index seek into a full scan. Always match the column’s data type in parameters and literals: use 'literal' (no N prefix) for VARCHAR columns and N'literal' for NVARCHAR columns. EF Core generally handles this correctly, but raw SQL and stored procedure parameters must be typed explicitly.Common Mistakes
Mistake 1 — Non-SARGable WHERE clause (function on indexed column forces scan)
❌ Wrong — WHERE YEAR(PublishedAt) = 2024; cannot use index on PublishedAt; full scan.
✅ Correct — WHERE PublishedAt >= '2024-01-01' AND PublishedAt < '2025-01-01'; index seek.
Mistake 2 — Ignoring parameter sniffing for procedures with skewed data distributions
❌ Wrong — stored procedure compiled for category with 5 rows; next call for category with 50,000 rows uses wrong plan.
✅ Correct — add OPTION (RECOMPILE) or use OPTIMIZE FOR UNKNOWN for highly variable parameters.