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 IndexesIn SQL Server indexes are one of the following two types:
- Clustered Index
- 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.
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)
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