Re: Reseeding identity field

Hi Tom

Yes, and setting the current value to -1 in my previous script created the situation where the current identity value was lower than the maximum value in the column. The current identity value will increase when you insert into the identity column with IDENTITY_INSERT ON, so the situations where you aren't going to need a seed value is if it was deliberately set low (on purpose or not!) or possibly after corruption.

John

"tshad" <toms@xxxxxxxx> wrote in message news:e$s6eFE8JHA.4116@xxxxxxxxxxxxxxxxxxxxxxx
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

"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

Good example.

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





.

Relevant Pages