How to change an IDENTITY field to NO-IDENTITY field in SQL

How to change an IDENTITY field to NO-IDENTITY field in SQL

Post by R. Hoe » Sun, 26 Nov 2006 06:11:41


Hello,

I have a table with a field called ID, which is an identity field. (See
create code below)

CREATE TABLE [dbo].[ADMINS](
[ID] [int] IDENTITY(74,1) NOT NULL,
[ZOEKID] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
[PASSWORD] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[OMSCHR] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[CREATED] [datetime] NOT NULL CONSTRAINT [DF_ADMINS_CREATED] DEFAULT
(getdate()),
[MODIFIED] [datetime] NOT NULL CONSTRAINT [DF_ADMINS_MODIFIED] DEFAULT
(getdate()),
CONSTRAINT [PK_ADMINS] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


Regarding [ID] [int] IDENTITY(74,1) NOT NULL,

How can I remove the IDENTITY of the field ID in a SQL statement, when the
table already exists?
And how can I reanable it after I have added some data (Starting the seed at
the higest ID +1)

I can't use SET IDENTITY_INSERT ON because of some other issues!

--
Regards,

Ronald Hoek

Applicationdevelopper
ComponentAgro B.V.
 
 
 

How to change an IDENTITY field to NO-IDENTITY field in SQL

Post by Oliver Tow » Sun, 26 Nov 2006 08:28:02

> I can't use SET IDENTITY_INSERT ON because of some other issues!

Why not? That's the way to do it.

Oliver Townshend

 
 
 

How to change an IDENTITY field to NO-IDENTITY field in SQL

Post by Thui » Sun, 26 Nov 2006 19:47:16

See my previous post about updating BLOB-fields

- Ronald