November 2, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/identityreset.asp
December 30, 2001 at 7:53 pm
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'
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 31, 2001 at 10:56 am
Nice script. Did you post it? I usually am fixing one-off things, but this would be handy for migrating data.
Steve Jones
December 31, 2001 at 4:27 pm
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
January 1, 2002 at 2:42 am
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.
Cheers
Ck.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 10, 2002 at 3:07 pm
If the Identity column is reset what happens to all relationships do they loss there related records?
January 10, 2002 at 3:33 pm
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
March 19, 2003 at 5:56 pm
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.
March 19, 2003 at 8:44 pm
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
January 30, 2007 at 5:41 pm
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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply