Fixing Your Identity

  • Comments posted to this topic are about the content posted at

  • Hi there

    I use this before running my data migration scripts for the bazillionth time...

    SELECT 'DBCC CHECKIDENT (' + o.[name] + ', RESEED, 1)'

    FROM syscolumns c

    INNER JOIN sysobjects o ON o.[Id] = c.[Id]

    INNER JOIN sysusers u ON o.[uid] = u.[uid]

    WHERE c.[status] & 128 = 128

    AND u.[name] = 'dbo'

    AND o.[name] <> 'dtproperties'



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Nice script. Did you post it? I usually am fixing one-off things, but this would be handy for migrating data.

    Steve Jones

  • DMO has two methods, CheckIdentityValue and CheckIdentityValues that deal with this. The first works on a table, the second checks ALL tables in a db - equivalent to the script Chris has - the caveat being that it doesnt accept any parameters so you get the default behavior of DBCC CheckIdent which is to only fix it if there is a problem. Think I'll drop a note to MS to see if they can fix this!


  • Hi Steve

    Yep, just submitted it. Funny enough, a lot of SS developers seem to think that identities for EVERY table is a great solution because it means less coding and simplification when traversing joins. Coming from a 3nf+ world, at times I find it hard to justify it, especially when the natural key remains in place and busines logic is still required to maintain it anyhow. Just something ive noticed over the years and still battle with today.



    Chris Kempster
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • If the Identity column is reset what happens to all relationships do they loss there related records?

  • An identity column has no "relationships" by default. If you want an identity to be the PK or an FK, you set that separately. If you reset the identity value, you could cause a pk violation if it is the pk.

    Steve Jones

  • the problem is we need db_owner or sysadmin to execute 'DBCC CHECKIDENT '.

    what about if user is a public role?

    my problem is i have a store porcedure with 'DBCC CHECKIDENT '.

    But a public role user cann't run it.

  • There are some utilities, like checkident and set identity_insert, that are not designed to allow anyone to use them. The reason is likely that often the developer's have not thought threw the impact of having people run them

    Why do you need public to run this. I'd argue this is a bad idea. It should be controlled and run by someone with admin type privldges.

    On a side note, granting rights to public is a bad idea.

    Steve Jones

  • I just finished forward engineering my database. This works like a charm when deleting and inserting records for testing. I am creating a business process that does allow for manual and automatic record inserts updates and deletes. The automatic data manipulation occurs in SSIS. The manual data manipulation comes from the user via .NET application. It appears that this would work really well for both processes in order to keep the identities in squential order. Is there a significant downside in using it in a .NET application where users have the ability to delete and insert records ad infinitum? Sorry for being Vague; however, there currently aren’t a specified number of times that the user has before they no longer have access to the table.





Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply