New MS SQL Server Indexes Interview Preparation Guide

Download PDF

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

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

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

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.

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

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.

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

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.

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.

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

Usually the data keeps getting added, deleted, or updated in the table due to which the fill factor is implemented during the index creation itself. Fill factor is not maintained after an index is created.

The changes in the future can be determined only when a new index is created on a table that has an existing data. It is beneficial to set the fill factor then itself.

Maintaining extra space on the data pages obviates the purpose of using the fill factor. The SQL Server would then have to split pages to maintain an amount of free space per the fill factor, on each page.

Thus, when data is added filling up the empty space, an index can be created and the fill factor can be re-specified distribute the data again.

The fill factor option is provided for smoothening index data storage and performance.

The percentage of space on each leaf level page to be filled with data is determined by the fill factor value When an index is created. This reserves a percentage of free space for future growth

Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data. We can have only one clustered index in a table but we can have many non-clustered index in a table. Physical data in the table is sorted in the order of clustered index while not with the case of non-clustered data.

A clustered index is a good choice for searching over a range of values. After an indexed row is found, the remaining rows being adjacent to it can be found easily. However, using wide keys with clustered indexes is not wise because these keys are also used by the non-clustered indexes for look ups and are also stored in every non-clustered index leaf entry

Full text indexes are stored in the file system and are administered through the database.

Only one full-text index is allowed for one table. They are grouped within the same database in full-text catalogs and are created, managed and dropped using wizards or stored procedures

A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future.

A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have

Having Clause: This clause is used for specifying a search condition for a group or an aggregate. It can only be used with a SELECT statement. It’s often used with GROUP BY clause without which its synonymous to a WHERE clause.

E.g.: SELECT Id, Name, Age FROM Customers
GROUP BY Age
HAVING Age>10
ORDER BYAge;

Where Clause: This clause is used to narrow down the dataset being dealt with following a condition.

SELECT Id, Name, Age FROM Customers
WHERE Age>10

It is strongly recommended to use a Where clause with every Select Statement to avoid a table scan and reduce the number of rows to be returned. It can be used with Select, Update, Delete etc statements.