| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2008-09-22 : 08:06:51
|
| Hi there. I've developed quite a lot of Access databases and some SQL Server dbs. I usually go for an identity column for the PK. I can understand why I might want two columns to be a PK, perhaps a date and Invoice number etc. Recently I viewed a db at work that had about 6 columns that made up the PK, dates etc. Now, my question is vague as I can't show the schema but just wanted to ask if there was much point in having so many columns to make up a PK? has anyone had a situation where they had a similar number of columns that made the PK? why?Thanks for any feedback in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-22 : 08:10:25
|
| Its not number of columns that matters but the columns that uniquely define a row of table. PK must chosen such that the value of PK should be unique always. So there can be chances where you might need to take more tham one columns for getting the unique combinations. Its during such cases that you will have composite PK consisting of more than 1 column. |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2008-09-23 : 08:01:23
|
| Thanks for getting back to me visakh16Maybe what I saw was a badly designed database. I certainly wouldn't have included so many fields just to make a unique key.You see, I come from more of an Access background and SQL Server is relatively new to me.I once had a discussion on an Access forum about using an Autonumber (Identity) column over natural primary keys like insurance numbers etc. I sort of came to the conclusion that autonumbers where easier and, in my opinion they are.Do you think the same is true for SQL Server or should we try and find natural keys over Identity columns even if it means using composite keys? Or can an Identity coloumn suffice most of the time?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 08:08:11
|
quote:
Originally posted by cidr
Thanks for getting back to me visakh16Maybe what I saw was a badly designed database. I certainly wouldn't have included so many fields just to make a unique key.You see, I come from more of an Access background and SQL Server is relatively new to me.I once had a discussion on an Access forum about using an Autonumber (Identity) column over natural primary keys like insurance numbers etc. I sort of came to the conclusion that autonumbers where easier and, in my opinion they are.Do you think the same is true for SQL Server or should we try and find natural keys over Identity columns even if it means using composite keys? Or can an Identity coloumn suffice most of the time?Thanks
here also your conclusion come good. Most cases its better to keep identity columns as PK. The overhead in those case is very less and it will automatically incremented for each new insertion and will be unique also. |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2008-09-23 : 09:36:57
|
| Thanks visakh16 for your help.I have one more question. I've noticed that some older databases before SQL Server 2000 and even SQL Server 2000 seem to use natural keys (and more columns than one) as PKs. Was this a common trend back then?I see more of an Identity column being used nowadays rather than the Natural keys.Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-09-23 : 11:40:20
|
| natural vs surrogate keys is a long ongoing debate... so both are used but some prefer one and some prefer second.personaly i always use a surogate key (identity) because PK's have to be short, which most natural keys aren't.plus every natural key can be changed for whatever outside reason while surrogates can't._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 11:47:15
|
| One case where we see usage of surrogate key is in warehouse tables (Dimensions) |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-28 : 11:31:56
|
| Natural keys on multiple columns are not all bad and from a modelling perspective are more correct. They can actually help performance and simplicity by putting data across many tables thus avoiding joins back to other tables in many cases.It's still up for debate though as Spirit1 says. Just make sure you put a unique constraint on your natural key if you decide to use a surrogate. |
|
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-09-28 : 16:38:17
|
| You have to be careful while using composite keys as PK's, At many customers I have seen big PKey's (multiple columns) causing lot of IO issues; bigger key's means extra IO to read data, and because it brings that data in Buffer cache so extra memory consumption as well. Using Identity is good because of it is incremental nature, and narrower columns which helps in Indexing and makes search operations faster, without wasting space on DAS/SAN. Be prudent in selecting data type for your Identity column, if you are extpecting that table to grow very fast (millions/billions of records in a year or so) choose correct data type that fulfils your application requirment I know some application design doesn't allow -ve values in Identity column that left you with no option except +ve values means half of total range. Consider Replication scenarios if you forsee that to be implemented. |
|
|
|
|
Question about the primary key