articles

Home / DeveloperSection / Articles / Indexes in SQL Server

Indexes in SQL Server

Niraj Kumar Mishra 2115 11-Aug-2017

Indexes

Indexes are used in relational databases to quickly retrieve the data .It is similar toindexes at the front 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 is8KB.

Type of Indexes
In SQL Server indexes are one of the following two types:

  1. Clustered Index
  2. Non-Clustered Index.

1.    Clustered Index

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.

2.    Non- Clustered Index

Unlike a clustered indexed, the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves.

Creating Index

create index SalaryIN on Emp(Salary) 


When you are want to take more than one fields on Index then that Index are Called Composite Index


create index IdSalaryIN on Emp(Id asc, Salary asc) 
Unique Index

When an Index is Created By using unique Keyword then that Index is Called Unique Index. If you make Unique Index on Salary Column then Salary Column automatically made Unique Key Constraints.

create Unique index SalaryIN on Emp(Salary)


Deleting indexes

Like as Droping Table you can also Delete Indexes by using Drop Keyword Like:

   drop index <index-name> on <object name>
   drop index SalaryIN on Emp

Getting list of indexes
If you want to check how many Indexes are available in your Table then you can
use a System defined Stored Procedure

sp_helpindex. By using this SP you can get All Index.

sp_helpindex < Table Name>

sp_helpindexEmp


Updated 26-Dec-2017

Leave Comment

Comments

Liked By