我在查询分析器中运行下面的语句 ALTER TABLE admin5 ALTER COLUMN ID int IDENTITY (1, 1) NOT NULL 目的是将现有的一个表的id列改变字段属性为自动编号 但是运行的结果是以下的报错 服务器: 消息 156,级别 15,状态 1,行 1 在关键字 'IDENTITY' 附近有语法错误。 请大侠指明,如果修改
恐怕只有按ghj1976(蝈蝈俊.net)之建议操作,参看SQL Server Books Online之
Alter Table (T-SQL)条:
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel. The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table. --这句表明不可更改为identity列。
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_Table1
(
id int NOT NULL IDENTITY (1, 1),
dd char(10) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (id, dd)
SELECT id, dd FROM dbo.Table1 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
改之前的表:
CREATE TABLE [dbo].[Table1] (
[id] [int] NULL ,
[dd] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
改后的表:
CREATE TABLE [dbo].[Table1] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[dd] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO