Modify Identity column

Modifying a identity column is not easy. However, we can achieve our goal by doing some trick.

Here is how.


Create a table with identity

CREATE TABLE [dbo].[NUM_OF_F_M](
[ID] [int] IDENTITY PRIMARY KEY NONCLUSTERED NOT NULL,
[NAME] [varchar](10) NULL,
[NUM_OF_F] [int] NULL,
)

Insert some data in the table.
VALUES ('HARUN', 10),
('SONY', 100),
('BASIR', 5),
('KAKON', 5),
('BABU', 100),
('RATAN', 4),
('JOY', 4)

Now let us create another table with similar name. But this time we will create table without IDENTITY.

CREATE TABLE [dbo].[NUM_OF_F_M_1](
[ID] [int] PRIMARY KEY NONCLUSTERED NOT NULL,
[NAME] [varchar](10) NULL,
[NUM_OF_F] [int] NULL,
)

Time to copy the data from the source table that has identity i.e  NUM_OF_F_M

INSERT INTO NUM_OF_F_M_1 SELECT * FROM NUM_OF_F_M


Verify the data in the table
SELECT * FROM NUM_OF_F_M_1 -- VERIFY DATA

Drop the source table
DROP TABLE NUM_OF_F_M -- DROP TABLE


Rename the table
SP_RENAME 'NUM_OF_F_M_1', 'NUM_OF_F_M', 'OBJECT'

Now the table has no identity column in it.

Note: Please do not change identity column unless you are ask to do so. There are reason to create identity on a column.