February 3, 2009 at 1:48 pm
Can anyone tell me are there any limits on how many columns should be in composite key? I have a unique combinations of 28 fields that I nedd to store in a table. Any sugestions?
February 3, 2009 at 1:54 pm
There's a 16 column limit to index keys. As Kimberly Tripp is fond of saying, that's a limit, not a goal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2009 at 1:55 pm
Maximum number of columns (excluding INCLUDED columns) is 16.
February 3, 2009 at 2:10 pm
Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.
Yes index column include was a big feature in SQL Server 2005 but the 16 columns must be 900 bytes or less. So instead of creating one big composite index you can use the index include feature to include columns used by your query as included columns in the index.
http://msdn.microsoft.com/en-us/library/ms188783.aspx
Kind regards,
Gift Peddie
February 3, 2009 at 2:19 pm
ivana.andric (2/3/2009)
Can anyone tell me are there any limits on how many columns should be in composite key? I have a unique combinations of 28 fields that I nedd to store in a table. Any sugestions?
[font="Verdana"]Time to redesign. Seriously. If you give us some context, we can make more suggestions. But a unique composite key of 28 fields just isn't practical.[/font]
February 3, 2009 at 2:22 pm
Bruce W Cassidy (2/3/2009)
ivana.andric (2/3/2009)
Can anyone tell me are there any limits on how many columns should be in composite key? I have a unique combinations of 28 fields that I nedd to store in a table. Any sugestions?[font="Verdana"]Time to redesign. Seriously. If you give us some context, we can make more suggestions. But a unique composite key of 28 fields just isn't practical.[/font]
Or you need to use a SID, a synthetic id.
February 3, 2009 at 2:26 pm
Lynn Pettis (2/3/2009)
Or you need to use a SID, a synthetic id.
[font="Verdana"]I suspect that will be the end result of a re-design, but first an introduction to the five "normal forms" might help. :P[/font]
February 3, 2009 at 2:30 pm
Actually, I've heard, but can't confirm, that if follow the rules of normalization far enough, you end up back where you started. Why you'd do that, I have no idea other than as a validation of the process.
Anyone else hear or read this somewhere?? I thought I heard it at a TDWI conference a couple of years ago.
February 3, 2009 at 2:39 pm
Lynn Pettis (2/3/2009)
Actually, I've heard, but can't confirm, that if follow the rules of normalization far enough, you end up back where you started. Why you'd do that, I have no idea other than as a validation of the process.Anyone else hear or read this somewhere?? I thought I heard it at a TDWI conference a couple of years ago.
[font="Verdana"]The "rules of normalisation" only go up to 5. After that, it's all hypothetical. 5th normal form isn't that hard to achieve, and it certainly wouldn't take you back to where you started. I mean, in each case, you are breaking out sub-sets from sets and creating relationships between sets. To get back where you started, you would have to do the reverse.
Are you sure you weren't confusing this with denormalisation? Which is a topic I would expect from a TDWI conference.
[/font]
February 3, 2009 at 3:11 pm
Nope, I am sure what I heard was normalization. I understand denormalization. I am aware of up to 6th normal form (no nulls).
February 3, 2009 at 3:28 pm
Lynn Pettis (2/3/2009)
I am aware of up to 6th normal form (no nulls).
From what I understand that was never formally "Codd-ified". I'd be interested to read some reference on 6th normal form if you have it. But I believe only the first 5 normal forms were agreed as "normal forms".
February 3, 2009 at 3:31 pm
I'd never use 6th normal form. Why would you want a table for evey column in your database? Think of all the joins you need to get the data back out!
February 3, 2009 at 3:37 pm
Lynn Pettis (2/3/2009)
I'd never use 6th normal form. Why would you want a table for evey column in your database? Think of all the joins you need to get the data back out!
[font="Verdana"]Oh, agreed. In practice, it's not quite that bad -- you'd only need a table per column that can contain nulls. My database designs usually make use of place-holders within keys rather than nulls anyway, so it's only nullable attributes for which I would need another table.
But yeah, I don't bother. Too much like hard work. Just create the nullable column and move on.[/font]
February 9, 2009 at 9:14 am
Hi
I am curious to know what kind of table is this?? 28 columns to make it unique? 😀
Do you have all the columns in database in single table 😉
Certainly there is something worng with the database design :hehe:
Thanks -- Vijaya Kadiyala
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply