April 16, 2010 at 4:46 am
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!!!
April 16, 2010 at 5:03 am
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
April 16, 2010 at 7:34 am
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.April 16, 2010 at 4:47 pm
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
April 16, 2010 at 4:55 pm
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 16, 2010 at 6:18 pm
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
April 17, 2010 at 2:45 am
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
April 17, 2010 at 2:55 am
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" 😉
April 17, 2010 at 10:43 am
Perry Whittle (4/17/2010)
when referring to key constraintsPrimary 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
April 17, 2010 at 10:50 am
That was a good catch. Looks like I need to write a good one pager on this and include all information
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 17, 2010 at 12:01 pm
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
April 17, 2010 at 12:10 pm
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
April 17, 2010 at 2:10 pm
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" 😉
April 17, 2010 at 4:13 pm
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
April 17, 2010 at 5:10 pm
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" 😉
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply