Is Primary key clustered index different from Unique key clustered index??

Question

Is Primary key clustered index different from Unique key clustered index??

splinter-740692

SSC Eights!

Points: 901
More actions
April 16, 2010 at 4:46 am

#386718

Hi,
I have a SQl table which has a primary key defined onthe three columns. The index description for the primary key states as 'clustered, unique, primary key on PRIMARY'.
My questions is now is the defined primary key a Clustered, unique primary key or just Clustered primary key??
Please help!!!

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

splinter-740692 (4/16/2010)
Hi,
I have a SQl table which has a primary key defined onthe three columns. The index description for the primary key states as 'clustered, unique, primary key on PRIMARY'.
My questions is now is the defined primary key a Clustered, unique primary key or just Clustered primary key??
Please help!!!

Primary keys ARE unique, so the two are the same.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

splinter-740692 (4/16/2010)
My questions is now is the defined primary key a Clustered, unique primary key or just Clustered primary key??

Primary Keys are enforced by a unique index.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Ditto as the other two, just adding that the final Primary in your definition refers to the filegroup on which the clustered index was created.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

PK != clustered, and they don't have anything to do with each other. It's like saying a tall, red building. Tall and red don't necessarily relate in any way.

PK - unique way of identifying each row. It can contain one or more fields to make uniqueness. Note that you can have other unique indexes that are not PKs.

Clustered - Data stored in order of the index. Non clustered means just an index, like the index in a book.

ON PRIMARY means the location in terms of filegroups. As Jason mentioned, this means this index is in the Primary filegroup

WayneS SSC Guru Points: 95460 More actions · Answer 5

Good answer Steve.

My answer was based on

My questions is now is the defined primary key a Clustered, unique primary key or just Clustered primary key??

, where the only difference was the word "unique".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

just to add to the solutions that have been posted already.

The term "primary key" comes from the concept of data analysis (normal forms, ...).

It determines the attribute(s) [columns] that uniquely identify a row in an entity

.

In an RDBMS, a foreign key should point to a primary key to enforce the relationship.

SQLServer also lets you declare a foreign key refering to (any) unique index of a table.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

when referring to key constraints

Primary key does not allow NULLs

Unique key allows a single NULL value

so the 2 are different in that respect

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Perry Whittle (4/17/2010)
when referring to key constraints
Primary key does not allow NULLs
Unique key allows a single NULL value
so the 2 are different in that respect

Man, I was going to jump on this at the first post. Good thing I'm trying to read the whole thread before responding. Good catch, Perry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

That was a good catch. Looks like I need to write a good one pager on this and include all information

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 10

Good catch, Perry.

That is indeed fundamental !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Steve Jones - Editor (4/17/2010)
That was a good catch. Looks like I need to write a good one pager on this and include all information

Heh... nah... it's one of my favorite interview questions for people who are certified. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

Jeff Moden (4/17/2010)
Man, I was going to jump on this at the first post. Good thing I'm trying to read the whole thread before responding. Good catch, Perry.

ha ha, pipped you to the post Jeff!! 🙂

as we all know a PK can't allow nulls otherwise where would referential integrity be?

Incidentally Jeff when you ask this question at interview and they get it wrong does that then become pork chop territory 😀 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Perry Whittle (4/17/2010)
Jeff Moden (4/17/2010)
Man, I was going to jump on this at the first post. Good thing I'm trying to read the whole thread before responding. Good catch, Perry.
ha ha, pipped you to the post Jeff!! 🙂
as we all know a PK can't allow nulls otherwise where would referential integrity be?
Incidentally Jeff when you ask this question at interview and they get it wrong does that then become pork chop territory 😀 😉

Heh... man, I've got to tell you that interviewing people has really become a disappointment. This is supposed to be a friendly "warm up question" for DBAs and Developers that no one who's even walked past a T-SQL book should get wrong. If they get this one wrong, I chalk it up to "nerves" and have them discuss the question a bit to see if it's nerves or if they really don't know the answer. If they really don't know the answer, then I try a couple of other simple "warm up questions". If they get another one wrong, that's usually the end of the interview. Sounds harsh but I'm not looking for casual users of T-SQL or a "C#/Java programmer with some SQL" when I conduct an interview for a DBA or SQL Developer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Jeff Moden (4/17/2010)
T-SQL or a "C#/Java programmer with some SQL" when I conduct an interview for a DBA or SQL Developer.

ahh that old chestnut. Procedural vs set based 😀

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