SQL Server Indexes Interview Questions and Answers

Back to: Dot Net Interview Questions and Answers

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?
27. What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
28. How to know which index a table is using?

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”.

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

This is one of the frequently asked SQL Server Indexes Interview Questions. 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).

What is the use of an Index in SQL Server?

Relational databases like SQL Server use indexes to find data quickly when a query is processed. Creating the proper index can drastically increase the performance of an application.

What is an index?

1. An index is a database object which is used by the SQL server to speed up the performance of queries by providing query access to rows in the data table.
2. By using indexes, we can save time and we can improve the performance of database queries and applications.
3. When we create an index on any column SQL Server internally maintains a separate table called index table so that when any user trying to retrieve the data from the existing table depends on index table SQL Server directly go to the table and retrieve required data very quickly.
4. In a table, we can use a maximum of 250 indexes. The index type refers to the way the index is stored internally by SQL Server.

Why do we need Indexes in SQL Server?

1. Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. Index on a table or a view is very similar to an index that we find in a book.
2. If we don’t have an index in a book and ask us to locate a specific chapter in that book, we will have to look at every page starting from the first page of the book. 
3. On, the other hand, if we have the index, we look up the page number of the chapter in the index, and then directly go to that page number to locate the chapter. 
4. Obviously, the book index is helping to drastically reduce the time it takes to find the chapter.
5. In a similar way, Table and View indexes can help the query to find data quickly. 
6. In fact, the existence of the right indexes can drastically improve the performance of the query. If there is no index to help the query, then the query engine checks every row in the table from the beginning to the end. This is called a Table Scan. A table scan is bad for performance. 

What are the types of Indexes available in SQL Server?

1. Clustered Index 
2. Non-Clustered Index

What is a Clustered Index in SQL Server?

1. In the case of a clustered index, the arrangement of the data in the index table will be the same as the arrangement of the data of the actual table.
2. Example: The index we find the start of a book.
3. When a table has a clustered index then the table is called a clustered table.
4. If a table has no clustered index its data rows are stored in an unordered structure.’
5. A table can have only one clustered index in it which will be created when the primary key constraint used in a table.
6. A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.

What is a Non-Clustered Index in SQL Server?

1. In the case of a non-clustered index, the arrangement of data in the index table will be different from the arrangement of the actual table.
2. A non-clustered index is analogous to an index in a textbook. The data is stored in one place, the index is another place. The index will have pointers to the storage location of the data.
3. Since, the non-clustered index is stored separately from the actual data a table can have more than one non clustered index, just like how a book can have an index by chapters at the beginning and another index by common terms at the end.
4. In the index itself, the data is stored in an ascending or descending order of the index key which does not in any way influence the storage of data in the table.
5. In a table, we can create a maximum of 249 non clustered indexes.

What is the difference between clustered and non-clustered index in SQL Server?

This is one of the frequently asked SQL Server Indexes Interview Questions. Let us understand the differences.

1. Only one clustered index per table whereas we can have more than one non-clustered index.
2. Clustered Index is slightly faster than the 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.
3. The clustered index determines the storage order of rows in the table and hence does not require additional disk space whereas a non-clustered index is stored separately from the table, additional storage space is required.
4. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore a table can have only one clustered index.
5. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

What is a Unique Index in SQL Server?

1. If the index is created by using the “UNIQUE” option then that column on which the index is created will not allow duplicate values i.e. it works as a unique constraint.
2. The unique constraint can be either unique clustered or unique non-clustered also.
3. While creating an index if clustered or non-clustered is not specified by default it is non-clustered.
4. A unique index is used to enforce uniqueness of key values in the index.

What are the differences between UNIQUE Constraints and the UNIQUE Index?

There are no major differences between a unique constraint and a unique index. In fact, when we add a unique constraint, a unique index gets created behind the scenes.

ALTER TABLE tblEmployees ADD CONSTRAINT UQ_tblEmplyees_City UNIQUE (city)

It will create a UNIQUE non clustered index on the City column. If we want we can also create a clustered unique index on City column like below

ALTER TABLE tblEmployees ADD CONSTRAINT UQ_tblEmplyees_City UNIQUE CLUSTERED (city)

It will create a clustered index on the City column.

When should we be creating a unique constraint over a unique index?

To make our intentions clear, create a unique constraint when data integrity is the objective. This makes the objective of the index very clear. In either case, data is validated in the same manner, and the query optimizer does not differentiate between a unique index created by a unique constraint or manually created.

When SQL Server uses Indexes?

1. SQL Server uses indexes of a table provided the select or update or delete statement contained “WHERE” condition in them and moreover the where condition column must be an indexed column.
2. If the select statement contains an “ORDER BY” clause also indexes will use.

NOTE: When SQL Server is searching for information under the database first it verifies the best execution plan for retrieving the data and uses that plan which can be either a full-page scan and index scan also.

When should we create indexes on a table?

