Change the table column for SQL Server from non-self-growth to self-growing

Reprint: http://www.thinksaas.cn/topics/0/423/423869.html

Demo

/**************** Preparation Environment ********************/--determine if a test table existsifOBJECT_ID (N'Test'N'U') isNotNULLdrop table Test--Creating the Test Table CREATE TABLE test (IDintNotNULL, name varchar ( -) notNULL)--Insert temporary data insert into test values (1,'Jackie Chan') insert into test values (3,'Zhang Ziyi') insert into test values (4,'Rene') insert into test values (8,'Faye Wong')Select* fromTest/**************** Implementing the Change auto-grow column ********************/begin transactioncreate Table test_tmp (IDintNotNULLIdentity1,1), name varchar ( -) notNULL) GoSetidentity_insert test_tmp OngoifExistsSelect* fromtest) EXEC ('INSERT into test_tmp (ID, name) Select ID, name from test with (Holdlock Tablockx)') GoSetidentity_insert test_tmp offgodrop table testgoexec sp_rename N'test_tmp'N'Test','OBJECT'Gocommitgo/**************** Validation Results *****************/INSERT into test values ('Zhang Man')Select* fromTest

Instance:

/**************** Implementing the Change auto-grow column ********************/begin transactioncreate Table test_tmp (UsergradeidintNotNULLIdentity1,1), Usergrade nvarchar (8) notNULL, [Status]intNotNULL, Remark nvarchar ( -), AddUser nvarchar ( +), Upduser nvarchar ( +), Addtime datetime2 (7), Updtime datetime2 (7)) GoSetidentity_insert test_tmp OngoifExistsSelect* fromm_usergrade) EXEC ('INSERT INTO test_tmp (usergradeid,usergrade,[status],remark,adduser,upduser,addtime,updtime)SelectUsergradeid,usergrade,[status],remark,adduser,upduser,addtime,updtime fromM_usergrade with (Holdlock Tablockx)')GoSetidentity_insert test_tmp offgodrop table m_usergradegoexec sp_rename N'test_tmp'N'M_usergrade','OBJECT'Gocommitgo

Change the table column for SQL Server from non-self-growth to self-growing