Frequently asked question on a job interview. So, let’s figure it out.
Indexes are the keys that stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
- With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index. It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
- With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.