SQL Indexes & Performance Interview Questions

An index is a database object that speeds up data retrieval by creating a separate data structure that points to rows in a table, similar to an index at the back of a book. Without an index, SQL Server has to scan every row in a table to find matches, which gets very slow on large tables. Indexes store a sorted copy of one or more columns along with pointers to the actual rows, so the engine can jump directly to relevant data. The trade-off is that indexes consume disk space and slow down INSERT, UPDATE, and DELETE operations because the index must be maintained alongside the table.

CREATE INDEX idx_employee_lastname ON employees (last_name);

-- With multiple columns (composite index)
CREATE INDEX idx_emp_dept ON employees (department_id, last_name);

A clustered index determines the physical sort order of the data in the table itself, so a table can only have one clustered index. The leaf nodes of a clustered index are the actual data pages. A non-clustered index is a separate structure that stores a sorted copy of the indexed columns along with a pointer back to the actual row, and a table can have up to 999 non-clustered indexes in SQL Server. Lookups using a non-clustered index may require an extra step called a key lookup to fetch columns not included in the index, while a clustered index read goes straight to the data. Primary keys are clustered by default in SQL Server, but you can change that.

It depends on which columns you provide. Composite indexes follow the leftmost prefix rule, meaning the index is used only if your WHERE clause includes the leading column(s) of the index. If your index is on (col_a, col_b, col_c) and you filter on col_a and col_b, the index will be used effectively. But if you skip col_a and only filter on col_b and col_c, the optimizer generally cannot use that index and will fall back to a full table scan. Always make sure your most selective and most commonly queried column is at the front of a composite index.

An index speeds up data retrieval by allowing the database engine to find rows without scanning the entire table. Think of it like a book index: instead of reading every page, you jump directly to the relevant location. Without indexes, a query on a large table does a full table scan, which gets very slow as data grows. Indexes are especially valuable on columns used frequently in WHERE clauses, JOIN conditions, and ORDER BY operations.

-- Without index: full table scan on 10M rows
SELECT * FROM orders WHERE customer_id = 5000;

-- With index: near-instant lookup
CREATE INDEX idx_orders_customer ON orders(customer_id);

Fill factor is a SQL Server setting that controls how much free space is left on each index page when the index is created or rebuilt. A fill factor of 80 means pages are filled to 80% capacity, leaving 20% free for future inserts and updates. This free space helps delay page splits, which happen when a new row needs to go into a page that is already full and SQL Server has to split that page into two, which causes fragmentation and hurts performance. The right fill factor depends on your workload: high for read-heavy tables, lower for frequently updated ones.