Indexes in SQL Server

This article describes SQL indexes. A SQL Index is used on a table. Learn how to use indexes in SQL Server.

SQL CREATE INDEX, ALTER INDEX, and DROP INDEX commands are used to create, alter, and delete indexes in SQL Server. SQL Indexes are used in relational databases to quickly retrieve the data. They are similar to indexes at the end of the books whose purpose is to find a topic quickly. 
  • Data is internally stored in a SQL Server database in “pages” where the size of each page is 8KB.
  • A continuous 8 pages is called an “Extent”.
  • When we create the table then one extent will be allocated for two tables and when that extent is computed it is filled with the data then another extent will be allocated and this extent may or may not be continuous to the first extent.

Table Scan


In SQL Server there is a system table with the name sysindexes that content information about indexes are available on tables into the database. A table even has no index, there will be one row in the sysindexes table related to that table indicating there is no index on the table.
  • When you write a select statement with a condition where clause than the first SQL Server will refer to the “indid” (index id).

Columns of the “Sysindex” table determine whether or not the column on which you write the conditions has an index. When that indid columns, to get an address of the first extent of the table and then searches each and every row of the table for the given value.

  • This process checks the given condition with each and every row of the table called a table scan.
  • A drawback for tablescan is that if there is no increase in rows in the table then the time taken to retrieve the data will increase and that effects performance.

Type of Indexes


In SQL Server indexes are one of the following two types:
  1. Clustered Index
  2. Non-Clusterd Index.

1. Clustered Index

A B-Tree (computed) clustered index is the index that will arrange the rows physically in the memory in sorted order.

An advantage of a clustered index is that searching for a range of values will be fast. A clustered index is internally maintained using a B-Tree data structure leaf node of btree of clustered index will contain the table data; you can create only one clustered index for a table.

Retrieving data with clustered Index

When you write a select statement with a condition in a where clause then the first SQL Server will refer to the “indid” columns of the “Sysindexes” table and when this column contains the value “1”.

Then it indexes the table with a clustered index and in this case it refers to the columns .”The Root” node of the B-tree of clustered index and searches in the b-tree to find the leaf node that contains the first row that satisfies the given conditions and retrieves all the rows that satisfy the given condition that will be in sequence.

Insert and Update with Clustered Index

  • Since a clustered index arranges the rows physically in the memory in sorted order, insert and update will become slow because the row must be inserted or updated in sorted order.
  • Finally, the page into which the row must be inserted or updated and if free space is not available in the page then creating the free space and then performing the insert, update and delete.
  • To overcome this problem while creating a clustering index specify a fill factor and when you specify a fill factor as 70 then in every page of that table 70% will fill it with data and remaining 30% will be left free.
  • Since free space is available on every page, the insert and update will fast.

2. Non-clustered Index

  • A non-clustered index is an index that will not arrange the rows physically in the memory in sorted order.
  • An advantage of a non-clustered index is searching for the values that are in a range will be fast.
  • You can create a maximum of 999 non-clustered indexes on a table, which is 254 up to SQL Server 2005.
  • A non-clustered index is also maintained in a B-Tree data structure but leaf nodes of a B-Tree of non-clustered index contains the pointers to the pages that contain the table data and not the table data directly.

Retrieving data with non-clustered index

  • When you write a select statement with a condition in a where clause then SQL Server will refer to “indid” columns of sysindexes table and when this columns contains the value in the range of 2 to 1000 then it indicates that the table has a non –clustered index and in this case it will refer to the columns root of sysindexes table to get two addresses.

Of the root node of a B-Tree of a non-clustered index and then search in the B-Tree to find the leaf node that contains the pointers to the rows that contains the value you are searching for and retrieve those rows.

Insert and Update with a Non-clustered Index

  • There will be no effect of insert and update with a non-clustered index because it will not arrange the row physically in the memory in sorted order.
  • With a non-clustered index, rows are inserted and updated at the end of the table.
Clustered Index Non- Clustered Index
This will arrange the rows physically in the memory in sorted order This will not arrange the rows physically in the memory in sorted order.
This will fast in searching for the range of values. This will be fast in searching for the values that are not in the range.
Index for table. You can create a maximum of 999 non clustered indexes for table.
Leaf node of 3 tier of clustered index contains, contains table data. Leaf nodes of b-tree of non-clustered index contains pointers to get the contains pointers to get that contains two table data, and not the table data directly.

Creating Indexes in SQL Server


To create an index, use the create index command that has the following system.

create [unique][clustered /non clusted] index :
  1. <indexname> on <object name>(<column list>)  
  2. [include(<columnlst>)]  
  3. [with fillfactor=<n>] 

By default an index is non-clustered.

For example, the following examples create a non-clustered index on department_no of emp tables.

  1. create index DNoINdex on Emp(DeptNo) 

Simple & Composite Indexes

  • Based on the number of columns on which an index is created, indexes are classified into simple and composite indexes.
  • When indexes are created on single columns then it is called a simple index and when the index is created in combination with multiple columns then it's called a composite index.

For example, the following example creates a non-clustered index in combination with department number and job columns of the emp table.

  1. create index dnotedxi on emp(deptno asc,job desc

Unique Index

  • When an index is created using the keyword unique then it is called a unique index, whom you create unique index on columns then along with an index unique constraint will be created on the columns.
  • If the columns on which you are creating a unique index contains duplicate values then a unique index will not be created and you will get an error.

Altering an Index


To alter an index use an alter index command that has the following syntax:
  1. Alter index <ind Nameon <object Name>  
  2. rebuild/Recognize/Disable. 

The Rebuild option will recreate the computer index, the recognize option will reorganize leaf nodes of the b-tree to index and the disable option will disable the index when the index is eligible then to enable it. alter the index using the rebuild option.

For example, the following example alters the index “dnoidx” available on the department number of columns on the emp table.

  1. alter index DNOiDX on EMp rebuild 

Getting list of indexes


To get compute a list of indexes available on a table this stored procedure is used.

sp_helpindex 'Stud'

Deleting indexes


Use the drop index command that has the following syntax:
  1. drop index <indexname> on <object name

For example, the following example deletes the index dnoidex available on the department number columns of the emp table.

  1. drop index doindex on student