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