Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 39 ยท Indexes and Performance

Covering Indexes, INCLUDE and Sargability

Remove key lookups with covering indexes and INCLUDE columns, write SARGable predicates that can seek, and learn implicit conversion pitfalls, with runnable plan demos.

Having an index is not the same as using it. Two ideas decide whether SQL Server can use an index well: whether the index covers the query, and whether the predicate is SARGable (Search ARGument able). This lesson teaches both, with SQLite plans you can run to see the effects.

It builds on clustered and nonclustered indexes.

Covering indexes

A nonclustered index covers a query when it contains every column the query needs. SQL Server then answers from the index alone, with no key lookups into the table.

Suppose this query runs all day:

SQL
SELECT OrderDate, Total
FROM   dbo.Orders
WHERE  CustomerId = 42;

An index on (CustomerId) finds the rows, but each one needs a lookup for OrderDate and Total. Two ways to cover:

SQL
-- Option 1: put every column in the key
CREATE INDEX IX_Cover1 ON dbo.Orders (CustomerId, OrderDate, Total);

-- Option 2: key on the search column, INCLUDE the rest
CREATE INDEX IX_Cover2 ON dbo.Orders (CustomerId) INCLUDE (OrderDate, Total);

INCLUDE columns are stored at the leaf level only. They aren't sorted and don't count toward the 16-column / 900-byte key limits, which keeps the upper levels of the tree small. Use key columns for filtering and sorting; use INCLUDE for columns that are only returned.

SQLite shows the same thing in its plan. The words COVERING INDEX mean no table access:

SQL Covering index in the plan

Press Run (or Ctrl + Enter) to see the result here.

The first query reads only the index (COVERING INDEX). The second needs note, which is not in the index, so SQLite has to visit the table, just like a key lookup.

Don't include everything. A fat index costs memory and slows writes. Cover hot queries, and check the missing-index suggestions (below) before blindly adding them.

Filtered indexes

If you only ever query a small slice, index only that slice:

SQL
CREATE INDEX IX_Orders_Open ON dbo.Orders (OrderDate)
WHERE Status = N'open';

It is small, cheap to maintain and fast. The query must include a compatible WHERE for the optimizer to use it. SQLite calls this a partial index and supports the same syntax:

SQL Partial (filtered) index

Press Run (or Ctrl + Enter) to see the result here.

SARGability

A predicate is SARGable when the engine can use an index seek for it. It must compare a bare column to a value. As soon as the column is wrapped in a function or expression, the index is useless, because SQL Server would have to compute the function for every row.

Not SARGable (scan) SARGable (seek)
WHERE YEAR(OrderDate) = 2025 WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
WHERE LEFT(LastName, 1) = 'S' WHERE LastName LIKE 'S%'
WHERE Price * 1.2 > 100 WHERE Price > 100 / 1.2
WHERE LastName LIKE '%son' no index helps; consider full-text or a reversed computed column
WHERE ISNULL(Status, 'x') = 'open' WHERE Status = 'open'
WHERE CONVERT(date, CreatedAt) = '2025-05-01' WHERE CreatedAt >= '2025-05-01' AND CreatedAt < '2025-05-02'

Always use a half-open range (>= start AND < next_start) for dates; BETWEEN on datetime columns can miss or double-count the boundary.

SQL Function on the column kills the index

Press Run (or Ctrl + Enter) to see the result here.

The first plan scans; the second searches the index. In SQL Server you would see a scan versus a seek in the execution plan.

Implicit conversions

If the column and the value have different types, SQL Server converts one side by the data type precedence rules. When it converts the column, the index can't be used. The classic case: a varchar column compared with an nvarchar parameter (because applications often send Unicode strings):

SQL
-- Code is varchar(20). The parameter arrives as nvarchar.
SELECT * FROM dbo.Parts WHERE Code = @Code;   -- column converted per row: scan

Fix it by making the parameter type match the column (varchar), changing the column type, or casting the parameter, never the column. Look for a CONVERT_IMPLICIT warning in the execution plan.

Finding missing indexes

SQL Server records what indexes it wished it had:

SQL
SELECT TOP (10)
       d.statement AS TableName, d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.avg_user_impact
FROM   sys.dm_db_missing_index_details AS d
JOIN   sys.dm_db_missing_index_groups  AS g ON g.index_handle = d.index_handle
JOIN   sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle
ORDER  BY s.user_seeks * s.avg_user_impact DESC;

Treat these as hints, not orders: they ignore existing similar indexes and suggest wide indexes. Merge related suggestions and test.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the purpose of the INCLUDE clause in CREATE INDEX?

    Included columns are carried in the leaf rows only; they are not part of the key.
  2. 2. Which predicate is SARGable?

    The column stands alone on one side of a range comparison, so an index seek is possible.
  3. 3. A varchar column is compared with an nvarchar parameter. What is the likely effect?

    nvarchar has higher precedence, so the varchar column is converted. Match the parameter type to the column.
  4. 4. How should you treat the missing-index DMVs?

    The suggestions are based on single queries and often overlap with existing indexes.

Summary

  • A covering index answers a query without key lookups; use INCLUDE for returned-only columns.
  • Filtered indexes make small, targeted indexes.
  • Keep predicates SARGable: bare column, half-open date ranges, matching types.
  • Missing-index suggestions are starting points, not instructions.

Next: Execution plans and statistics