Types Of SQL Indexes – Comprehensive Guide

Last updated on 13th Jul 2020, Blog, General

There are two main index types: Clustered index and Non-Clustered index.

A clustered index alters the way that the rows are physically stored. When you create a clustered index on a column (or a number of columns), the SQL server sorts the table’s rows by that column(s).

It is like a dictionary, where all words are sorted in an alphabetical order. Note, that only one clustered index can be created per table. It alters the way the table is physically stored, it couldn’t be otherwise.

In the example below, all rows are sorted by computer_id, as a clustered index on the computer_id column has been created.

CREATE CLUSTERED INDEX [IX_CLUSTERED_COMPUTER_ID] 

ON [dbo].[nics] ([computer_id] ASC)

A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. Instead, it creates a completely different object within the table, that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data.

It is like an index in the last pages of a book. All keywords are sorted and contain a reference back to the appropriate page number. A non-clustered index on the computer_id column, in the previous example, would look like the table below:

CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID] 

ON [dbo].[nics] ([computer_id] ASC)

Computer_id	Row Locator
5000025	234
5000031	345
5000045	112
5000046	348
5000055	234
5000059	984

Note, that SQL server sorts the indexes efficiently by using a B-tree structure. This is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions, in a logarithmic amortized time. This methodology minimizes the number of pages accessed, in order to locate the desired index key, therefore resulting in an improved performance.

SQL Server Index Maintenance

As great as indexes can be when it comes to improving the performance of your database, they do require some upkeep to keep them performing optimally.  Over time as INSERTs, UPDATEs and DELETEs are executed against your table the indexes associated with each table can become fragmented.  There are many factors that determine how quickly an index will become fragmented but luckily, we have some tools built into SQL Server that will show us exactly how fragmented an index has become.  The sys.dm_db_index_physical_stats dynamic management view will give you size and fragmentation statistics on each of your indexes.  The below query can be used as a starting point to list the fragmentation of your indexes.  Note the last parameter, SAMPLED.  This parameter determines the scan level when collecting the index statistics.  I usually use SAMPLED as most of the systems I work on are quite large but there are other options that get more or less detailed statistics depending on what you require. DEFAULT, NULL, LIMITED, DETAILED are the other options.

SELECT t.name AS TableName,i.name AS IndexName,

    ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘SAMPLED’) ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY avg_fragmentation_in_percent DESC;

Once you have determined the level of fragmentation in your indexes you have two different options available in order to address the fragmentation.  You can either REORGANIZE the index or REBUILD it.  Guidelines from Microsoft suggest that if the level of fragmentation is < 10% then you should do nothing, 10% to 30% you should REORG and > 30% you should REBUILD.  These numbers were chosen based on the time it takes to do each operation.  A REBUILD creates the new index from scratch where as a REORG just works to remove the fragmentation.  An index with little fragmentation can be REORGed rather quickly so once the fragmentation is above 30% the index can usually be rebuilt in the same amount of time it would take to REORG so it makes sense to just REBUILD it.  There are some other things to consider when deciding which option to choose.  These are listed below.

REBUILD	REORG
requires enough free space to create new index	works in place so no extra space required
if interrupted need to restart from scratch	can restart from where operation was interrupted
can be done online or offline	always done online
generates more trn log than reorg	only generates trn log for blocks that were reorged
statistics are updated automatically	statistics update must be done manually

SQL queries on clustered and non-clustered Indexes

Prerequisite – Indexing in Databases

Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, SQL server do the exact same thing when you searched for a content in a book by checking the index page. In the same way table’s index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL.

1. Clustered index
2. Non-clustered index

Clustered –

A clustered index is teh type of indexing that established a physical sorting order of rows. Suppose you have a table Student_info which contains ROLL_NO as a primary key TEMPthan Clustered index which is self-created on that primary key will sort the Student_info table as per ROLL_NO. A clustered index is like Dictionary, in teh dictionary sorting order is alphabetical there is no separate index page.

Examples:

Input:

Output:

ROLL_NO	NAME	DEPARTMENT
1410110403	MD Irfan	CSE
1410110404	S Samadder	CSE
1410110405	H Agarwal	CSE

If we want to create Clustered index on other column then first we have to remove the primary key after that we can remove the previous index.

Note that defining a column as a primary key makes that column the Clustered Index of that table. To make any other column, clustered index first we have to remove the previous one as follows below procedure.

Syntax:

//Drop index

drop index table_name.index_name

//Create Clustered index index

create Clustered index IX_table_name_column_name 

             on table_name (column_name ASC) 

Note: We can create only one clustered index in a table.

Explain the types of indexes.

1) Clustered index

2) Non-clustered

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

– Non-clustered index 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.