Sql-server – Change next auto-number assignment without recreating entire table, MS SQL Server 2000

sql server

I've got a table with a primary key that is an int, auto-number auto-increment. Your standard primary key type field.

Due to a complicated scenario with a previously replicated database, now I've got rows already in the table with values up to 410000 yet the value report from IDENT_CURRENT is only 400060 or so. Now, I've got code that is trying to insert records and blowing up because the database thinks that it should use number 400060, and yet I already have a record with that id. Kaboom.

I've looked into manually setting the next number in the table designer, but when viewing the script it generates, it is going to create a duplicate table, copy all records to it, drop the old table, rename it, do some stuff with constraints, etc. It's all very scary when I just need a simple change.

Is there a back way to change this value that won't cause so many unneeded changes?

Best Answer

use the following on your table

dbcc checkident ('dbo.table',reseed,410000)

it changes the current identity value to 410000, so the next insert will be whatever the icrement is plus 410000 (so 410001 if the increment is 1).

Related Solutions

Sql-server – Correct value for fill factor for clustered indexes with surrogate identity keys

It Depends

It's a balancing act. If your table is read intensive, with not many updates or deletes then the default (which is 100) should be ok.

If your table is very write intensive, with lots of updates, then a value below 80 might be more appropriate.

There's no magic formula for this stuff. (AFAIK, if there is please let me know) Best thing to do is have a test environment, have some workload to test. Make the changes & see how your database performs with the workload.

Sql-server – SQL Import to update existing records

In SQL Server 2008 you could put the data into a new table and use the MERGE statement with your existing table as the target, and your new table as your source.

Instead, you might prefer to put it into a new table and use that as your source with an old method (using two steps unfortunately):

UPDATE t SET col1 = s.col1, col2 = s.col2
FROM targetTable t
   JOIN
   newTable s
    ON s.id = t.id ;

INSERT targetTable (columnlist)
SELECT s.col1, s.col2, ...
FROM sourceTable s
   LEFT JOIN
   targetTable t
    ON s.id = t.id 
WHERE t.id IS NULL;

Bear in mind you may need to turn on IDENTITY_INSERT on your target table if you're trying to insert into an identity column as well.

Best Answer

Related Solutions

Sql-server – Correct value for fill factor for clustered indexes with surrogate identity keys

Sql-server – SQL Import to update existing records

Related Topic