[dba-SQLServer]basic question

Did you see the response about changing the identity value to no, saving,
and then resetting -- worked like a charm.

Susan H.


> 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
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
>