CREATE INDEX, DROP INDEX In SQL SERVER

Introduction

A SQL Server Index is used on a database table for faster data access. In this article, learn what SQL Server Indexes are, why we need them, and how to implement them in SQL Server.

Indexes in SQL Server

SQL Indexes are used in relational databases to retrieve data quickly. They are similar to indexes at the end of the books whose purpose is quickly finding a topic. SQL provides Create Index, Alter Index, and Drop Index commands used to create a new index, update an existing one, and delete an index in SQL Server.

Data is internally stored in a SQL Server database in "pages" where the size of each page is 8KB.
A continuous eight pages are called an "Ex. "nt."
When we create the table, one extent will be allocated for two tables; when it is computed, it is filled with the data. Then another time will be given, and this extent may or may not be continuous to the first extent.

Table Scan

In SQL Server, a system table with the name sysindexes contains information about indexes available on tables in the database. If a table has no index, there will be one row in the sysindexes table related to that table indicating no index on the table when you write a select statement with a condition where clause, 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 table's first extent and then searches each row of the table for the given value. This process checks the given condition with every table row, called a table scan. A drawback of table scan is that if there is no increase in rows in the table, the time taken to retrieve the data will increase, affecting performance.

Type of Indexes

SQL Server supports two types of indexes:

Clustered Index
Non-Clusterd Index.

Clustered Index in SQL Server

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 the btree of the clustered Index will contain the table data; you can create only one clustered Index for a table.

How do we Retrieve the 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. In this case, it refers to the columns ." The Root" node of the B-tree of clustered index 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 situation 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 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 on the page, create the free space and then perform 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 with data, and the remaining 30% will be left free.
Since free space is available on every page, the insert and update will be fast.

Nonclustered Index in SQL Server

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

How do we Retrieve data with a nonclustered index?

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

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

Insert and Update with a Nonclustered Index

There will be no effect of inserting and updating with a nonclustered index because it will not arrange the row physically in the memory in sorted order.
With a nonclustered index, rows are inserted and updated at the end of the table.

Clustered Index	Nonclustered 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 the table.	You can create a maximum of 999 nonclustered indexes for the table.
The leaf node of 3 tiers of the clustered Index contains table data.	The leaf nodes of the b-tree of the nonclustered Index contain pointers to get the included pointers with two table data and not the table data directly.

How to Create Indexes in SQL Server?

Use the create index command with the following system to create an index.

create [unique][clustered /non clusted] index :

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

By default, an index is nonclustered.

For example, the following examples create a nonclustered 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, it is called a simple index; when combined with multiple columns, it's called a composite index.

For example, the following example creates a nonclustered index in combination with the emp table's department number and job columns.

create index dnotedxi on emp(deptno asc,job desc)

Unique Index

When an index is created using the keyword unique, it is called a unique index; you create a unique index on columns, and a unique index constraint will be created.
If the columns on which you create a unique index contain 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:

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

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

For example, the following example alters the index "d" oidx" "available on the department number of columns on the emp table.

alter index DNOiDX on EMp rebuild

Getting a list of indexes

This stored procedure is used to compute a list of indexes available on a table.

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

Conclusion

This article taught us about Indesxes and its different types of indexes with various operations done by code examples in SQL Server.