Primary Keys

Question

Primary Keys

Viewing 13 posts - 16 through 27 (of 27 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Stewart "Arturius" Campbell (7/30/2012)
Good reminder, Thanks Dwain
If anyone comes even close to that limit, however, I would seriously propose a remedial database design course, with a healthy heaping of normalisation thrown in for good measure...:-P

+1

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

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Jeff Moden (7/30/2012)
I was pretty sure of the answer but went to check just to be absolutely sure because I've never gotten close to the limit. It just absolutely fascinates me that this little bit of very important "trivia" ISN'T covered under the constraints, primary key, or CREATE TABLE sections of BOL.

Me too.

First I was like, well it's a CLUSTERED INDEX and those can only have 16 columns.

Then I was like, well if it is explicitly made non clustered, it make a non clustered index so it can still have only 16

But then I had to go check just to be sure. 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Ken SSCoach Points: 17191 More actions · Answer 3

Good question. I recalled this limit being mentioned recently as related to another QOTD.

EL Jerry SSCertifiable Points: 7061 More actions · Answer 4

Good question. Thank you for the easy point.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Revenant SSC-Forever Points: 42467 More actions · Answer 5

A nice and easy one to start the week with... Thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Thomas Abraham (7/30/2012)
Jeff Moden (7/30/2012)
I was pretty sure of the answer but went to check just to be absolutely sure because I've never gotten close to the limit. It just absolutely fascinates me that this little bit of very important "trivia" ISN'T covered under the constraints, primary key, or CREATE TABLE sections of BOL.
Same reaction, both to the question and BOL. Also, wasn't there a recent question that was tangentially related to this? Something about large data types in an index?

Maybe the recent question about an XML index, which will hijack one of the 16?

Tom

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

Wow! I would have never imagined that such a simple basic question would generate so much dialogue.

It turns out that I was trying to create a key with 18 components and I got rejected. Note that this was a pretty contrived case (I'd never actually design a database like that :-D) in a temporary table whereI was hoping the additional key elements might improve performance.

Surely now it is something I'll never forget either.

Thanks to all for stopping by and commenting!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

PravS SSC Journeyman Points: 93 More actions · Answer 8

I GOT IT WRONG!...i referred following page

http://msdn.microsoft.com/en-us/library/ms191236(v=sql.105).aspx

which says

“If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.”

GOOD QUESTION…… +1

Thank you

D.Oc SSCrazy Points: 2875 More actions · Answer 9

Good question,

Thanks!

-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 10

easy but very important question !!!

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

L' Eomot Inversé (7/30/2012)
Thomas Abraham (7/30/2012)
Jeff Moden (7/30/2012)
I was pretty sure of the answer but went to check just to be absolutely sure because I've never gotten close to the limit. It just absolutely fascinates me that this little bit of very important "trivia" ISN'T covered under the constraints, primary key, or CREATE TABLE sections of BOL.
Same reaction, both to the question and BOL. Also, wasn't there a recent question that was tangentially related to this? Something about large data types in an index?
Maybe the recent question about an XML index, which will hijack one of the 16?

Yes, that was how I got it right. The XML index question was fresh on my mind. I can't imagine coming close to this limit so it's not one that will be on the tip of my pencil.

Neha05 Default port Points: 1494 More actions · Answer 12

Neha05

Default port

Points: 1494

August 8, 2012 at 11:31 am

#1523281

Good question!

kalyani.k478 Default port Points: 1400 More actions · Answer 13

kalyani.k478

Default port

Points: 1400

October 18, 2012 at 5:42 am

#1550415

+1