Alex Rivera | Logout

Auto increment feature in Database

Asked 2010-11-02T07:16:26.637
11

I use SQL Server and when I create a new table I make a specific field an auto increment primary key. The problem is some people told me making the field an auto increment for the primary key means when deleting any record (they don't care about the auto increment field number) the field increases so at some point - if the type of my field is integer for example - the range of integer will be consumed totally and i will be in trouble. So they tell me not to use this feature any more.

The best solution is making this through the code by getting the max of my primary key then if the value does not exist the max will be 1 other wise max + 1.

Any suggestions about this problem? Can I use the auto increment feature?

I want also to know the cases which are not preferable to use auto increment ..and the alternatives...

note :: this question is general not specific to any DBMS , i wanna to know is this true also for DBMSs like ORACLE ,Mysql,INFORMIX,....

Thanks so much.

asp.net mysql sql-server database sql-server-2005
Edit
Report

1 Answer

11

You should use identity (auto increment) columns. The bigint data type can store values up to 2^63-1 (9,223,372,036,854,775,807). I don't think your system is going to reach this value soon, even if you are inserting and deleting lots of records.

If you implement the method you propose properly, you will end up with a lot of locking problems. Otherwise, you will have to deal with exceptions thrown because of constraint violation (or even worse - non-unique values, if there is no primary key constraint).

answered 2010-11-02T07:21:55.687

Your Answer