|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Reseeding identity field
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 Maybe there is an open transaction preventing it? DBCC OPENTRAN;
Anyway, the identity value is a meaningless identifier. I hope you are not attempting to produce a contiguous row number with no gaps... On 6/16/09 8:30 PM, in article #sOUdLu7JHA.***@TK2MSFTNGP03.phx.gbl, "tshad" <t***@pdsa.com> wrote: Show quoteHide quote > 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 > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message No.news:C65DC507.1D2A1%ten.xoc@dnartreb.noraa... > Maybe there is an open transaction preventing it? DBCC OPENTRAN; > > Anyway, the identity value is a meaningless identifier. I hope you are > not > attempting to produce a contiguous row number with no gaps... > > I was actually trying to prevent the gaps. I was getting a number 100,000 greater than highest number in the table. Not sure how it got that way. This is a development database and we are moving real data to it all the time for testing. So I assume that at one time the number was higher and we had deleted a bunch of rows. Thanks, Tom Show quoteHide quote > > > On 6/16/09 8:30 PM, in article #sOUdLu7JHA.***@TK2MSFTNGP03.phx.gbl, > "tshad" > <t***@pdsa.com> wrote: > >> 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 >> >> > > I was actually trying to prevent the gaps. I was getting a number 100,000 But again, who cares about the gap? It's a meaningless number.> greater than highest number in the table. Not sure how it got that way. > This is a development database and we are moving real data to it all the Then maybe it shouldn't be an IDENTITY column in the development database?> time for testing. > So I assume that at one time the number was higher and we This can also happen if the procedure you are using for INSERT fails and> had deleted a bunch of rows. rolls back the transaction. The IDENTITY value gets "used" but unassigned. If this happened in a loop or was broken for some time then it could be easy to have 100,000 slots used up. "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message I agree, but the pm wants it to line up.news:C65E96A5.1D425%ten.xoc@dnartreb.noraa... >> I was actually trying to prevent the gaps. I was getting a number >> 100,000 >> greater than highest number in the table. Not sure how it got that way. > > But again, who cares about the gap? It's a meaningless number. > Clients database and that is the way they have it.>> This is a development database and we are moving real data to it all the >> time for testing. > > Then maybe it shouldn't be an IDENTITY column in the development database? > Thanks,>> So I assume that at one time the number was higher and we >> had deleted a bunch of rows. > > This can also happen if the procedure you are using for INSERT fails and > rolls back the transaction. The IDENTITY value gets "used" but > unassigned. > If this happened in a loop or was broken for some time then it could be > easy > to have 100,000 slots used up. > True. Tom Also, since the current identity value (351223) is NOT less than the maximum
identity value stored in the identity column (205001), then RESEED is not going to change the seed. Read the sentence starting with RESEED again to see what I mean. You need to explicitly add the third parameter. A On 6/16/09 8:30 PM, in article #sOUdLu7JHA.***@TK2MSFTNGP03.phx.gbl, "tshad" <t***@pdsa.com> wrote: Show quoteHide quote > 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 > > Which is what I did.
I just did a MAX on the field and used that as my 3rd parameter. Thanks, Tom Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:C65DD184.1D2AD%ten.xoc@dnartreb.noraa... > Also, since the current identity value (351223) is NOT less than the > maximum > identity value stored in the identity column (205001), then RESEED is not > going to change the seed. Read the sentence starting with RESEED again to > see what I mean. You need to explicitly add the third parameter. > > A > > > > On 6/16/09 8:30 PM, in article #sOUdLu7JHA.***@TK2MSFTNGP03.phx.gbl, > "tshad" > <t***@pdsa.com> wrote: > >> 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 >> >> >
Show quote
Hide quote
"tshad" <t***@pdsa.com> wrote in message Hi Tomnews:%23sOUdLu7JHA.240@TK2MSFTNGP03.phx.gbl... >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 > 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 Remember to start your identity ranges as low as possible to use the maximum range John
Show quote
Hide quote
"John Bell" <jbellnewspo***@hotmail.com> wrote in message I realized what the problem was but was trying to solve it in with just the news:uhQ%230dz7JHA.728@TK2MSFTNGP05.phx.gbl... > > "tshad" <t***@pdsa.com> wrote in message > news:%23sOUdLu7JHA.240@TK2MSFTNGP03.phx.gbl... >>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. CHECKIDENT statement. What I finally did was: SELECT @MaxSeed = MAX(Project_id) FROM Projects DBCC CHECKIDENT ('Projects,RESEED,@MaxSeed) That worked fine. Thanks, Show quoteHide quote > > Remember to start your identity ranges as low as possible to use the > maximum range > John 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 Show quoteHide quote "tshad" <t***@pdsa.com> wrote in message news:OKklRr27JHA.5932@TK2MSFTNGP03.phx.gbl... > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:uhQ%230dz7JHA.728@TK2MSFTNGP05.phx.gbl... >> >> "tshad" <t***@pdsa.com> wrote in message >> news:%23sOUdLu7JHA.240@TK2MSFTNGP03.phx.gbl... >>>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 > > 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 Show quoteHide quote "tshad" <t***@pdsa.com> wrote in message news:e$s6eFE8JHA.4116@TK2MSFTNGP04.phx.gbl... > 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" <t***@pdsa.com> wrote in message > news:OKklRr27JHA.5932@TK2MSFTNGP03.phx.gbl... >> >> "John Bell" <jbellnewspo***@hotmail.com> wrote in message >> news:uhQ%230dz7JHA.728@TK2MSFTNGP05.phx.gbl... >>> >>> "tshad" <t***@pdsa.com> wrote in message >>> news:%23sOUdLu7JHA.240@TK2MSFTNGP03.phx.gbl... >>>>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 >> >> > >  
Other interesting topics
Change Column Position
SQL Server 2008 / 2005 / 2000 / 7 / 6.5 largest possible database? error logging on SQL 2008 Disk full from delete How to BKP? Schedule windows server and sql server reboot weekly Conversion from nvarchar to varchar Determine actual row size? What happens to running trace when SQL is shutdown can't access sql datsbase |
|||||||||||||||||||||||
