Indexes
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 continous 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 indicationg 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 thawt 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:
- Clustered Index
- 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 clusterd 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 clusterd index will contain the table data; you can create only one clustered index for a table.
Retriving 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 referes 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 retrives all the rows that satisfy the given condition that will be in sequence.
Insert and Update with Clusterd 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 freespace is not available in the page then creating the freespace 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 the 30% will be left free.
- Since free space is available on every page, the insert and update will fast.
2. Non-clusterd Index
- A non-clusterd index is an index that will not arrange the rows physically in the memory in sorted order.
- An advantage of a non-clusterd 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-clusterd index is also maintained in a B-Tree data strurcture but leaf nodes of a B-Tree of non-clusterd index contains the pointers to the pages that contain the table data and not the table data directly.
Retriving data with non-clusterd 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 indicate 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 retrive 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 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
To create an index, use the create index command that has the following system.
create [unique][clustered /non clusted] index :
- <indexname> on <object name>(<column list>)
- [include(<columnlst>)]
- [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.
- 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.
- 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 a 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 then a unique index will not be created and you will get an error.
Altering and Index
To alter an index use an alter index command that has the following syntax:
- Alter index <ind Name> on <object Name>
- 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.
- alter index DNOiDX on EMp rebuild
Getting list of indexes
To get compute a list of indexes available on a table this stored preocedure is used.
sp_helpindex 'Stud'
Deleting indexes
Use the drop index command that has the following syntax:
- 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.
- drop index doindex on student