SQL Server and IDENTITY

gmhanna

Hi, I'm not sure this is the best way to do this. I have an agent running as a service on thousands of servers. Each server can delete and add several hundred records into one table everyday. I have set the KEY as an integer using IDENTITY(1,1). During the initial testing I have noticed that SQL Server does not reuse the KEY. This makes me wonder what happens when I've added and deleted 4 Billion records (The max size of an Integer). Does it automatically wrap and I don't need to worry about this? This is SQL Server, and I'm wondering if I should be doing a database REORGs like we do with DB2. I know it would of been nice to be able to use a KEY and not worry about this. From what I've read the serial number from each distributor is supposed to be unique however I've run into duplicates right from the start where the distributor is using zero's as the serial number. Thanks, Glenn

Glenn

David Mujica

Quick thought. Can you define the column as BIGINT ? Check the capacity out: http://technet.microsoft.com/en-us/library/ms187745.aspx[^]

gmhanna

Yes, I can define it that way, but that only moves the potential problem, doesn't fix it.

Glenn

Richard Deeming

gmhanna wrote:

that only moves the potential problem, doesn't fix it.

How much data are you inserting? :omg: Assuming 1,000,000 servers, each inserting 1,000,000 records per day, it would take you just under 25,252 years 332 days to use up the capacity of a bigint column starting at 1 and ignoring negative keys. If you start at the minimum value, it would take just under 50,505 years 298 days. At that point, I think it's fair to say it's Someone Else's Problem.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mycroft Holmes

Go with bigint on all transaction tables, int breaks when it hits the limit. The horizon for bigint is beyond your life time so settle for that.

Never underestimate the power of human stupidity RAH

ZurdoDev

Bit it sure is nice to see someone planning for the future. He definitely learned from Y2K. :)

There are only 10 types of people in the world, those who understand binary and those who don't.

S Douglas

gmhanna wrote:

wonder what happens when I've added and deleted 4 Billion records

Instead of running a delete command, use truncate table. That will reseed the identity field, thus allowing you to regenerate the numbers. (Not that there is anything wrong with what others suggested of just using a BIGINT for the ID field. Once MAX INT has been reached you will no longer be able to insert records into that table.) 2) If you are unable to truncate the table, then you can reseed the key. More information can be found here http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/[^] A quick example

CREATE TABLE Test(id INT IDENTITY(1, 1), TestValue INT)
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO
SELECT * FROM test
GO
DELETE test
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO
SELECT * FROM test
GO
TRUNCATE TABLE TEST
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO
SELECT * FROM test
GO
DBCC CHECKIDENT(test, reseed, 1)
GO
INSERT INTO test VALUES(1)
INSERT INTO test VALUES(2)
INSERT INTO test VALUES(3)
INSERT INTO test VALUES(4)
INSERT INTO test VALUES(5)
GO
SELECT * FROM test
GO
DROP TABLE test

gmhanna wrote:

This is SQL Server, and I'm wondering if I should be doing a database REORGs like we do with DB2.

From IBM's website, looks like the same feature would exist in SQL Server as clustered index management (more information http://technet.microsoft.com/en-us/library/ms189858.aspx[^])

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.