How can I alter a table turning ON or OFF an IDENTITY field ?

How can I alter a table turning ON or OFF anIDENTITY field ?

for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?

+

Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)In Enterprise Manager go to that table right click Design, highlight that column below there should be setting for Identity set to yes.

or in Query analizer I usually do this for tables that do have DO have identity insert on and I need to force a particular id

SET IDENTITY_INSERT [dbo].[TestTable] ON -- turns auto increment off

INSERT INTO TestTable (TestTabelID, Title)
VALUES (34, 'Test')

SET IDENTITY_INSERT [dbo].[TestTable] OFF -- turns auto increment back on

if it is not set to auto increment you may have to run an alter statement

-- untested code
ALTER TABLE [dbo].[TestTable]
ALTER COLUMN [TestTableID] [int] IDENTITY (1, 1) NOT NULL

seems odd that you say it has changed?|||You cannot use Alter table/column to add/remove identity property of a column. EM actually bulk copy data out to a temp table, delete the old one and then rename it any time you tinker with identity property.

--
-oj
http://rac4sql.net|||You could use:

SET IDENTITY_INSERT MyTable ON

--Do your stuff

SET IDENTITY_INSERT MyTable OFF|||Thank U all,
but what will happen to the UNIQUE numbers after I set IDENTITY ON back again ?

for example - if I had:

IDENTITY (1,1) ON
Client_ID 1 linked to Order_ID 1 linked to more...
Client_ID 2 linked to Order_ID 2 linked to more...
Client_ID 3 linked to Order_ID 3 linked to more...
...

and then (when transfering to other DB or for other reasons)
IDENTITY is set to OFF and Client_ID 2 is deleted:

Client_ID 1 linked to Order_ID 1 linked to more...
Client_ID 3 linked to Order_ID 2 linked to more...
...

so when I set up IDENTITY (1,1) turned ON again for the new data what will happen ?
will SQL Server will use the numbers on the table or just give the whole column
NEW numbers from 1.. to the last record...
(when will SQL Server remember all used unique numbers and when it will not)

Any articles on this issue ?
(use SQL server auto numbers or create my ownflexible unique numbers system with "locking" records)|||but what will happen to the UNIQUE numbers after I set IDENTITY ON back again ?

I believe nothing.
if you have three records with the values 5, 6, and 7 for the identity (CLIENT_ID) they will remain as is and any new records inserted to that table will start at 8.|||Thank U all.