加标识有数据的话 ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null go SET IDENTITY_INSERT 表 ON go update 表 set 编号1=编号 go SET IDENTITY_INSERT 表 OFF go ALTER TABLE 表 DROP COLUMN 编号 go exec sp_rename '表.编号1','编号' go
1:alter table yourTable add idd int update yourTable set idd = id alter table yourTable drop column id sp_rename 'yourTable.[idd]','id',COLUMN
添加表字段的自增属性create table test1 (id int,cc varchar(2)) go BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_test1 ( id int NOT NULL IDENTITY (1, 1), cc varchar(2) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_test1 ON GO IF EXISTS(SELECT * FROM dbo.test1) EXEC('INSERT INTO dbo.Tmp_test1 (id, cc) SELECT id, cc FROM dbo.test1 TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_test1 OFF GO DROP TABLE dbo.test1 GO EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT' GO COMMIT
2: alter table yourTable add id int identity(1,1)
去除表字段的自增属性BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_test1 ( id int NOT NULL, cc varchar(2) NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.test1) EXEC('INSERT INTO dbo.Tmp_test1 (id, cc) SELECT id, cc FROM dbo.test1 TABLOCKX') GO DROP TABLE dbo.test1 GO EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT' GO COMMIT
标识有数据的话 ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null go SET IDENTITY_INSERT 表 ON go update 表 set 编号1=编号 go SET IDENTITY_INSERT 表 OFF go ALTER TABLE 表 DROP COLUMN 编号 go exec sp_rename '表.编号1','编号'
ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null
go
SET IDENTITY_INSERT 表 ON
go
update 表 set 编号1=编号
go
SET IDENTITY_INSERT 表 OFF
go
ALTER TABLE 表 DROP COLUMN 编号
go
exec sp_rename '表.编号1','编号'
go
update yourTable set idd = id
alter table yourTable drop column id
sp_rename 'yourTable.[idd]','id',COLUMN
go
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_test1
(
id int NOT NULL IDENTITY (1, 1),
cc varchar(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_test1 ON
GO
IF EXISTS(SELECT * FROM dbo.test1)
EXEC('INSERT INTO dbo.Tmp_test1 (id, cc)
SELECT id, cc FROM dbo.test1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_test1 OFF
GO
DROP TABLE dbo.test1
GO
EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT'
GO
COMMIT
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_test1
(
id int NOT NULL,
cc varchar(2) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.test1)
EXEC('INSERT INTO dbo.Tmp_test1 (id, cc)
SELECT id, cc FROM dbo.test1 TABLOCKX')
GO
DROP TABLE dbo.test1
GO
EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT'
GO
COMMIT
不知pengdali(大力 V2.0)用alter table的方法效率如何?
ALTER TABLE 表 ADD 编号1 bigint identity(1,1) not null
go
SET IDENTITY_INSERT 表 ON
go
update 表 set 编号1=编号
go
SET IDENTITY_INSERT 表 OFF
go
ALTER TABLE 表 DROP COLUMN 编号
go
exec sp_rename '表.编号1','编号'