| Author |
Topic |
|
sql-lover
Yak Posting Veteran
99 Posts |
 Posted - 2012-08-02 : 14:28:08
|
| Primary Key vs Unique Clustered Index ...Yeah, I know, a question that has been asked thousand of times. But I never questioned myself on this until now.I just recently deployed a Unique Clustered Index on a table's column that is actually not null. So far so good.This morning one of my bosses asked me: "where is the PK you deployed last night, I can't see it, only the Unique Clustered Index".To my knowledge, they both act exactly the same, am I right about that? Assuming the column was defined as not null and all values are unique, I see no problem.I know the relational model and for some data modeling programs, the PK is important. But in terms of MS-SQL behavior, I believe there is no difference and I can leave it like that. I just don't want to do it again because the table is huge! Millions of records on it. Plus it has a lot of activity. Any change would be better during off peak hours.The follow up question would be: how can I create a PK, in case I want, without creating a Non Clustered Index on that column? Or in other words, can I create the PK without dropping the existing Clustered Index?Thanks in advance ... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-08-02 : 14:51:10
|
| ALTER TABLE myTable ADD CONSTRAINT PK_myTable PRIMARY KEY NONCLUSTERED(myColumn)Functionally there may be no difference, but from a logical standpoint I'd say it's better to make it a PRIMARY KEY. Every table should have a primary key, to avoid the kind of confusion you've already encountered. Worry less about clustered vs. nonclustered, that has nothing to do with PRIMARY KEY or UNIQUE. |
 |
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-08-02 : 15:02:43
|
| Thanks for reply.I do not want a non clustered index on that column. I want to keep that existing Unique Clustered Index and if possible or if I have to, create the actual PK keeping the existing Unique Clustered Index.I think that if the table has a Clustered Index already, the ALTER command will create the PK with a Non Clustered Index on it. Is that correct?It looks like it is not possible for me to create a PK now using the existing Unique Clustered Index unless I drop it and create it from scratch, with the T-SQL command. Am I right about that? |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-02 : 15:59:33
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-08-02 : 18:33:06
|
quote:
Originally posted by sql-lover
Thanks for reply.I do not want a non clustered index on that column. I want to keep that existing Unique Clustered Index and if possible or if I have to, create the actual PK keeping the existing Unique Clustered Index.I think that if the table has a Clustered Index already, the ALTER command will create the PK with a Non Clustered Index on it. Is that correct?It looks like it is not possible for me to create a PK now using the existing Unique Clustered Index unless I drop it and create it from scratch, with the T-SQL command. Am I right about that?
YOu kidna lost me on what you want to do. But, you can only have one clustered Key/Index as that (more or less) is the table. If you already have a clustered index, you can create a PK that is NONCLUSTERED. So, it really depends on what you want to do. You can certainly drop any and all indexes and create what you actually need/want. Just be careful incase there would be issues in your environment (i.e. heavy transactional load). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-08-02 : 18:38:11
|
quote:
Originally posted by sql-lover
It looks like it is not possible for me to create a PK now using the existing Unique Clustered Index unless I drop it and create it from scratch, with the T-SQL command. Am I right about that?
That's correct. You'll need to drop the unique clustered index you created and create the primary as clustered. Or just create the primary key as non-clustered and keep the unique clustered index, but you'd have a "duplicate" index in this case.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sql-lover
Yak Posting Veteran
99 Posts |
Posted - 2012-08-02 : 21:23:02
|
quote:
Originally posted by tkizer
quote:
Originally posted by sql-lover
It looks like it is not possible for me to create a PK now using the existing Unique Clustered Index unless I drop it and create it from scratch, with the T-SQL command. Am I right about that?
That's correct. You'll need to drop the unique clustered index you created and create the primary as clustered. Or just create the primary key as non-clustered and keep the unique clustered index, but you'd have a "duplicate" index in this case.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Thanks Tara,You just clarified and answer my question.And thanks to all the others who provided some help as well. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Primary Key vs Unique Clustered Index
Tara Kizer