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.
