How to alter column to identity(1,1)
You can't alter the existing columns for identity.
You have 2 options,
1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column
But take spl care when these columns have any constraints / relations.
/*
For already craeted table Names
Drop table Names
Create table Names
(
ID int,
Name varchar(50)
)
Insert Into Names Values(1,'SQL Server')
Insert Into Names Values(2,'ASP.NET')
Insert Into Names Values(4,'C#')
*/
--In this Approach you can retain the existing data values on the newly created identity column
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL IDENTITY (1, 1),
Name varchar(50) NULL
) ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS(SELECT * FROM dbo.Names)
INSERT INTO dbo.Tmp_Names (Id, Name)
SELECT Id, Name FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'
--In this approach you can’t retain the existing data values on the newly created identity column;
--The identity column will hold the sequence of number
Alter Table Names Add Id_new Int Identity(1,1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID','Column'
------------------------------------------------------------------------------------------------
--create test table
create table table1 (col1 int, col2 varchar(30))
insert into table1 values (100, 'olddata')
--add identity column
alter table table1 add col3 int identity(1,1)
GO
--rename or remove old column
exec sp_rename 'table1.col1', 'oldcol1', 'column'
OR
alter table table1 drop column col1
--rename new column to old column name
exec sp_rename 'table1.col3', 'col1', 'column'
GO
--add new test record and review table
insert into table1 values ( 'newdata')
select * from table1
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 数据库服务器 SQL Server 版本升级公告
· 程序员常用高效实用工具推荐,办公效率提升利器!
· C#/.NET/.NET Core技术前沿周刊 | 第 23 期(2025年1.20-1.26)