January 21, 2013 at 11:52 pm
hi
i have table which as identity column which as primary key it is related to lot of tables
how do i reset the identity order
thanks
with best regards
pradeep
January 22, 2013 at 12:14 am
If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.
It would be wise to first check what the current identify value is. We can use this command to do so:
DBCC CHECKIDENT (‘tablename’, NORESEED)
For instance, if I wanted to check the next ID value of my orders table, I could use this command:
DBCC CHECKIDENT (orders, NORESEED)
To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)
Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)
DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)
January 22, 2013 at 12:17 am
mpradeep23 (1/21/2013)
i have table which as identity column which as primary key it is related to lot of tables .how do i reset the identity order
IF you are saying here that this table is related with other tables too (FK constaint ..might be logically ) so by resetting the identity you are going to put your self in BIG trouble , you all references will get messed up .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 22, 2013 at 2:10 am
hi
Tahnks for update i worked
January 22, 2013 at 4:41 pm
jeetsingh.cs (1/22/2013)
To set the value of the next ID to be 1000, I can use this command:DBCC CHECKIDENT (orders, RESEED, 999)
Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.
It's good info you presented bu there is just one footnote. It is likely that the original poster has data in their table, but what you said is true only when there is data in the table. If the table is empty then the next value would actually be 999 and not 1000, i.e. no +1 when the table is empty, which is a quirk in how IDENTITY columns work.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply