Indexes

Without an index, finding one order among five million means reading all five million rows and testing each one. With the right index, the database goes almost directly to the row.

The mechanism is worth a sentence, because it explains everything else in this lesson. An index is a separate structure holding the indexed column values in sorted order, with a pointer back to the row. Sorted order is what allows the database to discard most of the data without reading it — the same reason a sorted list can be searched by halving it repeatedly rather than scanned from the start.

-- Almost every order query filters or joins on customer_id
CREATE NONCLUSTERED INDEX ix_orders_customer_date
    ON orders (customer_id, order_date DESC);

-- Joining order_items to its parent order happens constantly
CREATE NONCLUSTERED INDEX ix_order_items_order
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Filtering products by category, with the columns a listing needs
CREATE NONCLUSTERED INDEX ix_products_category
    ON products (category)
    INCLUDE (product_name, unit_price, units_in_stock)
    WHERE discontinued = 0;

• The first index supports "this customer's orders, most recent first". Because order_date is in the index and already descending, the sort is free.
• INCLUDE adds columns to the index leaf without making them part of the sort key. The second index can answer a whole order-lines query from the index, with no lookup back into the table.
• The third has a WHERE clause: a filtered index. It only contains rows where discontinued = 0, so it is smaller and cheaper to maintain. It can only be used by queries that also filter on discontinued = 0. PostgreSQL calls this a partial index.

An index on (customer_id, order_date) is sorted by customer, and within each customer, by date. That ordering is the whole story.

A phone book sorted by surname then first name lets you find everyone called Sharma, and then Ravi Sharma within them. It does nothing for finding everyone called Ravi. An index behaves the same way: it can be used for a leading prefix of its columns, not for a column in the middle.

-- Uses the index well: filters on the leading column
WHERE customer_id = 417;

-- Uses the index well: leading column, then the second
WHERE customer_id = 417 AND order_date >= '2026-01-01';

-- Uses the index for the sort too, because the order already matches
WHERE customer_id = 417 ORDER BY order_date DESC;

-- Cannot seek: order_date is not the leading column
WHERE order_date >= '2026-01-01';

-- Range on the leading column, so the second column's order is no longer useful
WHERE customer_id BETWEEN 400 AND 500 AND order_date = '2026-03-01';

• The first three are the intended uses. The database can jump straight to the relevant section of the index.
• The fourth needs an index led by order_date. The existing one is sorted by customer first, so dates for a given value are scattered throughout it.
• The fifth is the subtle one. Once the first column is a range rather than a single value, rows matching the second column are spread across many sections of the index. The database can still use the index for the customer range, then filter, but it cannot seek directly to the date.
• The general rule for ordering columns: equality filters first, then the range or sort column. Most selective first is a common suggestion and a less reliable one.

An index can only be searched on the values it stores. Transform the column in your condition and the stored values no longer match what you are asking for, so the database has to compute the transformation for every row — which means reading every row.

The jargon for a condition that can use an index is "sargable", from Search ARGument ABLE. The term matters less than recognising the shapes.

-- 1. Function on the column
--    Cannot seek: the index stores dates, not years
WHERE YEAR(o.order_date) = 2026
--    Rewrite as a range on the raw column
WHERE o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01'

-- 2. Leading wildcard
--    Cannot seek: the index is sorted by first character
WHERE p.product_name LIKE '%cable%'
--    A trailing wildcard can seek
WHERE p.product_name LIKE 'Cable%'

-- 3. Arithmetic on the column
WHERE oi.unit_price * oi.quantity > 500
--    Move the maths to the other side, or store the line total as a computed column
WHERE oi.unit_price > 500 / oi.quantity

-- 4. Implicit conversion
--    customer_ref is NVARCHAR; comparing to a number forces a conversion per row
WHERE c.customer_ref = 4172
--    Compare like with like
WHERE c.customer_ref = '4172'

• Case 1 is the most common by a wide margin. YEAR(), CAST(), CONVERT() and UPPER() around an indexed column all have the same effect.
• Case 2 has no rewrite, because "contains" genuinely cannot be answered from a sorted-by-prefix structure. Full-text search exists for this, and on a small table a scan is perfectly acceptable.
• Case 4 is easy to miss because nothing looks wrong. Implicit conversion of the column side is silent and scans. It usually shows in an execution plan as a CONVERT_IMPLICIT warning.
• A note on case 3: the rewrite shown changes behaviour if quantity can be zero. A computed persisted column with its own index is the safer answer when this filter matters.

So the answer to "should I index this column?" is never automatically yes. A table written to heavily and read rarely — an audit log, an event stream — may be better with one or two indexes than eight.

The shape to avoid is a table with twelve overlapping indexes added one at a time by different people fixing different reports. Each was justified in isolation; together they slow every write and several are redundant.