Anatoly Lubarsky Logo
programming, design, integration, games, music

MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED

Suppose you need to delete all data from the table and reset IDENTITY count. We have 2 options. One goes for TRUNCATE TABLE that resets IDENTITY (altogether it wipes out all table data). The second goes to DBCC CHECKIDENT RESEED.


It is good that DBCC CHECKIDENT RESEED feature exists, but I use TRUNCATE TABLE. DBCC CHECKIDENT RESEED has one disadvantage: if there were rows in the table before deletion, it will give 1 for IDENTITY field after the next insert, else - 0, therefore it makes it counter-productive to use this construction in the build scripts.


Related Posts:

Saturday, December 13, 2003 4:13 AM

Comments

# re: MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED
I have read on another post that you can call DBCC CHECIDENT like this:

DBCC CHECIDENT ('table', RESEED, 0)
DBCC CHECIDENT ('table', RESEED)

And that this solves the problem.

11/10/2006 2:48 PM by Christo

# re: MSSQL Reset Identity: TRUNCATE TABLE
Also, a lot of tables have foreign keys. In which case TRUNCATE TABLE results in the error message:

"Cannot truncate table 'TableWithForeignKey' because it is being referenced by a FOREIGN KEY constraint."

2/27/2007 2:45 PM by Sparkplug

# re: MSSQL Reset Identity: TRUNCATE TABLE
DBCC CHECIDENT ('table', RESEED, -1)

5/3/2007 8:35 AM by Carel

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs