question

Katie 1 avatar image
Katie 1 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.
sql-server-2008sqldatabase-design
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Having several foreign key relations into this table will give you more performance problems than the identity
0 Likes 0 ·
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.
0 Likes 0 ·
Oleg avatar image
Oleg answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 :)
4 Likes 4 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
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
2 Likes 2 ·
@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...
2 Likes 2 ·
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.
1 Like 1 ·
@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.
1 Like 1 ·
Scott Hauder: My point with saving space is that often the candidates for a natural compound key are already present in the table, and will be indexed. Again - an Orderline table will have both a sequence number and a FK to OrderID. Both will be indexed, probably in a compound index. That index will be unique (with or without a unique constraint). It's also a perfect clustered index candidate. I'm no normalization fundamentalist, but I'm no fan of unnecessary columns either.
0 Likes 0 ·
This is the classic debate right, all natural keys vs. all artificial. I lean towards the artificial side of the argument because I've seen too many instances where the "we'll never change this piece of data" natural key gets changed and then everything goes into the pot. But, I understand and agree with many of the arguments for using natural keys, even as the cluster. But, all things being more or less equal, I'll still probably tend towards the artificial key, which means identity.
0 Likes 0 ·
@Grant - would you call a foreign key to an artificial key 'natural'? In the example I mentioned, the TransactionID was basically an artificial key in the transactions table - so I actually have no idea what you would call that! :)
0 Likes 0 ·
@Grant - HAHAHA. Nancy it is :)
0 Likes 0 ·
David 1 avatar image
David 1 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.