Top MS SQL Server Indexes Interview Questions & Answers:

1. Can you explain important index characteristics?

The characteristics of the indexes are:

They fasten the searching of a row.
They are sorted by the Key values.
They are small and contain only a small number of columns of the table.
They refer for the appropriate block of the table with a key value

2. Explain the types of indexes?

Types of indexes:

Clustered: It sorts and stores the data row of the table or view in order based on the index key.
Non clustered: it can be defined on a table or view with clustered index or on a heap. Each row contains the key and row locator.
Unique: ensures that the index key is unique
Spatial: These indexes are usually used for spatial objects of geometry
Filtered: It is an optimized non clustered index used for covering queries of well defined data

3. Write down the syntax and an example for create, rename and delete index?

Create Index:

CREATE INDEX index_name ON table_name (col_1,col_2..);

Example:

Create index index_sample ON employee(emp_id)

Rename Index:

ALTER INDEX index_name RENAME to new_index_name ;

Example:

ALTER INDEX index_sample RENAME to new_sample

Delete index:

drop index [schema.]index [force];

Example:

Drop index new_sample

4. determine when an index is appropriate?

a. When there is large amount of data. For faster search mechanism indexes are appropriate.
b. To improve performance they must be created on fields used in table joins.
c. They should be used when the queries are expected to retrieve small data sets
d. When the columns are expected to a nature of different values and not repeated
e. They may improve search performance but may slow updates.

5. Explain the purpose of indexes?

Allow the server to retrieve requested data, in as few I/O operations
Improve performance
To find records quickly in the database

6. What is Unique Index?

Unique index is the index that is applied to any column of unique value.
A unique index can also be applied to a group of columns.

7. Tell me the difference between clustered and non-clustered index?

A clustered index reorders the way records are stored. A non clustered index is in which the logical order of the index does not match the physical stored order of the rows on disk. A clustered index is must faster because the index entries are actually data records. There can be just one clustered index per table while there can be up to 249 non clustered indexes

8. Do you know Clustered and Non-Clustered Index?

Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.

Non-clustered is the index in which logical order doesn't match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.

9. Explain Indexes disadvantages?

Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.

10. MS SQL Server index?

Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key.
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater

Syntax:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name