Alter table to set the IDENTITY to off

Question

I have a table, which has a column(orderid), which its IDENTITY is set to true. Now I would like to set it off. How can I do that with ALTER COLUMN?some thing like this?

ALTER TABLE MyTable
ALTER Column MyColumn SET IDENTITY OFF

Answer 1

Once the identity column is set you cannot remove it or you cannot set it to OFF.

You probably have to drop the column by first copying the data into some other column(which does not have the identity). So it would be like add a new column to your table and copy the values of your existing identity column to it. Then drop the old column(having identity) and finally rename the new column to the old column name.

Answer 2

You can do this in 4 steps

1. Make new Column
2. Copy Data to that Column
3. Remove old column
4. Rename new column to old one

Answer 3

You have to use SET IDENTITY_INSERT TO ON. If you set it as ON then you should explicitly pass values to the ID Column.

Why should you switch off the Identity? May be you are trying to pass some explicit values.

Please refer the sample demo here.

-- Create tool table.
CREATE TABLE dbo.Tool
  (
     ID   INT IDENTITY NOT NULL PRIMARY KEY,
     NAME VARCHAR(40) NOT NULL
  );

GO

-- Inserting values into products table.
INSERT INTO dbo.Tool
            (NAME)
VALUES      ('Screwdriver'),
            ('Hammer'),
            ('Saw'),
            ('Shovel');

GO

-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE  NAME = 'Saw';

GO

SELECT *
FROM   dbo.Tool;

GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool
            (ID,
             NAME)
VALUES      (3,
             'Garden shovel');

GO

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON;

GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool
            (ID,
             NAME)
VALUES      (3,
             'Garden shovel');

GO

SELECT *
FROM   dbo.Tool;

GO

-- Drop products table.
DROP TABLE dbo.Tool;

GO