September 22, 2009 at 10:44 am
[sorry admins, couldn't a 2008 thread to post this in]
In moving my ETL processes from 2005 to 2008 I discovered the truncate statement no long performs the undocumented feature of reseting the identity column.
I now have to use this additional statement.
DBCC CHECKIDENT('name-of-table', RESEED, 0)
Might be old news but I just thought I'd put it up here for the next person googling this one.
If there are better ways to do please do tell.
thanks gang
Skål - jh
September 22, 2009 at 10:52 am
Per BOL -
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
So - if the table was defined with a seed other than 0, then that is what it will reset to.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 22, 2009 at 11:44 am
thank you.
on the table seeding was set to 1 seed, and 1 increment,
then 0 seed, and 1 increment.
Then identity insert is toggled on then off to insert a zero ID row.
In both cases, the rest of the inserts after truncation resulted in Identity of 2.
Only with that DBCC command could I get the IDs to start at 1 as expected.
Skål - jh
September 22, 2009 at 7:37 pm
I've just run a test against a Sql Server 2008 instance and it seems to reset the Identity column just fine when you Truncate the table.
September 22, 2009 at 7:42 pm
That has been my experience as well.. I just tested it.. It resets to the seed..
CEWII
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy