I have a lock table in my database, in which locks get created and deleted very often (they are just a record in the lock table). I used INT as the PK. Some of the projects this will be used for are very large (can run up to 10 years). What happens when the PK reaches its max, does SQL Server start back at 1? Just curious , I have never tested it.

good question, but you could define the table, set identity insert on, insert 1 row that is 1 digit prior to the max.

then turn off identity indert, and do another insert. should be a 5 minute test.

Skeeterbug wrote:

I have a lock table in my database, in which locks get created and deleted very often (they are just a record in the lock table). I used INT as the PK. Some of the projects this will be used for are very large (can run up to 10 years). What happens when the PK reaches its max, does SQL Server start back at 1? Just curious , I have never tested it.

I believe it restarts at 1 again... but would be interested in hearing the result of your test. http://www.windowsitpro.com/SQLServer/Article/ArticleID/21970/21970.html might be of interest!

http://www.databasejournal.com/features/mssql/article.phpr/2212141

int - Integer data from -2^31 through 2^31 - 1

Gives us:

2,147,483,648

So if (on average):

-1,000 Locks created per day

-360,000 per year

It would take 5,965 years for this to reach its max.

Looks like I don't need to worry about this

Devildog74 wrote:

good question, but you could define the table, set identity insert on, insert 1 row that is 1 digit prior to the max.

then turn off identity indert, and do another insert. should be a 5 minute test.

I tested this and as expected you get an arithmetic overflow.

create table testmaxint (pk int primary key identity(2147483645, 1), label varchar(100))
go

INSERT INTO testmaxint (label) VALUES ('First record')
INSERT INTO testmaxint (label) VALUES ('Second record')
INSERT INTO testmaxint (label) VALUES ('Third record')
INSERT INTO testmaxint (label) VALUES ('Fourth record')
go

The indentity will not restart.

Using a TRUNCATE TABLE will reset to the original seed, which in my example only gave me 3 good inserts. Your best bet is starting with a seed of the lowest limit of the int with a value of -2,147,483,647 which will allow about 4.3 billion unique values.

Of course, if you could add into your maintenance plan some way to TRUNCATE the table, perhaps weekly or whatever you would never have a problem.

BOb

pilotboba wrote:

Devildog74 wrote:

good question, but you could define the table, set identity insert on, insert 1 row that is 1 digit prior to the max.

then turn off identity indert, and do another insert. should be a 5 minute test.

I tested this and as expected you get an arithmetic overflow.

create table testmaxint (pk int primary key identity(2147483645, 1), label varchar(100))
go

INSERT INTO testmaxint (label) VALUES ('First record')
INSERT INTO testmaxint (label) VALUES ('Second record')
INSERT INTO testmaxint (label) VALUES ('Third record')
INSERT INTO testmaxint (label) VALUES ('Fourth record')
go

The indentity will not restart.

Using a TRUNCATE TABLE will reset to the original seed, which in my example only gave me 3 good inserts. Your best bet is starting with a seed of the lowest limit of the int with a value of -2,147,483,647 which will allow about 4.3 billion unique values.

Of course, if you could add into your maintenance plan some way to TRUNCATE the table, perhaps weekly or whatever you would never have a problem.

BOb

Thank you Bob

Out of curiosity why don't you use a GUID instead of an identity column, it sounds like your deleting as well inserting and not creating a history of data, where a clustered identity column would be beneficial. If all your looking for is short term unique value a GUID is a better answer and it will always be unique.

John

jtgooding wrote:

Out of curiosity why don't you use a GUID instead of an identity column, it sounds like your deleting as well inserting and not creating a history of data, where a clustered identity column would be beneficial. If all your looking for is short term unique value a GUID is a better answer and it will always be unique.

John

Thanks for the tip. I changed the table to use a GUID instead.