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?
asked Nov 30, 2010 at 02:19 PM in Default
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.
answered Nov 30, 2010 at 02:42 PM
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.
answered Nov 30, 2010 at 03:10 PM
Don't use an IDENTITY column if you don't need it. I've seen examples where IDENTITY was added to tables for no good reason. Doing that just bloats the table and indexes needlessly.
answered Dec 01, 2010 at 05:09 AM