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.

 

 

Code Snippet

/*

            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#')

*/

 

 

 

Code Snippet

--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'

 

 

 

Code Snippet

--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'

 

 

 

 

 

------------------------------------------------------------------------------------------------

 

 

 

Code Snippet

--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

 

 

posted @   邓维  阅读(2400)  评论(0编辑  收藏  举报
编辑推荐:
· 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)
点击右上角即可分享
微信分享提示