1. We need to create an index on a table column provided those columns are frequently used in where condition or order by clause. 
2. It is not advised creating an index on each and every column because a number of indexes can degrade the performance of the database also because every modification we make on the data should be reflected in all the index tables.

What is a Covering query?

1. If all the columns that we have requested in the select clause of the query are present in the index, then there is no need to look up in the table again. The requested column data can simply be returned from the index.
2. A clustered index always covers a query, since it contains all of the data in a table. A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. To a certain extent, a composite index can cover a query.

What is a table scan? Or what is the impact of table scans on performance?

This is one of the frequently asked SQL Server Indexes Interview Questions. When SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word. The SQL engine needs to visit every row in a table. In database terminology, we call this behavior a table scan or just scan. A full table scan of a very large table can degrade the performance. Creating proper indexes will allow the database to quickly narrow in on the rows to satisfy the query and avoid scanning every row in the table. 

What is the system stored procedure that can be used to list all the indexes that are created for a specific table?

sp_helpindex is the system stored procedure that can be used to list all the indexes that are created for a specific table. For example, to list all the indexes on table tblCustomers, we can use the following command.

EXEC sp_helpindex tblCustomers

What is the purpose of the query optimizer in SQL Server?

An important feature of SQL Server is the query optimizer (component). The query optimizer’s job is to find the fastest and least resource-intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task.

What is the first thing you will check for if the query below is performing very slowly?

SELECT * FROM tblProducts ORDER BY UnitPrice ASC

Check if there is an Index created on the UntiPrice column used in the ORDER BY clause. An index on the UnitPrice column can help the above query to find data very quickly. When we ask for sorted data, the database will try to find an index and avoid sorting the results during the execution of the query. We control sorting of data by specifying a field, or fields, in an ORDER BY clause, with the sort order as ASC (ascending) or DESC (descending). 

With no index, the database will scan the tblProducts table and sort the rows to process the query. However, if there is an index, it can provide the database with a presorted list of prices. The database can simply scan the index from the first entry to the last entry and retrieve the rows in sorted order. 

The same index works equally well with the following query, simply by scanning the index in reverse.

SELECT * FROM tblProducts ORDER BY UnitPrice DESC 

What is the significance of an Index on the column used in the GROUP BY clause?

Creating an Index on the column, that is used in the GROUP BY clause, can greatly improve the performance. We use a GROUP BY clause to group records and aggregate values, for example counting the number of products with the same UnitPrice. To process a query with a GROUP BY clause the database will often sort the results on the columns included in the GROUP BY. 

The following query counts the number of products at each price by grouping together records with the same UnitPrice value.

SELECT UnitPrice, Count(*) FROM tblProducts GROUP BY UnitPrice 

The database can use the index (Index on UNITPRICE column) to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able to count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.

What is the role of an Index in maintaining a unique column in a table?

1. Columns requiring unique values (such as primary key columns) must have a unique index applied. There are several methods available to create a unique index. 
2. Marking a column as a primary key will automatically create a unique index on the column.
3. We can also create a unique index by checking the Create UNIQUE checkbox when creating the index graphically. 
4. We can also create a unique index using SQL with the following command: 

CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)

The above SQL command will not allow any duplicate values in the ProductName column and an index is the best tool for the database to use to enforce this rule. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of the values in the new data duplicate existing values.

What are the disadvantages of an Index in SQL Server?

This is one of the frequently asked SQL Server Indexes Interview Questions. 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.

Disk Space: Indexes are stored on the disk and the amount of space required will depend on the size of the table and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. 

Insert, Update and Delete statements could be slow: Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. Indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.

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. The Phone Book is an example of the Clustered Index.

Non-Clustered Index: We can have multiple Non-Clustered Indexes per table. Index at the back of a book is an example of a Non-Clustered Index.

For SQL Server 2005: 1 Clustered Index + 249 Nonclustered Index = 250 Index
For SQL Server 2008: 1 Clustered Index + 999 Nonclustered Index = 1000 Index

Which Index is faster, Clustered or Non Clustered Index?

Clustered Index is slightly faster than the 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 non-clustered index on the primary key column?

Sometimes it is better to use a unique non-clustered 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 non-clustered indexes can be composite indexes.

If all of the information for a query can be retrieved from an Index then it is called as Covering query. 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.

What are the different index configurations a table can have?

A table can have one of the following index configurations:

1. No indexes
2. A clustered index
3. A clustered index and many non-clustered indexes
4. A non-clustered index
5.  Many non-clustered indexes      

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers to it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.

Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

How to know which index a table is using?

SELECT table_name,index_name FROM user_constraints

In the next article, I am going to discuss the most frequently asked SQL Server Triggers Interview Questions with Answers. Here, in this article, I try to explain the most frequently asked SQL Server Indexes Interview Questions and Answers. I hope you enjoy this SQL Server Indexes Interview Questions and Answers article. Please post your feedback, question, or comments about this SQL Server Indexes Interview Questions and Answers article.