June 18, 2003 at 9:13 am
Hi all,
I am having one primary column empid (Numeric) in my table.
for that column i set the identity yes option.
Here my qtn is whenever i inserted into table,the empid value is incresing one by one.What i am facing the problem is ,lets say ,i have added 1000 rows into the table.immediately i have deleted all.
When i add again, the value is starting with 1001,1002...
Is there any option where i can reset the value to 1,2...?
Hope u would have understand!.
Pls help me on this!.
Thanks in Advance!
-Ramesh.
June 18, 2003 at 10:02 am
check for DBCC CHECKIDENT in BOL.
eg:
DBCC CHECKIDENT (Employee, RESEED, 1)
June 18, 2003 at 11:35 am
DBCC CHECKIDENT will create a duplicate Identity values
Ex. Id_no, Name
1 Test1
2 Test2
3 Test3
4 Test4
If you delete rows with Id_no 1 and 2 and
Issue a DBCC CHECKIDENT(Table_Name, RESEED, 1)
Next time when you insert 3 rows your table would look like
Id_no, Name
1 Test_New1
2 Test_New2
3 Test_New3
3 Test3
4 Test4
It does not skip existing Identity values
You can write a script to identify missing Identity values and
use these values during your data insertion, but this is not practical
The best solution is to re-build the identity column
by dropping and re-creating it after data insertion
MW
Edited by - mworku on 06/18/2003 3:13:17 PM
MW
June 18, 2003 at 9:00 pm
he deleted all the rows, so i dont see any problem with reseeding it to 1.
June 19, 2003 at 6:27 am
You can use TRUNCATE <tablename> to delete all records.
The identity column value is reset to 1 for the next insertion.
Claude
June 19, 2003 at 2:36 pm
Actually, it's Truncate TABLE <Table Name>, and that's definitely the way to do it. If you have any foreign keys that reference that table you'll have to drop them first.
cheers.
cl
Signature is NULL
June 19, 2003 at 2:38 pm
BEGIN TRAN
DBCC CHECKIDENT('Table1',RESEED,1)
DBCC CHECKIDENT('Table1',RESEED)
COMMIT TRAN
This will reset to the next non-duplicate value. The RESEED option will only reset the next-in-sequence upward, so set to 1, then let the RESEED do it's work.
-Dan
-Dan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply