Explain with example the importance of Clustered and Non-Clustered Index

Answer:

A clustered index alters the way that the rows are stored. When we create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book.

Way to create a clustered index

USE [tempdb]



GO



-- Create a new table with three columns.



CREATE TABLE [dbo].[TestTable]



    (TestCol1 int NOT NULL,



     TestCol2 nchar(10) NULL,



     TestCol3 nvarchar(50) NULL);



GO



-- Create a clustered index called IX_TestTable_TestCol1



-- on the dbo.TestTable table using the TestCol1 column.



CREATE CLUSTERED INDEX IX_TestTable_TestCol1 



    ON dbo.TestTable (TestCol1); 



GO







--Drop the table



DROP TABLE [dbo].[TestTable]



GO

A non-clustered index , on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.

Way to create a non-clustered index

USE [tempdb]



GO



-- Create a new table with three columns.



CREATE TABLE [dbo].[TestTable]



    (TestCol1 int NOT NULL,



     TestCol2 nchar(10) NULL,



     TestCol3 nvarchar(50) NULL);



GO



-- Create a nonclustered index called IX_TestTable_TestCol1 



-- on the [dbo].[TestTable] table using the TestCol1 column. 



CREATE NONCLUSTERED INDEX IX_TestTable_TestCol1 ON dbo.TestTable (TestCol1); 



GO







--Drop the table



DROP TABLE [dbo].[TestTable]



GO