TYPES OF INDEXES :-
Clustered and non clustered indexes:
- Clustered and non clustered indexes.
- If order of the values in table and order of the values in index its not same,then index is called”non-cluster index”.
- By default SQL Server creates non clustered index.
Clustered index :
- If order of the values in table and if order of the values in index is same then index is called “clustered index”.
- Here index stores records and record are arranged based on the column on which clustered index is created.
- To create clustered index then use keyword ”clustered”.
- We can create only one clustered index pin table.
- By default sql server create clustered unique index on primery key column.
- If we want to arrange records & doesn’t allow duplicate values then use clustered unique index.
– Create table xyz(xint,yint,zint)
– Create clustered index i10 on xyz(z).
– Insert into xyz values(1,2,10)
– Insert into xyz values (4,3,12).
Select * from xyz
| X | Y | Z |
| 1 | 2 | 10 |
| 4 | 3 | 12 |
How to drop index :
– Drop index xyz.i10
– Drop index xyz .i11
- User can’t drop indexes primary key column. When primary key is dropped the correspond index is also dropped.
- Query menu -> Display estimated execution plan.