December 18, 2003 at 6:06 am
According to BOL under DBCC CHECKIDENT:
"If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table."
But my testing:
set nocount on
CREATE TABLE [dbo].[RobTable] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[RobNote] [varchar] (12) NULL,
[DateTimeInserted] datetime DEFAULT getdate()
) ON [PRIMARY]
go
insert RobTable (RobNote) values ('Note 1')
insert RobTable (RobNote) values ('Note 2')
insert RobTable (RobNote) values ('Note 3')
select * from RobTable order by DateTimeInserted, RecordID
DBCC CHECKIDENT (RobTable,RESEED,0) -- set the identity column to begin from 1 again
insert RobTable (RobNote) values ('Note 4')
select * from RobTable order by DateTimeInserted, RecordID
drop table RobTable
set nocount off
gives duplicate Identity values:
RecordID RobNote DateTimeInserted
----------- ------------ ------------------------------------------------------
1 Note 1 2003-12-18 12:38:32.890
2 Note 2 2003-12-18 12:38:32.903
3 Note 3 2003-12-18 12:38:32.903
Checking identity information: current identity value '3', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
RecordID RobNote DateTimeInserted
----------- ------------ ------------------------------------------------------
1 Note 1 2003-12-18 12:38:32.890
2 Note 2 2003-12-18 12:38:32.903
3 Note 3 2003-12-18 12:38:32.903
1 Note 4 2003-12-18 12:38:32.983
I have got round it by reseeding to 'max+1'.
Has anyone come across this before? I was expecting identity to be a unique value.
Rob
December 18, 2003 at 6:14 am
DBCC CHECKIDENT (RobTable) gives you the expected results.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 18, 2003 at 7:21 pm
Hmm... My BOL doesn't just state that about error 2627. What it also states is:
Invalid identity information can cause error message 2627 when a primary key or unique key constraint exists on the identity column.
I have used the DBCC CHECKIDENT with no problems in the past. I do however have a unique index on the field as I use it as an alternate key. Also note that I ALWAYS do Max + 1!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Edited by - gljjr on 12/18/2003 7:23:32 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 19, 2003 at 12:46 am
Just for fun I ran
set nocount on
CREATE TABLE [dbo].[RobTable] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[RobNote] [varchar] (12) NULL,
[DateTimeInserted] datetime DEFAULT getdate()
) ON [PRIMARY]
go
insert RobTable (RobNote) values ('Note 1')
insert RobTable (RobNote) values ('Note 2')
insert RobTable (RobNote) values ('Note 3')
select * from RobTable order by DateTimeInserted, RecordID
DBCC CHECKIDENT (RobTable,RESEED,0) -- set the identity column to begin from 1 again
insert RobTable (RobNote) values ('Note 4')
insert RobTable (RobNote) values ('Note 5')
insert RobTable (RobNote) values ('Note 6')
insert RobTable (RobNote) values ('Note 7')
select * from RobTable order by DateTimeInserted, RecordID
drop table RobTable
set nocount off
which returns
RecordID RobNote DateTimeInserted
----------- ------------ -----------------------
1 Note 1 2003-12-19 08:39:10.720
2 Note 2 2003-12-19 08:39:10.720
3 Note 3 2003-12-19 08:39:10.720
RecordID RobNote DateTimeInserted
----------- ------------ ------------------------
1 Note 1 2003-12-19 08:39:10.720
2 Note 2 2003-12-19 08:39:10.720
3 Note 3 2003-12-19 08:39:10.720
1 Note 4 2003-12-19 08:39:10.740
2 Note 5 2003-12-19 08:39:10.740
3 Note 6 2003-12-19 08:39:10.740
4 Note 7 2003-12-19 08:39:10.740
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply