假設原表結構如下: create table ttt ( t1 int, t2 varchar(8) )現在想把字段t1設為自增字段和主鍵.那麼運行下面的代碼: CREATE TABLE dbo.Tmp_ttt ( t1 int NOT NULL IDENTITY (1, 1), t2 varchar(8) NULL ) go SET IDENTITY_INSERT dbo.Tmp_ttt ON go IF EXISTS(SELECT * FROM dbo.ttt) EXEC('INSERT INTO dbo.Tmp_ttt (t1, t2) SELECT t1, t2 FROM dbo.ttt TABLOCKX') go SET IDENTITY_INSERT dbo.Tmp_ttt OFF go DROP TABLE dbo.ttt go EXECUTE sp_rename N'dbo.Tmp_ttt', N'ttt', 'OBJECT' go ALTER TABLE dbo.ttt ADD CONSTRAINT PK_ttt PRIMARY KEY CLUSTERED ( t1 ) ON [PRIMARY] COMMIT 為什麼不用 alter table ttt drop column t1 go alter table ttt add t1 identity(1,1) not null go alter table ttt add constrain primary key pk_t (t1) 的方法.是因為先刪掉一列.再增加一列. 那麼列的順序就改變了. 有可能帶來意想不到的問題. (比方說,你的程序中有個insert語句是沒有寫字段名的)
下面的语句是企业管理器中生成的。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_test ( id int NOT NULL IDENTITY (1, 1) ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_test ON GO IF EXISTS(SELECT * FROM dbo.test) EXEC('INSERT INTO dbo.Tmp_test (id) SELECT id FROM dbo.test TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_test OFF GO DROP TABLE dbo.test GO EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT' GO ALTER TABLE dbo.test ADD CONSTRAINT PK_test PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY]GO COMMIT
create table ttt
(
t1 int,
t2 varchar(8)
)現在想把字段t1設為自增字段和主鍵.那麼運行下面的代碼:
CREATE TABLE dbo.Tmp_ttt
(
t1 int NOT NULL IDENTITY (1, 1),
t2 varchar(8) NULL
)
go
SET IDENTITY_INSERT dbo.Tmp_ttt ON
go
IF EXISTS(SELECT * FROM dbo.ttt)
EXEC('INSERT INTO dbo.Tmp_ttt (t1, t2)
SELECT t1, t2 FROM dbo.ttt TABLOCKX')
go
SET IDENTITY_INSERT dbo.Tmp_ttt OFF
go
DROP TABLE dbo.ttt
go
EXECUTE sp_rename N'dbo.Tmp_ttt', N'ttt', 'OBJECT'
go
ALTER TABLE dbo.ttt ADD CONSTRAINT
PK_ttt PRIMARY KEY CLUSTERED
(
t1
) ON [PRIMARY]
COMMIT
為什麼不用
alter table ttt drop column t1
go
alter table ttt add t1 identity(1,1) not null
go
alter table ttt add constrain primary key pk_t (t1)
的方法.是因為先刪掉一列.再增加一列.
那麼列的順序就改變了.
有可能帶來意想不到的問題.
(比方說,你的程序中有個insert語句是沒有寫字段名的)
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_test
(
id int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_test ON
GO
IF EXISTS(SELECT * FROM dbo.test)
EXEC('INSERT INTO dbo.Tmp_test (id)
SELECT id FROM dbo.test TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_test OFF
GO
DROP TABLE dbo.test
GO
EXECUTE sp_rename N'dbo.Tmp_test', N'test', 'OBJECT'
GO
ALTER TABLE dbo.test ADD CONSTRAINT
PK_test PRIMARY KEY CLUSTERED
(
id
) ON [PRIMARY]GO
COMMIT