SQL Server Interview Questions

What are the disadvantages of an Index?

There are 2 disadvantages of an Index
1. Increased Disk Space
2. Insert, Update and Delete statements could be slow. In short, all DML statements could be slow.

What are the 2 types of Indexes in SQL Server?

1. Clustered Index
2. Non Clustered Index

How many Clustered and Non Clustered Indexes can you have per table?

Clustered Index – Only one Clustered Index per table. A clustered index contains all of the data for a table in the index, sorted by the index key. Phone Book is an example for Clustered Index.
Non Clustered Index – You can have multiple Non Clustered Indexes per table. Index at the back of a book is an example for Non Clustered Index.

Which Index is faster, Clustered or Non Clustered Index?

Clustered Index is slightly faster than Non Clustered Index. This is because, when a Non Clustered Index is used there is an extra look up from the Non Clustered Index to the table, to fetch the actual rows.

When is it usually better to create a unique nonclustered index on the primary key column?
Sometimes it is better to use a unique nonclustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field.

What is a Composite Index in SQL Server?

or
What is the advantage of using a Composite Index in SQL Server?
or
What is Covering Query?

A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes.
If all of the information for a query can be retrieved from an Index. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.

By creating a composite indexes, we can have covering queries.