Change a column in one table to an automatic growth method _mssql

A student asked me yesterday, "a table has been built, can you change one of the fields to automatic growth?" "," Yes, but there is no need to modify it, it should be designed when the table is being built, "I said. This time he and another student
discussed. He thought he could, and another tried to say no. Because they are not the students I brought the class, they also consulted their teachers, so I did not comment.

Requirements:
How to modify one column in a table to grow automatically.

Answer:
1) Scenario One: No data in the table, you can use drop column and then add column

ALTER TABLE table name drop column column name
ALTER TABLE name AD D Column name int identity (1,1)

2) Scenario two: a part of the data already exists in the table

/**************** Preparation Environment ********************/--to determine if there is a test table if OBJECT_ID (n ' Test ', n ' U ') is NOT NULL drop TABLE test-creates test table CREATE TABLE test (ID int not NULL, name varchar)--Insert temporary data insert I Nto test VALUES (1, ' Jackie ') insert into test values (3, ' Zhang Ziyi ') insert into test values (4, ' Liu ') inserts into test values (8, ' King  Philip ') select * FROM test/**************** implementation change auto grow column ********************/BEGIN TRANSACTION CREATE TABLE Test_tmp (ID int NOT null identity (1,1), name varchar. NOT NULL) Go Set identity_insert test_tmp ' Go if exists ' (SELECT * from t EST) EXEC (' INSERT into test_tmp (ID, name) Select ID, name from test with (Holdlock Tablockx) ') Go Set identity_insert te St_tmp off Go drop table test go EXEC sp_rename n ' test_tmp ', n ' Test ', ' OBJECT ' Go commit go/**************** Verify results * * * /INSERT INTO test values (' Zhangman ') SELECT * FROM Test 

Summary: Modifying the table Design interface is easiest. If the column already has data stored in it, the modification may throw an exception that can be resolved using data import export. In short, regardless of the way you use, you need to back up the data in advance.