SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Fixing Your Identity / Latest Posts

RE: Fixing Your Identity

Lee Davis-285380 — Tue, 30 Jan 2007 17:41:00 GMT

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.

Thanks!

RE: Fixing Your Identity

Steve Jones - SSC Editor — Wed, 19 Mar 2003 20:44:00 GMT

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 themWhy 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 Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjoneswww.dkranch.net

RE: Fixing Your Identity

yihua833 — Wed, 19 Mar 2003 17:56:00 GMT

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.

RE: Fixing Your Identity

Steve Jones - SSC Editor — Thu, 10 Jan 2002 15:33:00 GMT

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 Jonessteve@dkranch.net

RE: Fixing Your Identity

Lazer — Thu, 10 Jan 2002 15:07:00 GMT

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

RE: Fixing Your Identity

ckempste — Tue, 01 Jan 2002 02:42:00 GMT

Hi SteveYep, 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.CheersCk.

RE: Fixing Your Identity

Andy Warren — Mon, 31 Dec 2001 16:27:00 GMT

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!Andy

RE: Fixing Your Identity

Steve Jones - SSC Editor — Mon, 31 Dec 2001 10:56:00 GMT

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

RE: Fixing Your Identity

ckempste — Sun, 30 Dec 2001 19:53:00 GMT

Hi thereI use this before running my data migration scripts for the bazillionth time...SELECT 'DBCC CHECKIDENT (' + o.[name] + ', RESEED, 1)'FROM syscolumns cINNER JOIN sysobjects o ON o.[Id] = c.[Id]INNER JOIN sysusers u ON o.[uid] = u.[uid]WHERE c.[status] & 128 = 128AND u.[name] = 'dbo'AND o.[name] <> 'dtproperties'CheersChris

Fixing Your Identity

Steve Jones - SSC Editor — Fri, 02 Nov 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/identityreset.asp