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

 Posted by Rajnilari2015 on 10/19/2016 | Category: Sql Server Interview questions | Views: 151 | Points: 40
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


| Alert Moderator 

Comments or Responses

Login to post response