[dba-SQLServer]basic question

Yup, I did, but I chose to ignore it because this answer does not take into account what is going on in the background.  If anybody interested, run the Profiler while "resetting" the identity field through the GUI, and you will see what SQL Server actually does in order for you to "think" that it is resetting the field.   In reality the server does the following:

- creates a new table without the IDENTITY clause for the selected field;
- generates all dependent tables, views, functions, stored procedures, and triggers;
- inserts all records from the original table;
- drops the original table;
- renames the new table to the original name;
- recompiles all dependent objects from the scripts generated earlier.

Robert Djabarov
Senior SQL Server DBA
USAA IT/DBMS
? (210) 913-3148 - phone
? (210) 753-3148 - pager

 -----Original Message-----
From: 	Susan Harkins [mailto:harkins at iglou.com] 
Sent:	Wednesday, March 19, 2003 2:55 PM
To:	dba-sqlserver at databaseadvisors.com
Subject:	Re: [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
>
>
>

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com