Should every table have an numeric (identity on) id field?
-
Plz. also let me know if not having an identity field affects performance
-
The rules of Normalization say that you should have non meaningful keys. An identity int is great for this purpose. This can be replaced by a RowGUID (uniqueidentifier). I also skip this rule when optimizing sometimes. If I want my key (the one and only clustered index) to be something meaningful, I might skip the ID or have it and not make it the key.
Short answer:
IMHO usually yes but sometimes no.LogicMagic
-
yeah, why not? no-one will notice a couple of extra bytes per row
identity fields can be great as alternate keys, if you have a bulky composite key then using a four or five byte numeric as your FK link/ search argument is much easier (and maybe faster too, not sure)
to avoid hot pages you should use a meaningful key (clientName or whatever) as your clustered index and have a non-clustered uniquer index on your Identity column... i say should, but as usual there's always an example where a particular method won't perform as well as it should... or even make sense.
so... in short stick one on, it won't hurt.
s
-
Not necessarily, If you were asked to setup SQL Replication and at some point then you would have to redesign the whole database schema, The IDENTITIES don't help a lot during these cases.
-
I beg to differ with the above. The rules of formal database design state that a "natural" meaningful key should be used if at all possible. A numeric identity key is an "unnatural" key that has nothing to do with the actual data. Using an identity column as a key is often the shortcut approach that leads to duplicates in the "real" data later.
-
having an identity column or not will not affect performance.
having good indexes will affect performance.
every table should have a clustered index.
every table should not have an identity column just for the sake of having a meaningless useless uniquely identifying column.
every table should have a meaningful business related key to ensure no duplicates can be inserted.
now you can for some reasons ( composite keys being one ) you can create an identity column as a clustered unique index on tables. this can reduce storage space and increase performance in some applications.
unique identifiers - please tell me when to use these in datatabse (unless it really is the natural key of some entity)?
/rockmoose
You must unlearn what You have learnt -
Rockmoose,
I prefer to have number-centric data structures. In other words, I like having an integer key for any entity that's not a "dead end" entity such as a type table or a table that will never be used as a "hub" in the data structure. By doing this, when you have to join all these together and process them, all the joins and index searches are done on numeric fields. Having said that, there should be constraints in place to make sure the natural key in enforced always. Otherwise, there is no integrity maintained in the relational model.
The end tables or hub tables on the other hand really make no sense to have an artificial key on. There are other exceptions such as replication. In replication though, you can achieve the same by having a domain key with an identity key. There are the same problems with artificial keys in replication because you have to maintain the integrity of the natural key.
Ahhh, so many things to consider. Isn't it grand.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Hey Mean Old Yak DBA !
"every table should have a meaningful business related key to ensure no duplicates can be inserted"
I did not say that this always is the key that tables are joined with 😉
I just wanted to make a point for any "not so mean younger dba's" to not start bying the delusion that putting unique identifier *yakh* or identity keys on each and every table is good practice or good database design.
That's where I thought this thread was going, only Ken Davis was making any sense.
/rockmoose
You must unlearn what You have learnt
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply