1

This question already has an answer here:

I have a table that is created as below.

CREATE TABLE [dbo].[Table1](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Title] [VARCHAR](255) NULL,
    ...

Lets say that the latest ID is 100 ( I have 100 records)

Is there a way to ALTER this table's IDENTITY column and change the auto increment value to now be 500?

So at the end i would have something like this ID 98, 99, 100, 500, 501, ....

I can not seem to find a solution for this in T-SQL

marked as duplicate by Paul White sql-server Jan 10 '15 at 1:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What version of SQL are you using and why do you want to do this? – LowlyDBA Jan 9 '15 at 16:19
  • SQLServer 2008 R2, because I am starting to use a new front end system and I would like the IDs to begin with a pre determined ID (500) so that i can easily tell if data was entered in old system (ID<500) or new system (ID>500) – Dust Jan 9 '15 at 16:20
9

You can use DBCC CHECKIDENT to reseed the IDENTITY column.

Here is a sample you can run:

SET NOCOUNT ON;
USE tempdb;
GO

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1));
GO

INSERT dbo.foo DEFAULT VALUES;
GO 100

-- note: set it to ([the next value you want] - 1)
DBCC CHECKIDENT(N'dbo.foo', RESEED, 499);
GO

INSERT dbo.foo DEFAULT VALUES;
GO 3

SELECT ID FROM dbo.foo WHERE ID >= 98;

Results:

98
99
100
500
501
502
2

You can use the following MSDN_DBCC CHECKIDENT Hope that helps!

DBCC CHECKIDENT (Transact-SQL)

You can use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT 
 ( 
    table_name
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

Example

USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO

Not the answer you're looking for? Browse other questions tagged or ask your own question.