29 July 2009 ~ View Comments

Identity column starts from zero instead of one

Code

I was experiencing a strange problem. Identity field of a table is set to [1,1] by generating script. Every time, when I drop the whole database, and then re-create it by running script in SQL Server 2005 Management Studio, and then insert a record into that table programmatically, SQL Server set identity field value of that first record to 0 instead of 1!

I was gone mad until I have comeup with a small piece of code like this. The code will delete all the rows from all the tables in your database. So use it cautiously.


CREATE PROCEDURE [dbo].[ReSeed] AS
BEGIN
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
BEGIN TRY
TRUNCATE TABLE ?
DBCC CHECKIDENT (''?'', RESEED, 1)
END TRY
BEGIN CATCH
DELETE FROM ?
DBCC CHECKIDENT (''?'', RESEED, 0)
END CATCH
'
END

Enjoy!

Update me when site is updated

sidekick it

Tags: ,
  • http://pro-thoughts.blogspot.com/ Vladimir Kelman

    Thank you, Narendra!

    Could you explain in a few words how it works?

  • http://pro-thoughts.blogspot.com/ Vladimir Kelman
  • http://www.scriptstar.co.uk Narendra

    Hi VK,

    First of all, I am not sure why SQL server is starting some tables with identity column 0 and some tables with 1 after using reseed.

    Actually I wrote a procedure and it will start by checking whether the table in the database is having any identity column set or not.

    If the table has an identity column then it will try to truncate the table data and set the reseed to 1. If you don’t use truncate then table identity column is starting from 0.

    But you can’t use truncate for all the tables, some tables may throw an exception (you can use delete statement for these tables) so I wrapped the above statement with TRY block.

    If it throws an error then I can catch that and use delete statement and reseed to 0.

    Hope this explanation is helpful.

    May not be logical but works fine for me. Have you tried and tested my solution?

    Cheers,
    Scriptstar

  • http://sharepointsidekick.com/Identity-column-starts-from-zero-instead-of-one SharePoint Sidekick

    Identity column starts from zero instead of one…

    Thank you for submitting this cool story – Trackback from SharePoint Sidekick…

blog comments powered by Disqus