March 31, 2016 at 8:57 am
Hi all,
Can any one tell me the reason why unique clustered index created by default when we create primary key.
What is the reason?
Thnx in Advnc.
March 31, 2016 at 9:22 am
Simply because Microsoft chose to assume that a PK would also automatically be the clustering key (if one didn't already exist, of course). Other dbms's don't make that assumption, SQL Server does.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 31, 2016 at 9:31 am
p.shabbir (3/31/2016)
Hi all,Can any one tell me the reason why unique clustered index created by default when we create primary key.
What is the reason?
Thnx in Advnc.
Don't know for sure, you'd have to ask MS developers. But I do think it's a good idea.
When presenting about indexes, I tell people that the best clustered index is the one they put thought in, but also that with very few exceptions, any clustered index is better than not having a clustered index. I am pretty sure that without this default, we'd see far more tables that have no clustered index at all.
March 31, 2016 at 9:32 am
More than assuming that the PK should be the clustering key, it's because the clustered index should be the first index in any table, and the PK needs an index to work correctly.
Of course, this is guessing what the developers had in mind when designing this.
March 31, 2016 at 9:33 am
p.shabbir (3/31/2016)
Can any one tell me the reason why unique clustered index created by default when we create primary key.
Strictly speaking, it isn't. A unique index can allow NULL values; a primary key can't.
CREATE TABLE #John (i int NULL)
INSERT INTO #John VALUES (NULL)
ALTER TABLE #John ADD CONSTRAINT PK_John_i PRIMARY KEY (i)
CREATE UNIQUE CLUSTERED INDEX UCX_John_i ON #John(i)
John
March 31, 2016 at 9:35 am
Luis Cazares (3/31/2016)
and the PK needs an index to work correctly
Hmm, not sure what you mean there. There's no problem at all explicitly creating a PK as nonclustered, even when it's the first index on a table and you later add a clus index. Yes, that method causes additional overheard in rebuilding the pk's index, but it's not a problem per se for SQL to implement.
Edit: Never mind. I misunderstood your comment originally, thinking you meant that SQL needed a clus index to implement a PK. But, yes, just having a PK does require that an index be built, which is what you really meant.
Still, personally, I don't like it defaulting to a clus index. The clus index is far too critical to be defaulted, it should be explicitly chosen based on an analysis of the table's actual needs.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 31, 2016 at 9:51 am
Thank you very much all.
Clustered key? Not understood.
And what is heap index? Does heap index and heap table both are same.
I know if the table does not contain an index is known as heap table.
Thnx.
March 31, 2016 at 9:57 am
ScottPletcher (3/31/2016)
Luis Cazares (3/31/2016)
and the PK needs an index to work correctlyHmm, not sure what you mean there. There's no problem at all explicitly creating a PK as nonclustered, even when it's the first index on a table and you later add a clus index. Yes, that method causes additional overheard in rebuilding the pk's index, but it's not a problem per se for SQL to implement.
I meant exactly what it says. A PK constraint needs an index. I never stated that it needs to be a clustered index. And yes, there's no problem on creating the PK as nonclustered and then define the clustered index, as long as there's no data on the table. When there's data, we have that overhead that could cause problems.
However, the first index defined for a table should be the clustered index. That's because all other indexes depend on the clustered index (which is basically the table).
EDIT: I saw you edited your post. I agree that the clustered index is too critical to be default, but I also believe that is too critical to be left out. The best option if to have everything defined correctly and don't rely on any defaults (way too many available).
March 31, 2016 at 9:57 am
The clustering key is the column, or set of columns, on which you choose to create your clustered index.
Where did you read the term "heap index"? A table without a clustered index is, as you mentioned, called a heap, so the term is something of an oxymoron.
John
March 31, 2016 at 10:07 am
I was asked by an Interviewer John.:-)
March 31, 2016 at 10:17 am
The confusion on the term "heap index" might have raised because a heap (no index or table need to be added to the term) is included in the view sys.indexes
March 31, 2016 at 10:27 am
Finally, what I have understood is since "first index defined for a table should be the clustered index" so, microsoft chose clustered index when creating PK.
Correct me if I am wrong.
Thnx for your patience.
March 31, 2016 at 10:51 am
We don't really know why MS chose to make the PK by default also the clustering index.
Personally I think it was more about making it "easy to use" so it could be installed and maintained "without a DBA". Up through SQL 2000 (or so), many SQL administrators were Windows admins or other non-database people, who also assumed the role of installing SQL and creating databases and objects. [SQL 7.0, in particular, was so dirt cheap, esp. with big company discounts, it often got installed everywhere.]
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 31, 2016 at 10:56 am
Once again thank you guys.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply