identity

Question

Post reply

identity

bfarr23

Right there with Babe

Points: 787
More actions
July 11, 2003 at 9:16 am

#65303

do most people use identity for primary key column?
are there any bad things about using this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Rodrigo Acosta

SSC-Insane

Points: 21261

July 11, 2003 at 1:30 pm

#465172

Are you planning to replicate this table?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Typically the primary key also is the clustered index. If you use an identity, prepare for a page splits hotspot, subsequent fragmentation, and the performance decreases from them. These aren't show stoppers, you just need to be aware of them and take the proper maintenance steps.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

patisi SSC Enthusiast Points: 128 More actions · Answer 3

This is an age old question. There is no reason not to per se, it is more important to determine the use of the table and consider issues like replication which have work arounds. Also, consider updates to the key in the future and whether you plan to implement RI in the Database model. These are just a few of the considerations you have be aware of. Overall, there are arguments both ways it really depends on many factors that you did not mention.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

Also, there is a problem in SQL Server with identity column when recovering from crashes. This has existed in SQL Server since at least v4.2.

Suppose you have an identity column in a table, and have added 1000 rows (w/o any deletes). If the server crashes (say if you pull the plug out of it), when SQL Server is recovering, the next identity value will be increased to 1,000,000. Crash again, and the next identity value will go to 10,000,000.

Each time this SQL Server does not shut down "gracefully", the next identity value increases like this. Consequently, you will run out of values long before you actually use 4 billion+ rows (depending, of course, on how often you have these "crashes").

Yes, you can reset the next identity value after the crash/recovery. But who will actually remember to do so. I have tested this as each new version of SQL Server has come out, hoping that MS has fixed this, but it still occurs. Maybe with Yukon... ???

In general, I do not use identities for "permanent" record ID (i.e. CustomerID, OrderID, etc). I will use a uniqueidentifer so that there will be no way ever to run out of values, for any reason. I will use only use identities for tables that are more temporary or that will be dropped regularly (i.e. audit log, daily transaction dump, etc.).

Mark

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
Also, there is a problem in SQL Server with identity column when recovering from crashes. This has existed in SQL Server since at least v4.2.
Suppose you have an identity column in a table, and have added 1000 rows (w/o any deletes). If the server crashes (say if you pull the plug out of it), when SQL Server is recovering, the next identity value will be increased to 1,000,000. Crash again, and the next identity value will go to 10,000,000.
Each time this SQL Server does not shut down "gracefully", the next identity value increases like this. Consequently, you will run out of values long before you actually use 4 billion+ rows (depending, of course, on how often you have these "crashes").
Yes, you can reset the next identity value after the crash/recovery. But who will actually remember to do so. I have tested this as each new version of SQL Server has come out, hoping that MS has fixed this, but it still occurs. Maybe with Yukon... ???
In general, I do not use identities for "permanent" record ID (i.e. CustomerID, OrderID, etc). I will use a uniqueidentifer so that there will be no way ever to run out of values, for any reason. I will use only use identities for tables that are more temporary or that will be dropped regularly (i.e. audit log, daily transaction dump, etc.).

Do you have any links to documentation on this. I have never seen this condition and have purposely killed the server thru use of debug to test certain things. I would like to know what the details are behind this.

jcool SSC Eights! Points: 863 More actions · Answer 6

I use identity as a primary key of a table for easier handling of records that is strictly local for a table. Example: triggers (joining inserted and deleted), positioning after requery in a form (Access), ...

I use unique constraints for foreign keys instead of PK. I never take identity from one table and put it into another because it has a bad continuity problems (you get gaps in numbering). For continuing numbering I use instead of insert trigger and generate numbers that have no gaps (and are human readable, not like GUID).

If you depend on identity as your unique key and people have to work with its value, it will become very cumbersome after you have used that table for years and identity has become very large.

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 7

quote:
Also, there is a problem in SQL Server with identity column when recovering from crashes. This has existed in SQL Server since at least v4.2.
Suppose you have an identity column in a table, and have added 1000 rows (w/o any deletes). If the server crashes (say if you pull the plug out of it), when SQL Server is recovering, the next identity value will be increased to 1,000,000. Crash again, and the next identity value will go to 10,000,000.

I am sorry I ALWAYS OR MOST OF THE TIME use identity for primary key column and NEVER faced this problem. I am using SQL server for development for the last 6+ years

With SQL Server 6.0, I had some problems when the server crashes current value changes to a lower value. I worked with 6.5 only for a short period, and from 1999 I am working with Server 7 and 2000.

I prefer identity for some reasons

1. All manually entered "primary keys" are subject to changes. (e.g. For the purpose of reporting people may request re-order the primary keys) It is not a good practice to change the primary keys. But when the business grows the needs too grow. So keep an additional field (with tinyint, smallint, int or even bigint if you expect more rows) and refer that in all tables referencing this Primary table. More than that multiple updates on the table (where primary key too is changing) where you have some update triggers (where you refer inserre\ted and Updated tables) will be a big headache.

2. Some times primary key may be a composit key. It is difficult to refer in sps, triggers etc, and may lead to errors. specially if we have another table referencing this primary table it will take additional space.

3. Only if insert fails Gaps happens. If that is happening frequently, it has to be handled seperately. having gaps is a good reason for checking the insert statements. As I update only through sps, and validate all the data before inserts (in order to give friendly message to the user) I hadly face that problem.

4. It could be usefull to check the order the data had been entered

There are some drawbacks too.

1. Querying the referenced data for reporting purpose may need additional columns or even additional joins. This decreases the performance.

2. They are having a maximum value. It means, one day you may reach that maximum value and you can't enter after that.

3. searching a range of identtiy fields is useless (most of the time). Primary keys are actually usefull for Range searches.

Now it is up to you to decide.

G.R. Preethiviraj Kulasingham

Chief Technology Officer.

Softlogic Information Systems Limited,

14 De Fonseka Place,

Colombo 05.

Sri Lanka.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

bfarr23 Right there with Babe Points: 787 More actions · Answer 8

I will be replicating this.

(sql server 2000 transactional replication.)

cross SSCommitted Points: 1735 More actions · Answer 9

What would one do if a database started with identity as PK and then years later became larger than expected and ran out of numbers to increment to? How can you fix it so your application could keep running?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

quote:
What would one do if a database started with identity as PK and then years later became larger than expected and ran out of numbers to increment to? How can you fix it so your application could keep running?

Alter the column to make the datatype bigint.