SQL Indexes and Query Performance for Beginners

An index is a separate structure that lets the database find rows without reading the whole table, like the index at the back of a book. Fresher interviews rarely go deep, but they always ask what indexes are, the clustered versus non-clustered difference, and why some queries cannot use an index.

Clustered and non-clustered indexes (SQL Server)

A table with no clustered index is called a heap. A primary key is a logical rule, and a clustered index is a physical structure: SQL Server happens to implement a primary key with a clustered index by default (unless one already exists, or you ask for NONCLUSTERED). They are not the same thing.

-- Supports lookups by customer and avoids a key lookup for the total
CREATE INDEX ix_orders_customer
    ON orders (customer_id)
    INCLUDE (total);

Seek versus scan

• An index seek walks the index tree straight to the matching rows. It is what you hope for when a query returns a few rows.
• An index scan reads the whole index (or a big part of it). A table scan reads every row.

A scan is not automatically bad. On a small table, or when the query needs most of the rows anyway, scanning can be the cheapest plan.

Covering indexes and composite indexes

A covering index contains every column a query needs, either as key columns or as INCLUDE columns, so SQL Server never has to look up the table. That is the INCLUDE (total) above.

A composite index has several key columns, and their order matters. An index on (department_id, salary) helps WHERE department_id = 1 and WHERE department_id = 1 AND salary > 60000, but it does not help a query that filters on salary alone, because the index is sorted by department_id first.

SARGable queries

SARG stands for Search ARGument. A predicate is SARGable when the engine can use an index seek for it. The usual way to break that is to wrap the column in a function or expression: the engine would have to compute the function for every row before it could compare anything.

Implicit conversions

When you compare two different data types, SQL Server converts one side by its data type precedence. If it has to convert the column, the index cannot be used to seek. A common case is comparing a column with an value, because has higher precedence. Keep column types and parameter types identical.

Indexes are not free

Every index must be maintained when data changes. More indexes mean slower , and , and more storage. Index the columns you filter, join and sort on frequently (foreign keys are good candidates), not everything. An index on a column with very few distinct values, such as a yes/no flag, rarely helps.

Reading an execution plan

An execution plan shows how the database will run (or did run) your query.

• In SQL Server Management Studio, press Ctrl+L for the estimated plan, or turn on Include Actual Execution Plan (Ctrl+M) and run the query.
• Read it from right to left. Look for table or index scans on large tables, key lookups repeated many times, and warnings such as implicit conversions or missing-index hints.
• prints how many pages the query read, which is a good way to compare two versions of a query.
• In PostgreSQL, the equivalent is .

Practise

1. Create a table with 100,000 rows (a loop or a numbers table), run a filter query and note the plan. Add an index and compare.
2. Rewrite so an index can be used.
3. Take the Indexes, Design and Transactions quiz.