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 

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.