asked
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.
Having several foreign key relations into this table will give you more performance problems than the identity
I don't know. I wouldn't write off FKs as a performance problem until you're 100% certain you have more writes than reads.
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
If you need more use a bigint, or if you *really* like 4byte identities, seed the table at -2,000,000,000 and get a bonus 2 billion rows :)
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.
Identity is not necessarily a good candidate for a PK as many people definitely prefer to have PKs which makes sense to the business users. It is an excellent candidate for a unique clustered index though because of its ever-increasing nature and small width. This means that it is totally valid to have a table clustered by the identity column with a non-clustered natural primary key. The former prevents page splits during inserts as the new records go straight to the tail while the latter allows fast non-clustered index seeks for queries expected to return a rather small number of records.
I definitely prefer the clustered identity PKs--save for the common scenarios that Magnus points out. Unique indexes on natural keys are fine but I've worked on dbs with only natural keys for the clustered index and many of them were 5-7 columns wide and 50+ bytes. They may seem elegant to the designer but imho writing queries and joining several tables each with 5 columns really blows not to mention the non-clustered index bloat. Further it becomes inelegant when you simply need a query of the type WHERE CustomerID NOT IN (SELECT CustomerID...) forget it, you need to find a more lengthy solution
@Magnus - I totally agree about the Clustered index - with one caveat - that you have some sort of guarantee that the rows going into the table will be in increasing order. I had a big database a while back which stored transactions, and several tables were linked to the transactions table - they all had a non clustered PK on the identity column - and were clustered on the TransactionID. However, I would not have felt comfortable with that if I didn't know that the TransactionID would always grow, and always be inserted roughly in the right order...
I'd say there are often much better Clustered Index-candidates than the identity-column of a table, regardless of natural key-existance. In my example with Order/Orderline - even if Orderline-table would have an identity-column as PK, I would still use OrderID as clustered index. If OrderID would be a non-clustered index, it would mean SQL Server have to jump from index-tree to data-page for each orderline. With OrderID as clustered index, SQL Server would just have to seek the index-tree, jump to the first datapage, and then stay on the datapages until all orderlines for the OrderID has been found. There will be fragmentation even with Identity clustered index. The size of a row might grow (a varchar-column being updated), thus having to be moved to a new page, a row can be deleted etc.
@Matt Good question. An approach I've used a lot recently works off the idea of creating an identity column on top level objects (along with a unique constraint on the true natural key) and then all the child objects, usually defined by combinations of top level objects, all have a compound PK consisting of FKs to the parents... What do you call that? I don't know. I'd actually call it the natural key, but it's a natural key consisting of artificial values from the parents... I think we should call it Nancy.
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.
