Primary Key Field - Identity

Question

Post reply

Primary Key Field - Identity

Cory Slaney

SSCrazy

Points: 2459
More actions
May 19, 2005 at 8:31 am

#66965

I would like to create a table that stores a unique id value and several other bits of information and that everytime the tables gets truncated, it does not reuse one of the former id values, but rather moves on to the next. Can this be accomplished with the id field be set a a primary key identity field? Or if the table, is truncated, will the id start back at the same identity seed value as specified in the table properties. Any help would be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Mark-163717 SSC Enthusiast Points: 188 More actions · Answer 1

You could create a stored procedure that would:

1. get the last identity for the table

2. drop the table

3. create the table with the new identity seed value set to the last identity you retrieved in step 1

I am not sure why you want to truncate the data....I am sure you have your reasons. Any one else with ideas?

padmakumar_pga Right there with Babe Points: 766 More actions · Answer 2

Hi

TRUNCATE TABLE will reset identity seed . But delete will preserve the identity seed . if it is a small table you can Use delete . Else Procedure option you can use .

Padmakumar

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

Mike Metcalf

SSCrazy Eights

Points: 8763

May 19, 2005 at 9:52 am

#559898

First star wars quote found I did

ron k SSCertifiable Points: 6103 More actions · Answer 4

You might want to look in books online for SET IDENTITY_INSERT.

This looks like it may allow you to "trick" the system by saving the last identity value just before the truncate and then inserting a row with the saved value and deleting the same row. Subsequent inserts will be an increment of the value you just inserted/deleted.

Edit: I didn't see Mark's post. That looks like a good solution but be aware your stored procedure will need to keep up with the most current definition of your table.

PhilPacha Hall of Fame Points: 3645 More actions · Answer 5

PhilPacha

Hall of Fame

Points: 3645

May 19, 2005 at 1:17 pm

#560016

Look at "DBCC CHECKIDENT" in BOL.

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 6

This works but you must insert a dummy row or insert at least one row of new data.

HTH MIke

USE TEST

DECLARE @NewSeed int

SELECT @NewSeed = max(employeeid) FROM Employees

TRUNCATE TABLE Employees

SET @NewSeed = @NewSeed + 1

SET IDENTITY_INSERT Employees ON

--insert a dummy record with the next IDENTITY VALUE

INSERT INTO Employees(EmployeeID,FirstName) VALUES(@NewSeed,'Dummy')

/* The new row or dummy row must be set before you set identity_insert tablename to off*/

SET IDENTITY_INSERT Employees OFF

INSERT INTO Employees(FirstName)Values('Dummy2')

ron k SSCertifiable Points: 6103 More actions · Answer 7

After looking at this suggestion from PhilPacha it apprears to be the best solution. So check out DBCC CHECKIDENT.

Cory Slaney SSCrazy Points: 2459 More actions · Answer 8

Thanks all. I used DBCC CHECKIDENT, as suggested by PhilPacha, to look for the last identity value used and then incremented that value. Your feedback was greatly appreciated!