intermediateIndexes — Clustered, Non-Clustered, Composite, Covering

When would you choose a partial index over a regular index?

Use a partial index when: 1) Only a fraction of rows are queried (e.g., WHERE status = 'PENDING' when 90% of orders are 'COMPLETED'). 2) A specific value is queried far more than others. Benefits: smaller index (only indexes matching rows → faster scans, less memory, faster maintenance). Example: CREATE INDEX ON orders(order_date) WHERE status = 'ACTIVE'. This index is 10× smaller than a full inde

Ready to master this question?

Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.

Next Step

Continue to A query filtering on three columns (status, order_date, customer_id) is slow. How do you design the optimal index? →← Back to all SQL questions