Is it a good idea to use Identity on a table with extensive writes in the real time ?
Is it a good idea to use identities on a table. I am not sure at what point it will start creating problems? if i have a database table which will have extensive writes in the production evnivronment, what is the aproach i should take what are some of the possible alternatives to the identity? Thanks.
Identity should not start creating problems unless you get it to flip over the maximum value. For example, if your column is **int** identity(1, 1) then the maximum value it can hold is 2,147,483,647. If your table is not approaching the 2 billion mark yet then there is no danger here. Since extensive writes are probably wrapped into transactions, it is possible that you will have some holes in your identity column values because the identity is not subjected to rollback the current value should the transaction rollback take place (it behaves identically as do the Oracle's sequences). This behaviour is by design and it does prevent the possibility of duplicate values. The only way to run into trouble with identitity is by writing error-prone script to incorrectly reseed the current value. It is much easier to implement identity column than to attempt to design a pseudo-identity by yourself. Just my 2 cents. Oleg
I agree with Oleg - identity probably won't be a big problem for you. But I myself am not a big fan of identity. So often is it used when there's no need for an identity columnn. For example when there are perfect candidate columns for a compound, natural key. One example is Order-Orderline relations, where I see no point in using identity for the Orderline table. Instead, I would use a compound primary key on OrderID and OrderlineNumber. If you don't have a good candidate for a natural primary key, don't worry, identity column will do fine. But if you do have a good natural key candidate, use that instead and save some space.