Re: Reseeding identity field
- From: "tshad" <toms@xxxxxxxx>
- Date: Thu, 18 Jun 2009 11:19:49 -0700
What it seems to be is that:
DBCC CHECKIDENT(MyTable) and DBCC CHECKIDENT(MyTable,RESEED) do the same
thing.
I assume that RESEED is the default.
If you the largest number in the identity column is less that the current
IDENTITY nothing will happen. But if the largest number in theidentity
column is greater than the current IDENTITY. The current IDENTITY will
change to whatever the largest number is.
I had thought that if you only did DBCC CHECKIDENT(MyTable) it would tell
you what the current value is. But that is not the case. You would have to
do a NORESEED to prevent the change.
Thanks,
Tom
"tshad" <toms@xxxxxxxx> wrote in message
news:OKklRr27JHA.5932@xxxxxxxxxxxxxxxxxxxxxxx
"John Bell" <jbellnewsposts@xxxxxxxxxxx> wrote in message
news:uhQ%230dz7JHA.728@xxxxxxxxxxxxxxxxxxxxxxx
Good example.
"tshad" <toms@xxxxxxxx> wrote in message
news:%23sOUdLu7JHA.240@xxxxxxxxxxxxxxxxxxxxxxx
I am trying to reseed the identity field in my program.
My largest ID is 205001 and it is using 351223
I want it to go back to 205002 (use the Highest value in the Identity
field which is what the docs say) but it still stays at 351223.
RESEED Changes the current identity value, using the maximum value in
the identity column, if the current identity value is less than the
maximum identity value stored in the identity column.
I tried:
DBCC CHECKIDENT ('Projects,RESEED)
Is there another way to do this?
I could do a max() on the field and add that paramater to the CHECKIDENT
procedure, but I was wondering if there is another way to do this?
Thanks,
Tom
Hi Tom
It is confusing, the exceptions section implies it won't do it as "the
current identity values is larger than the maximum value in the table",
but the described reset method of doing a NORESEED will not give you the
maximum value in the table!
Hopefully this will help? At least it will give you something to play
with!!
USE TEMPDB;
GO
CREATE TABLE T1 ( id int not null identity );
GO
DECLARE @i int = 0;
WHILE @i < 10
BEGIN
INSERT INTO T1 DEFAULT VALUES ;
SET @i+=1;
END
GO
DBCC CHECKIDENT( t1, NORESEED ); -- Current identity value and column
values are the same (10)
GO
DBCC CHECKIDENT ( t1 ); -- Current identity value and column values are
the same (10) -- no change!
GO
DBCC CHECKIDENT( t1 ); -- Current identity value and column values are
the same (10) -- no change!
GO
DECLARE @i int = 0
BEGIN TRANSACTION
WHILE @i < 10
BEGIN
INSERT INTO T1 DEFAULT VALUES ;
SET @i+=1;
END
ROLLBACK TRANSACTION
GO
SELECT MAX(id) FROM T1 ; -- 10
GO
SELECT IDENT_CURRENT('T1'); -- 20
GO
DBCC CHECKIDENT ( t1, NORESEED ); -- Current identity value and column
value are the same (20)
GO
DBCC CHECKIDENT ( t1 ); /* Current identity value and column value are
the same (20) - no change even though the maximum value in the table is
lower */
GO
DBCC CHECKIDENT ( t1, RESEED ); -- Current identity value and column
value are the same (20) - no change!
GO
DBCC CHECKIDENT ( t1, RESEED, -1 ); -- Current identity value 20 column
value set to -1
GO
DBCC CHECKIDENT ( t1, NORESEED ); -- Current identity value -1 column
value set to 10
GO
DBCC CHECKIDENT ( t1 ); -- Current identity value -1 column value set to
10 -- changed
/* need to do this or you will get duplicates */
GO
INSERT INTO T1 DEFAULT VALUES ;
INSERT INTO T1 DEFAULT VALUES ;
INSERT INTO T1 DEFAULT VALUES ;
GO
SELECT MAX(id) FROM T1 ; -- 13
GO
SELECT IDENT_CURRENT('T1'); -- 13
GO
DBCC CHECKIDENT ( t1, NORESEED ); -- Current column value and identity
value are the same (13)
GO
DECLARE @i int = 0
BEGIN TRANSACTION
WHILE @i < 10
BEGIN
INSERT INTO T1 DEFAULT VALUES ;
SET @i+=1;
END
ROLLBACK TRANSACTION
GO
SELECT MAX(id) FROM T1 ; -- 13
GO
SELECT IDENT_CURRENT('T1'); -- 23
GO
DBCC CHECKIDENT ( t1, NORESEED ); -- Current identity value and column
value are the same (23)
GO
DBCC CHECKIDENT ( t1 ); /* Current identity value and column value are
the same (23) - no change even though the maximum value in the table is
lower */
GO
DECLARE @seed int = (SELECT MAX(id) FROM T1);
DBCC CHECKIDENT ( t1, RESEED, @seed ); -- Current identity value 23
column value set to 13
GO
DBCC CHECKIDENT ( t1, NORESEED ); /* Current identity value and column
value are the same (13)*/
GO
INSERT INTO T1 DEFAULT VALUES ;
INSERT INTO T1 DEFAULT VALUES ;
INSERT INTO T1 DEFAULT VALUES ;
GO
SELECT MAX(id) FROM T1 ; -- 16
GO
SELECT IDENT_CURRENT('T1'); -- 16
GO
DBCC CHECKIDENT ( t1, NORESEED ); -- Current column value and identity
value are the same (16)
GO
DROP TABLE t1;
GO
I realized what the problem was but was trying to solve it in with just
the CHECKIDENT statement.
What I finally did was:
SELECT @MaxSeed = MAX(Project_id) FROM Projects
DBCC CHECKIDENT ('Projects,RESEED,@MaxSeed)
That worked fine.
Thanks,
Remember to start your identity ranges as low as possible to use the
maximum range
John
.
- Follow-Ups:
- Re: Reseeding identity field
- From: John Bell
- Re: Reseeding identity field
- References:
- Reseeding identity field
- From: tshad
- Re: Reseeding identity field
- From: John Bell
- Re: Reseeding identity field
- From: tshad
- Reseeding identity field
- Prev by Date: Re: SQL 2008 not starting
- Next by Date: Re: HELP, RAN OUT OF DISK SPACE
- Previous by thread: Re: Reseeding identity field
- Next by thread: Re: Reseeding identity field
- Index(es):
Relevant Pages
|
