A method of modifying a column in a table to grow automatically

Time:2021-6-12

Yesterday, a student asked me, “a table has been built. Can I change a field in it to auto grow?”“ Yes, but there’s no need to modify it. It should be designed when the table is built, “I said. At this time, he and another student
Let’s talk about it. He thought it was ok, and the other tried to say no. Because they are not the students in my class, they also consulted their own teachers, so I did not express any opinions.

Demand:
How to modify a column in a table to grow automatically.

answer:
1) Scenario 1: if there is no data in the table, you can use drop column and then add column

Alter table name DROP column name
Alter table name add column name int identity (1,1)

2) Scenario 2: some data already exist in the table

/****************Prepare environment********************/

--Judge whether there is a test table
if object_id(N'test',N'U') is not null
drop table test

--Create test table
create table test
(
id int not null,
name varchar(20) not null
)

--Insert temporary data
Insert into test values (1, 'Jackie Chan')
Insert into test values (3,'zhang Ziyi ')
Insert into test values (4, 'Liu Ruoying')
Insert into test values (8, 'Faye Wong')

select * from test



/****************Change the auto growing column********************/

begin transaction

create table test_tmp
(
id int not null identity(1,1),
name varchar(20) not null
)
go

set identity_insert test_tmp on
go

if exists(select * from test)
exec(' insert into test_tmp(id, name ) select id, name from test with(holdlock tablockx)')
go

set identity_insert test_tmp off
go

drop table test
go

exec sp_rename N'test_tmp' ,N'test' , 'OBJECT'
go

commit

GO

/****************Validation results*****************/
Insert into test values ('zhang man ')
select * from test

Conclusion: it is the simplest to modify the interface of table design. If the existing data of this column is stored in the database, the modification may cause an exception, which can be solved by importing and exporting data. In a word, no matter what method is used, the data needs to be backed up in advance.