Changing the seed and increment values on the identity column

Question

Old Hand

Points: 325

July 5, 2005 at 5:18 am

The following example changes the seed and increment values on the identity column.

CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))

INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')

ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)

This is an example which can change the seed and increment values on the identity column.

But it is throwing an error :

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

Please help me, how to change the increment value of identity column.

Thanks,

Netaji

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

DBCC CHECKIDENT should be able to help you. Information can be found in BOL or here is one link. I searched Yahoo with "change identity seed SQL":

Good Hunting!

Leo Leong SSCommitted Points: 1615 More actions · Answer 2

you have to put the column's data type after the column name (but before the IDENTITY keyword)

for example,

alter table alter column mycol int IDENTITY(200, 2)

try and see

Leo