where we use clustered and non clustered index in real time.

Posted by Shreedar under Sql Server on 9/27/2015 | Points: 10 | Views : 816 | Status : [Member] | Replies : 1
I just have a theoretical knowledge on clustered and non clustered indexes.
But I would like to know where we use these in real time scenario.

Regards

Sridhar Thota.
www.dotnet-sridhar.blogspot.com



Responses

Posted by: Rajnilari2015 on: 9/27/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
1
Down
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


For more information, please refer

http://www.dotnetfunda.com/forums/show/10097/clustered-and-non-clustered-indexes-in-sql-server

Hope this helps

--
Thanks & Regards,
RNA Team

Shreedar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response