Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gnobber
Starting Member
11 Posts |
 Posted - 2008-05-09 : 10:49:19
|
| Hi,I've just recently learned that being an identity seed-column doesn't guarantee that you will always get unique values. It can double up and cause a violation of PK. If so, is there a work around this that doesn't involve a REINDEX? Cause if im home and my client suddenly experiences this in the middle of a busy day, that would be a total disaster. Any ideas on how i can avoid this or a workaround maybe? Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-09 : 18:05:33
|
There is no way the IDENTITY value can give same value again.Unless you tamper with it using DBCC.Maybe you users had @@IDENTITY and not SCOPE_IDENTITY() function?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
gnobber
Starting Member
11 Posts |
Posted - 2008-05-09 : 20:58:56
|
| I thought so too, but my database got corrupted and used DBCC CHECKDB to fix the problem. After that, when I added a new record on a table with a PK of bigint identity seed (1, 1), a constraint violation occurs. I used DBCC CHECKIDENT to know what value is being inserted on the PK, and yes, it produces a value that is already existing. I used DBCC CHECKIDENT again with RESEED option to set the identity seed starting value correctly and it worked again. However, what im afraid is if this happens to my client and im not around. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-10 : 01:38:00
|
Make a new menu item under "Maintenance" menu.Write a sproc like thisDECLARE @MaxID INTSELECT @MaxID = MAX(ID) FROM Table1SET @MaxID = COALESCE(@MaxID + 1, 1)SET @SQL = 'DBCC CHECKIDENT ...' + CAST(@MaxID AS VARCHAR(12))EXEC (@SQL)And then client can fix this problem if it occurs again.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
How to avoid repeating identity seed