Identity property

Hello friends,

I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query.

Please let me know

Thanks & Regards
Girish Nehte

CREATE TABLE your_table( id_numint IDENTITY(1,1), fnamevarchar (20), minitchar(1), lnamevarchar(30))

PS: IDENTITY(seed, increment)|||

Thanks Addie.

Actually I have already build table and its identity field for id column is already set to "Yes". Now what I want to do is create a script which when executed will first set identity field to "No" and then again to "Yes", i.e. I want to alter that table.

How it can be done?

Thanks & Regards
Girish Nehte

|||T-SQL's ALTER TABLE statement doesn't support dropping the IDENTITY property in SQL Server 2000 or 7.0. Your only option for deleting an IDENTITY column is to create a new table structure without the IDENTITY column, then copy the data into this structure.

Just curious - why would you like to do that?

|||

I suspect what you want to do is to turn off the auto increment on the identity column so you can insert your own values. To do that, use

SETIDENTITY_INSERT tablenameOFF

then populate the table and issue

SETIDENTITY_INSERT tablenameON

|||

Actually in my project I want to create a script after running that script all the data from the data will be deleted and columns with identity "YES" will be reset to 0. Thats why and I think that it can be done by setting and resetting the identity field.

|||

Try:

1. using the truncate statement instead of delete (ex: TRUNCATE TABLE theNameOfYourTable)
2. EXEC ('DBCC CHECKIDENT(theNameOfYourTable,RESEED,0)')