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
Type of Indexes
In SQL Server indexes are one of the following two types:
- Clustered Index
- Non-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
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.
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)
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)
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
you want to check how many Indexes are available in your Table then you can
a System defined Stored Procedure
sp_helpindex. By using this SP
you can get All Index.
sp_helpindex < Table Name>