建议你用设计去修改。来看看你用设计器去修改时,ms是怎么做的,如果用语句,你也得这么搞:很麻烦IF object_id('tb','u') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id INT,v VARCHAR(2)) INSERT tb SELECT 1,'aa' UNION ALL SELECT 3,'bb' UNION ALL SELECT 12,'cc' GOSELECT * FROM tb /* 1 aa 3 bb 12 cc */ GOBEGIN TRANSACTION SET QUOTED_IDENTIFIER ON 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 GO CREATE TABLE dbo.Tmp_tb ( id int NOT NULL IDENTITY (1, 1), v varchar(2) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_tb ON GO IF EXISTS(SELECT * FROM dbo.tb) EXEC('INSERT INTO dbo.Tmp_tb (id, v) SELECT id, v FROM dbo.tb WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_tb OFF GO DROP TABLE dbo.tb GO EXECUTE sp_rename N'dbo.Tmp_tb', N'tb', 'OBJECT' GO COMMIT GOINSERT tb SELECT 'dd' UNION ALL SELECT 'ee' SELECT * FROM tb GO /* 1 aa 3 bb 12 cc 13 dd 14 ee */
drop column aaid
go
alter table tb
add aaid int identity(1,2)
不用设计器.不删除要用SQL语句修改.应该可以修改吧.
用DBCC CHECKIDENT('表名',RESEED,新标志值)也只能设置当前标志值,不能改变初始种子和步长。
只有先drop再add(不过这一列就跑到最后一列去了)
还是用表设计器直接改比较好。
DROP TABLE tb
GO
CREATE TABLE tb(id INT,v VARCHAR(2))
INSERT tb SELECT 1,'aa'
UNION ALL SELECT 3,'bb'
UNION ALL SELECT 12,'cc'
GOSELECT * FROM tb
/*
1 aa
3 bb
12 cc
*/
GOBEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
GO
CREATE TABLE dbo.Tmp_tb
(
id int NOT NULL IDENTITY (1, 1),
v varchar(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tb ON
GO
IF EXISTS(SELECT * FROM dbo.tb)
EXEC('INSERT INTO dbo.Tmp_tb (id, v)
SELECT id, v FROM dbo.tb WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tb OFF
GO
DROP TABLE dbo.tb
GO
EXECUTE sp_rename N'dbo.Tmp_tb', N'tb', 'OBJECT'
GO
COMMIT
GOINSERT tb SELECT 'dd' UNION ALL SELECT 'ee'
SELECT * FROM tb
GO
/*
1 aa
3 bb
12 cc
13 dd
14 ee
*/