Optimization Rules for Query Statements

Once you know how a query is indexed, the next step is deciding whether to change the query, the index, or both. A large part of that decision depends on whether the optimizer is using a clustered or a non-clustered index, since the two behave differently under the same query.

• Clustered index: speeds up processing because the table's data rows are physically ordered by the clustered index key. Every table can have at most one.
• Non-clustered index: speeds up processing through a separate structure of index keys and row pointers; the underlying data rows are not stored in that order. A table can have many non-clustered indexes.

Before rewriting a query, compare what the execution plan actually chose against how each index type behaves for that query's shape:

For SQL Server to perform an index seek, traversing the B-tree directly to the matching value, the indexed column must stand alone on one side of the comparison. Wrapping it in a function, a calculation, or a leading wildcard forces the engine to evaluate every row instead, an index scan:

-- Anti-pattern: forces an index scan
SELECT OrderID, TotalAmount
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2025
  AND LEFT(CustomerCode, 3) = 'VIP'
  AND TotalAmount * 1.06 > 1000.00;

-- Rewritten to enable an index seek
SELECT OrderID, TotalAmount
FROM Sales.Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
  AND CustomerCode LIKE 'VIP%'
  AND TotalAmount > (1000.00 / 1.06);

The rewritten version isolates each indexed column on its own, so the optimizer can seek on each predicate instead of computing a value for every row before it can compare it.

When a plan shows a non-clustered index seek paired with a Key Lookup (clustered) through a nested loops join, the query is reading columns from the base table that are not in the non-clustered index. A Key Lookup costs one random read per qualifying row, so once the optimizer estimates the query will touch more than roughly 1% to 5% of the table's pages, it typically abandons the non-clustered index entirely and falls back to a clustered index scan.

To fix it: never use SELECT *, and remove any column from SELECT, JOIN, or WHERE that the query does not need. Once every referenced column is covered by the non-clustered index, the Key Lookup disappears, and the optimizer keeps using the seek even at larger row counts.

A composite non-clustered index on (CustomerID, OrderDate, Status) sorts first by CustomerID, then by OrderDate, then by Status.

• Seekable: WHERE CustomerID = 10 or WHERE CustomerID = 10 AND OrderDate >= '2025-01-01'.
• Not seekable: WHERE OrderDate >= '2025-01-01' alone, since it skips the leading column and forces an index scan.

Once an inequality predicate (>, <, BETWEEN, LIKE 'A%') is applied to a key column, the optimizer cannot seek on any column to its right in that same index. Put equality conditions on the leading columns whenever you can, and save range conditions for the last column you filter on.

When a column is compared to a value of a different datatype, SQL Server applies data type precedence and implicitly converts the lower-precedence side. If a VARCHAR(50) column has a non-clustered index and the query compares it to an NVARCHAR parameter or literal (N'12345'), SQL Server converts the column, not the literal, because NVARCHAR has higher precedence. That conversion happens row by row, which turns what should be an index seek into a full scan, even though the index exists.

To fix it: match the parameter's, variable's, and literal's datatype and length to the target column's actual schema definition.

OR across two different columns, such as WHERE CustomerID = 100 OR SalesPersonID = 25, usually prevents a single index seek and forces either an index scan or a more expensive index union. Rewriting it as two seekable branches combined with UNION ALL lets each branch use its own non-clustered index seek:

SELECT OrderID, CustomerID, SalesPersonID
FROM Sales.Orders
WHERE CustomerID = 100

UNION ALL

SELECT OrderID, CustomerID, SalesPersonID
FROM Sales.Orders
WHERE SalesPersonID = 25
  AND (CustomerID <> 100 OR CustomerID IS NULL);

The second branch's extra condition keeps a row that matches both predicates from being returned twice.

Writes pay an index cost that reads do not. An UPDATE or DELETE whose WHERE clause cannot seek forces SQL Server to take update locks across a full scan of the table, which can cause serious blocking. SQL Server 2025's Optimized Locking feature, transaction-ID locking paired with lock-after-qualification, substantially shortens how long those row locks are held during the scan, but it is not on by default: it requires Accelerated Database Recovery and Read Committed Snapshot Isolation to be enabled, then ALTER DATABASE ... SET OPTIMIZED_LOCKING = ON for the database. Even with it enabled, a non-SARGable WHERE clause still has to scan every row to evaluate the predicate; it only reduces how long each row stays locked, not the scan itself.

It is also worth filtering out no-op writes. If an UPDATE sets a column to the value it already holds, SQL Server still logs the write and may still maintain that column's indexes. Adding a guard such as WHERE TargetColumn <> @NewValue skips rows that would not actually change.

After rewriting a query to replace a scan with a seek, or to remove a Key Lookup, confirm it with STATISTICS IO, TIME rather than trusting the rewrite on sight:

SET STATISTICS IO, TIME ON;
GO

SELECT OrderID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerID = 402
  AND OrderDate >= '2025-01-01';

GO
SET STATISTICS IO, TIME OFF;
GO

Check the Messages tab in SSMS: a drop from, say, 45,000 logical reads under a clustered index scan to a handful of reads under a covering non-clustered index seek confirms the change actually helped, rather than just looking different in the plan.

A few habits matter specifically when a large volume of data is being inserted at once:

• Inserting many rows out of clustered-key order can cause page splits as SQL Server works to keep the table physically ordered by that key; inserts that already follow the key's order largely avoid this.
• If statistics are stale, the optimizer can choose an index that no longer fits the data, so keep statistics current on tables that change often.
• For a very large one-time load, it can be worth suspending automatic statistics updates on the affected indexes for the duration of the load and updating them once at the end, rather than letting SQL Server recompute them repeatedly as the load runs.

The next lesson will review this module.