Interview Q&A / SQL & DBMS

1. What is the difference between a clustered and a non-clustered index?

   A clustered index decides the physical order in which table rows are stored, so the table itself is the index and there can be only one. A non-clustered index is a separate structure holding the indexed values plus a pointer back to the row, and a table can have many.

   AdvancedIn-depth4 min read
2. What are the disadvantages of having too many indexes?

   Every index uses disk and memory, and must be updated on each INSERT, UPDATE and DELETE, which slows writes. Extra indexes can also confuse the optimizer and slow maintenance such as bulk loads and backups.

   BeginnerQuick answer1 min read
3. What is an index in a database and how does it speed up queries?

   An index is a separate sorted structure, usually a B-tree, that maps column values to row locations. It lets the database jump to matching rows instead of scanning the whole table, at the cost of extra storage and slower writes.

   BeginnerQuick answer1 min read
4. What is a covering index?

   A covering index contains every column a query needs, so the database answers from the index alone without visiting the table. This is called an index-only scan and avoids extra lookups.

   IntermediateQuick answer1 min read
5. What is denormalization and when would you use it?

   Denormalization deliberately adds redundant data, such as copied columns or precomputed totals, to avoid joins and speed up reads. It trades storage and write complexity for read performance, and suits read-heavy reporting.

   IntermediateQuick answer1 min read
6. Why might the database ignore an index on a column?

   Common reasons are wrapping the column in a function, a leading wildcard in LIKE, a type mismatch, a condition that matches most rows, or outdated statistics. The optimizer then prefers a full scan.

   IntermediateQuick answer1 min read