Using IDENTITY FIELD in Alter Table Query
-
Hi all, Please help me..
Is it possible to change the Seed or Increment of an Identity field using Alter Table Query.
eg.
Employee_No int identity(1,1)
to
Employee_No int identity(2,1)
or
Employee_No int identity(1,2)
(table is empty)
Thanks in advance
Kiran
-
You can reseed using the DBCC CHECKIDENT command. For instance:
DBCC CHECKIDENT('Employee', RESEED, 2)Off the top of my head I don't know of a way of changing the increment easily. I'll keep looking or perhaps another reader will post the answer!
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
BOL for IdentityIncrement Property (DMO) says it is read only for existing columns. Seems like it should be possible to change it - what would it hurt?
Andy
-
Try
ALTER TABLE tblName ALTER COLUMN Employee_No int identity(1,2) NOT NULL
BUt it will only effect data entered after that point the way expected. Also if you set the seed value to something that already exists it will be ignored if the value already exists.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thank you for your reply,
DBCC CHECKIDENT (“Employee”,RESEED, 2) worked ok.
When I tried to execute ALTER TABLE Employee ALTER COLUMN Employee_id int IDENTITY(2,2) generated an error..
“Incorrect syntax near the key word ‘Identity’”
Is the syntax you gave is correct?
Thanks
Kiran
-
The ALTER COLUMN has a different syntax than ADD COLUMN. It is possible to add a new column which will have the IDENTITY property (provided there isn't already an IDENTITY column on the table), but I don't believe you can ALTER COLUMN to add, drop, or change an IDENTITY property. Here is the syntax on ALTER COLUMN from Books Online:
quote:
ALTER TABLE table{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
] }
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply