SERIES - 3 SQL Server Indexes Interview Questions and Answers

In this article, I am going to discuss the most frequently asked SQL Server Indexes Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Temporary Tables Interview Questions and Answers. As part of this article, we are going to discuss the following SQL Server Indexes Interview Questions with answers.

1. How is data stored in a database in SQL Server?
2. How will be the database engine retrieves the information from a table in SQL Server?
3. What is the use of an Index in SQL Server?
4. What is an Index in SQL Server?
5. Why do we need Indexes in SQL Server?
6. What are the types of Indexes available in SQL Server?
7. What is a Clustered Index in SQL Server?
8. What is a Non-Clustered Index in SQL Server?
9. What is the difference between clustered and non-clustered index?
10. What is a Unique Index?
11. What are the differences between UNIQUE Constraints and the UNIQUE Index?
12. When should we be creating a unique constraint over a unique index?
13. When SQL Server uses Indexes?
14. When should we create indexes on a table?
15. What is a Covering Query in SQL Server?
16. What is a table scan? Or what is the impact of table scans on performance?
17. What is the purpose of the query optimizer in SQL Server?
18. What is the first thing you will check for if the query below is performing very slowly?
19. What is the significance of an Index on the column used in the GROUP BY clause?
20. What is the role of an Index in maintaining a unique column in a table?
21. What are the disadvantages of an Index in SQL Server?
22. How many Clustered and Non-Clustered Indexes can you have per table?
23. Which Index is faster, Clustered or Non-Clustered Index?
24. When is it usually better to create a unique non-clustered index on the primary key column?
25. 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?
26. What are the different index configurations a table can have?

Q.1 How is data stored in a database?

1. SQL Server stores data in it under data pages where a data page is a memory location for storing the information.
2. A data page will be having a size of 8KB and every 8 data pages we stored under a logical container known as “extend”.

Q.2 How will be the database engine retrieves the information from a table?

Whenever the database engine wants to retrieve the information from the table it will adopt two different mechanisms for searching the data

1. Full page scan
2. Index Scan

In the first case, SQL Server will search for the required information in each and every data page to collect the information. So, if the tables having more number of rows it will take lots of time for searching all the data so it is a time-consuming process.

In the second case SQL Server without searching into each and every data page for retrieving the information it will make use of an index for retrieving the information, where an index is a pointer to the information what we retrieve which can reduce the disk I/O operation saving the time, but if we want to use index scan for searching the data first the index has to be created.

Note: Whenever an index is created on a column or columns of a table internally an index table gets created maintaining the information of a column on which the index is created as well as the address (pointer to the row corresponding to a column).

Q.3 What is the use of an Index in SQL Server?