We help IT Professionals succeed at work.

Reset Identity column

REA_ANDREW
REA_ANDREW asked
on
3,462 Views
Last Modified: 2013-11-05
Hi,

Is there anyway I can reset the seed of the identity column without deleting the contents of the table?

Cheers

Andrew
Comment
Watch Question

dbcc checkident(tablename, reseed, id#)

i.e.

dbcc checkident(temptable, reseed, 101)

Commented:
try this

DBCC CHECKIDENT('myTable', RESEED, 0)
Doug BishopDatabase Developer

Commented:
DBCC CHECKIDENT ('dbo.myTable', RESEED, 1000)
Next insert will have an IDENTITY value of 1000

Author

Commented:
I have reset but they have not changed the current IDs. What should happen?

Author

Commented:
Do I need to delete all after I have done this or put the current details in a staging table reset ID and insert them back in from staging table?
Doug BishopDatabase Developer

Commented:
Be sure to use a value that does not already exist in the table or cover a range that already exists, otherwise SQL Server will try to do an insert and you could get a dup error, especially if there is a unique constraint or PK constraint on the column.

In other words, if you have rows with IDENTITY column values of 1-55, 68, 75 and 102 and you reseed at 100, SQL Server will attempt to use a value of 102 for your third insert. It won't check to see if that value aleardy exists.
Database Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
You can try this

Declare @max int
Select @max = ISNULL(Max(ID)+1,1) From myTable
DBCC CHECKIDENT('myTable', RESEED, @max)

Commented:
Or simply this

DBCC CHECKIDENT ('myTable', RESEED)

http://msdn2.microsoft.com/en-us/library/ms176057.aspx
"I have reset but they have not changed the current IDs. What should happen?"

Doing a reseed simply resets the counter going forward - it does not affect any existing records. Use hongjun's script to ensure that you will start from a proper #. If you need to adjust the #s, use dbbishop's advice.

Author

Commented:
dbbishop

I will go with your technique. Fortunately this is a kinda of import table so there is no FK assigned so the ID really is there so we can edit records or delete them.

I am trying to do a SELECT INTO now. and back.

Cheers

Will update this thread in a minute.

Andrew

Author

Commented:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tbl_Cars_TempAccountHold' in the database.

I get this error with this statement

SELECT * INTO tbl_Cars_TempAccountHold FROM tbl_Cars WHERE AccountID <>0 AND AccountID is not null

Commented:
This means there's already a table with the name 'tbl_Cars_TempAccountHold' in the database. You will have to decide should you drop or keep the table.
If you need to keep, then perhaps you can do a select into another table with a different name.

hongjun

Commented:
You may need below should you need to insert ID explicitly.

SET IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a table
http://msdn2.microsoft.com/en-us/library/aa259221(SQL.80).aspx

Cheers
hongjun

Author

Commented:
How can I simply just insert the table data, as what I need to do is this

a) Move records into a temporary table
b)TRUNCATE the original table thus setting the SEED back to 0
c) INSERT the records back in from temp table so there ID's go from 1 to the number of records.

Can anyone advice on how I do this?

Commented:
You can try this

-- Move to a temp table
Select * Into #temp From YourTable
go

-- TRUNCATE the original table thus setting the SEED back to 0
Delete From YourTable
go
DBCC CHECKIDENT ('YourTable', RESEED, 1)
go

-- Insert back
-- But I exclude out the Identity field in the column list
Insert Into YourTable (Field1, field2, field3) Select field1, field2, field3 From #temp


hongjun
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I have just been doing the math on this and I think I am panicking a bit too much.

the field can hold a maximum value of 9223372036854775807

I divided this by 1,000,000 as I said if we do 1 million perday

Then I divided the result by 365 to get years

So 25269512429 years left until exhaustion

That is what I got.

Author

Commented:
hongjun cheers for the code I will use that anyway, regardless of the years left lol

Cheers

Commented:
oh ok.. So there's no problem?

hongjun
Doug BishopDatabase Developer

Commented:
I would change the code from
-- TRUNCATE the original table thus setting the SEED back to 0
Delete From YourTable
go
DBCC CHECKIDENT ('YourTable', RESEED, 1)
go

TO:

TRUNCATE TABLE YourTable
GO

Also, if the identity seed and increment are already (1,1) in the table, there is no need for the DBCC CHECKIDENT() as TRUNCATE automatically does a reseed. TRUNCATE can be a LOT faster than DELETE and uses very little log space.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.