Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fixing Your Identity Expand / Collapse
Author
Message
Posted Friday, November 2, 2001 12:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:35 AM
Points: 33,102, Visits: 15,213
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/identityreset.asp






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1493
Posted Sunday, December 30, 2001 7:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
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"
Post #23285
Posted Monday, December 31, 2001 10:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:35 AM
Points: 33,102, Visits: 15,213
Nice script. Did you post it? I usually am fixing one-off things, but this would be handy for migrating data.

Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #23286
Posted Monday, December 31, 2001 4:27 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:35 PM
Points: 6,779, Visits: 1,868
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


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #23287
Posted Tuesday, January 1, 2002 2:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
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"
Post #23288
Posted Thursday, January 10, 2002 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 2:48 PM
Points: 1, Visits: 3
If the Identity column is reset what happens to all relationships do they loss there related records?




Post #23289
Posted Thursday, January 10, 2002 3:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:35 AM
Points: 33,102, Visits: 15,213
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
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #23290
Posted Wednesday, March 19, 2003 5:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 7, 2010 8:28 PM
Points: 18, Visits: 4
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.




Post #23291
Posted Wednesday, March 19, 2003 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:35 AM
Points: 33,102, Visits: 15,213
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #23292
Posted Tuesday, January 30, 2007 5:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 5, 2007 6:14 AM
Points: 10, Visits: 1

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!

 

Post #341100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse