Table of contents
1.
Introduction
2.
Table Scan
3.
Type of Indexes
3.1.
1. Clustered Index
3.2.
2. Non-Clustered Index
4.
Creating Indexes in SQL Server
5.
Simple & Composite Indexes
6.
Altering an Index
7.
Deleting indexes
8.
Frequently Asked Questions
8.1.
Why are SQL Server indexes disadvantageous?
8.2.
In SQL Server, what is the purpose of an index?
8.3.
In which columns should indexes be applied?
8.4.
In SQL Server, which index is faster?
8.5.
A SQL table can have how many indexes?
9.
Conclusion
Last Updated: Mar 27, 2024

SQL Server Indexes

Author vishal teotia
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

In relational databases, SQL indexes are used to quickly retrieve data. Like indexes at the end of books, they serve the purpose of providing quick access to information. You can create new indexes, update existing indexes, and delete existing indexes in SQL Server by using the Create Index, Alter Index, and Drop Index commands.

Table Scan

A system table called sysindexes in SQL Server contains information about indexes that are available on tables in the database. When a table has no index, there will be a row in the sysindexes table that indicates that the table has no index. If you use a condition where clause in a select statement, the first SQL Server will refer to the "indid" (index id).

The "Sysindex" table determines whether the column where the conditions are written has an index or not. In that case, we get the address of the first level of the table and look at each and every row to find the given value. Each and every row of the table is checked against the given condition in this process, also known as a table scan. The drawback of tablescan is that if the number of rows in the table remains the same, the time taken to retrieve the data will increase, affecting performance.

Type of Indexes

This example shows how to create a stored procedure that joins two tables and returns the results.

Indexes can be categorized into two types in SQL Server:

  1. Clustered Index
  2. Non-Clustered Index.

1. Clustered Index

B-Tree (computed) clustered indexes are the ones that physically arrange the rows in memory in sorted order.

Clustered indices have the advantage of being fast in search for a range of values. There is only one clustered index per table, and it is internally maintained as a B-Tree data structure consisting of leaf nodes that contain the table data.

Retrieving data with Clustered Index

Select statements which include a condition in a where clause refers to the "indid" columns of the "Sysindexes" table when this column has the value "1.

A clustered index is then applied to the table, and in this case, it pertains to the columns. There is a root node in the B-tree of a clustered index, and it searches in the B-tree for the leaf node that contains the first row that meets the given conditions, retrieving all rows that satisfy the given conditions in order.

Insert and Update with Clustered Index

  • In a clustered index, rows are physically arranged in memory in sorted order, so inserting and updating become slow because the row must be inserted or updated in sorted order.
  • Finally, the row must be inserted into the page and if there is no free space on the page, then the free space must be created and then the row can be updated, inserted, or deleted.
  • The solution to this problem is to specify a fill factor when creating a clustering index, for example, 70 will fill the table with 70% of the data and the remaining 30% will be free.
  • The insert and update will be quick since free space is available on every page.

2. Non-Clustered Index

Indexes that are non-clustered do not physically arrange the rows in memory in sorted order. It is faster to search for values that are in a range with a non-clustered index. On a table, you can create a maximum of 999 non-clustered indexes, compared to 254 in SQL Server 2005. As with a clustered index, non-clustered indexes are also stored in a B-Tree structure, but the leaf nodes of a B-Tree with non-clustered indexes contain pointers to the pages containing the table data, not the table data itself.

Retrieving data with Non-Clustered Index

If you write a select statement with a condition in the where clause, then SQL Server will refer to the "indid" column of the sysindexes table, and if that column has a value between 2 and 1000, then it indicates that the table has an unclustered index, so it refers to the root column of the sysindexes table to find the two addresses. 

To retrieve the rows with the value you are searching for from the root node of a B-Tree of a non-clustered index using the leaf node that contains the pointers to the rows containing the value you are searching for.

Insert and Update with Non-Clustered Index

  • Inserting and updating a row with a non-clustered index will have no effect since the row will not be sorted physically in memory.
  • An unclustered index inserts and updates rows at the end of the table.

Creating Indexes in SQL Server

Create an index using the create index command, which has the following system.

create index <indexname> on <object name>(<column list>)  
[include(<columnlst>)]  
[with fillfactor=<n>] 


Indexes are non-clustered by default.

Simple & Composite Indexes

An index is categorized into simple and composite indexes based on the number of columns it contains. A simple index is one created on a single column, whereas a composite index is one created on multiple columns.

Altering an Index

Using the alter index command, you can alter an index as follows:

Alter index <ind Name> on <object Name>  
rebuild/Recognize/Disable.


In the Rebuild and Recognize options, the leaf nodes in the b-tree are reorganized to index, while in the Disable option, the index is disabled until it is eligible to be enabled. Alter the index using the rebuild option.

Deleting indexes

Dropping an index is done by using the drop index command, which has the following syntax:

drop index <indexname> on <object name> 


In the following example, the department number columns of emp are deleted by deleting the index dnoidex.

drop index doindex on student 

Frequently Asked Questions

Why are SQL Server indexes disadvantageous?

Among its disadvantages are the increased disk space, the slower data modification, and the inability to update records in clustered indexes. 

In SQL Server, what is the purpose of an index?

Using indexes, you can retrieve data more quickly from the database.

In which columns should indexes be applied?

The following situations require you to create an index on a column when a lot of queries are made on this column or the column has a referential integrity constraint or columns have UNIQUE key integrity constraints.

In SQL Server, which index is faster?

Clustered indexes may be faster for one SELECT statement, but they may not always be the best choice.  

A SQL table can have how many indexes?

No matter how the indexes are created, each table can have up to 999 non-clustered indexes.  

Conclusion

In this article, we have learned everything we need to know about indexes to come up with a good index strategy and optimize the queries. You can also visit this link if you want to learn SQL.

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in MongoDBCompetitive ProgrammingDatabasesSystem Design, and many more!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!!

Live masterclass