Clustered Index VS Non Clustered Index in SQL

Clustered Index

1.  cluster Index determines the physical order of data in a table
2.  Primary key automatically Create a Cluster Index
3.  A table can only have 1 cluster index, but 1 cluster index can have multiple column
4.  Int and nvarchar both can have clustered index but nvarchar should not have max
5. Cluster indexes are faster than non Clustered Index because both index and data are on same table

How to create a Cluster index

     Create Clustered index [cluster-name]
     on tablename(ColumnName)
 
Non Clustered Index

1. In a Non-Clustered Index the index is stored seperately
2. Just like a book the index is stored at the first page and data is on the other page
3. A table can contain more than 1 non clustered index
4. non Cluster indexes are slower than Clustered Index because index and data are on different table
5. Becuase non clustered index are stored in a seperate table they require additional disk space

How to create a Non-Cluster index

     Create nonclustered index [cluster-name]
     on tablename(ColumnName)

How to check if a tabel has index or not

Execute sp_helpindex [table-name]


I hope it was informative for you and I would like to Thank you for reading.