February 10, 2015 at 5:52 am
hello friends can u any one help my doubt,i'm a learner of sql.i have some confusion in this and doubt is
what is the difference between clustered and non clustered index, unique index and non unique index with examples?
pls help me........clear my doubt
February 10, 2015 at 6:13 am
You can start from the MSDN page for clustered and nonclustered indexes: https://msdn.microsoft.com/en-us/library/ms190457.aspx
Google returns 97.000 results for "sql server difference between clustered and nonclustered index". Does any of those answer your question?
-- Gianluca Sartori
February 10, 2015 at 6:41 am
what is the difference between clustered and non clustered index
This is a big topic and there's plenty of people on SSC that can answer this in detail. I think it's worth noting that, to understand indexing it's important that you know the difference between a heap and a clustered index. In SQL server there are two types of "tables": a heap and a clustered index. A heap is a table that is not sorted; the data exists in whatever order.
This code will create a heap:
CREATE TABLE dbo.T1 (col1 int)
A clustered index is a "table" that is sorted by a specific key or combination of keys.
This code will create a clustered index:
CREATE TABLE dbo.T2 (col1 int PRIMARY KEY);
This code will also create a clustered Index:
CREATE TABLE dbo.T3 (col1 int);
ALTER TABLE T3 ADD pk_t3 PRIMARY KEY (col1)
The important thing to note is that tables don't have clustered indexes, they either are a clustered index or they are a heap. They are either an unsorted set of data (heap) or they are a sorted set (clustered) index.
unique index and non unique index with examples?
Again, huge topic and I'll let others answer this (I am on the train going to work) but I will say this: Unique indexes are good and often under utilized. Both clustered indexes (which are unique btw) and unique indexes are extremely helpful when you are sorting, grouping or getting a distinct set of values.
A great series of articles to read about indexes are the ones written by Gail Shaw on the subject: http://www.sqlservercentral.com/articles/Indexing/68439/[/url]
Edit: Fixed mistake that djj pointed out.
-- Itzik Ben-Gan 2001
February 10, 2015 at 6:54 am
In addition to Alan.b's link to Gails articles, there are is also another series called Stairway to SQL server Indexes, by David Durant, which is located here http://www.sqlservercentral.com/stairway/72399/
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 10, 2015 at 7:02 am
Alan.B (2/10/2015)
This code will create a heap:
CREATE TABLE dbo.T1 (col1 int primary key)A clustered index is a "table" that is sorted by a specific key or combination of keys.
This code will create a clustered index:
CREATE TABLE dbo.T2 (col1 int PRIMARY KEY);
Did you mean the first one to be?
CREATE TABLE dbo.T1 (col1 int)
If not I do not see the difference.
February 10, 2015 at 8:23 am
djj (2/10/2015)
Alan.B (2/10/2015)
This code will create a heap:
CREATE TABLE dbo.T1 (col1 int primary key)A clustered index is a "table" that is sorted by a specific key or combination of keys.
This code will create a clustered index:
CREATE TABLE dbo.T2 (col1 int PRIMARY KEY);Did you mean the first one to be?
CREATE TABLE dbo.T1 (col1 int)If not I do not see the difference.
Yes, thanks. Good catch. :blush:I fixed my code above so that it is now correct. (this is the problem with participating on the SSC forumns using your phone.)
-- Itzik Ben-Gan 2001
February 10, 2015 at 8:32 am
Alan.B (2/10/2015)
Yes, thanks. Good catch. :blush:I fixed my code above so that it is now correct. (this is the problem with participating on the SSC forumns using your phone.)
Had me questioning my understanding. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply