Djabarov, Robert
Robert.Djabarov at usaa.com
Wed Mar 19 14:48:46 CST 2003
I'd have to take back a part of my previous statement about not being able to assign a new SEED number for an identity field. As Billy Pang correctly pointed out, - DBCC CHECKIDENT(...) allows you to reseed the value for the SEED parameter. Thanks Billy. However, INCREMENT parameter cannot be changed without recreating the table. Robert Djabarov Senior SQL Server DBA USAA IT/DBMS ? (210) 913-3148 - phone ? (210) 753-3148 - pager -----Original Message----- From: Djabarov, Robert Sent: Wednesday, March 19, 2003 11:12 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]basic question As far as I know, SET operations require a user to belong at a minimum to db_ddladmin database role, even when it is within the stored procedure that the user is allowed to execute. And also, I don't believe you can alter SEED and INCREMENT properties of an identity field without re-creating the table or removing IDENTITY all together and re-adding it with new values. Robert Djabarov Senior SQL Server DBA USAA IT/DBMS ? (210) 913-3148 - phone ? (210) 753-3148 - pager -----Original Message----- From: John Frederick [mailto:j.frederick at att.net] Sent: Wednesday, March 19, 2003 8:10 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]basic question I'm don't know what automatic behaviors apply to in identity field, but in SQL Server, you have more control. In code, you can use "SET IDENTITY_INSERT ON" to completely turn off the constraints, enabling you to insert records with unused values corresponding to deleted records. I haven't tried it, but even without this, you might be able to plug new values into the IDENTITY SEED AND IDENTITY INCREMENT properties of the field as long as you didn't create values that violated constraints. -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Antony, Fredrick Sent: Tuesday, March 18, 2003 11:49 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]basic question No Identity is meant for identical value. There is no way of resetting it Regards Fred -----Original Message----- From: Susan Harkins [mailto:harkins at iglou.com] Sent: Wednesday, March 19, 2003 6:10 AM To: SQLList Subject: [dba-SQLServer]basic question In Access, a compact will reset the AutoNumber seed value to one more than the largest AN value already in the table. Is there anyway to do this with an Identity column in SQL Server? Susan H. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com