Here are a few questions regarding MS SQL Server indexes that I have been asked in interviews. This is important stuff, read on!!
What is a primary key?
A unique index that identifies each row of a table. There can be only one PK on a table. SQL defaults the PK to a clustered index.
Can a primary key have a null value?
What is a unique index?
An index that enforces uniqueness, no two values can be the same. There can be multiple unique indexes on a table.
Can a unique index have a null value?
Yes it can have one null value.
What is a clustered index?
A clustered index represents the physical order of the data on disk. The table rows are sorted on disk in the same order as the keys. Therefore there can be only one clustered index on a table since the data can only be sorted in one order. Usually the primary key is the clustered index.
What is a nonclustered index?
A key value structure where the keys are sorted and the values are row locators or pointers back to the data row or clustered index.
What data types can a primary key be?
An integer type is most often recommended for primary keys: tiny int, small int, int, big int. A uniqueidenfitier is also sometimes used as a PK. A varchar(up to 900) can also be used as a primary key, this could be something like an email address. If your PK is clustered, insert performance will suffer with a non-integer PK because the data will need to be reordered for each insert.
How can adding an index to a table hurt performance?
Adding an index to a table adds overhead to transactions on the table, inserts, updates, deletes. The index needs to be updated for each transaction. So a table that experiences a lot of inserts, updates, and deletes will be likely to exhibit a performance hit.
What is a covering index?
A covering index physically stores the requested columns in the select query as part of the index. The requested data does not have to be retrieved using a row locator, it can be returned immediately from the index itself.