3

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

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
Dust
  • 137
  • 2
  • 2
  • 8
  • What version of SQL are you using and why do you want to do this? – LowlyDBA - John M Jan 09 '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 09 '15 at 16:20

2 Answers2

13

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
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
3

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
Heisenberg
  • 1,505
  • 5
  • 18
  • 31