SQL Server indexes

SQL Server index

What is an 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.

Disadvantages of the Indexes

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.

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

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.

Difference between clustered and non-clustered index.

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.

SQL Server indexes 

Explain the 'Fillfactor' concept in Indexes.

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. 

How do you maintain a fill factor in existing indexes?

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.

What is it unwise to create wide clustered index keys?

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.

What is full-text indexing?

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.

What is fill factor and pad index?

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 haveDescribe 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

SQL Server indexes -

What is an index?

Answer
Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.

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

What are the types of indexes?

Answer
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

Describe the purpose of indexes.

Answer

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

Explain the difference between clustered and non-clustered index.

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

SQL Server indexes 

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.

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