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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply