下面是用 企业管理器 把table1表的id字段改为自增的sql语句: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
恐怕只有按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列。
ALTER TABLE admin5 add [ID] int IDENTITY(1,1) NOT NULL
但是ALTER TABLE Table11 ALTER COLUMN ID int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL就报错!
还是右键--设计表,把ID 字段改为int,再在标识上选是,标识种子1,增量1。
但在已有数据的表上改不会成功的吧。
检查一下数据是否符合IDENTITY的要求
如果你原来的列里面写的就是ID,那么你可以增加一个IDENTITY列,来代替原来的ID,如果原来列里面写的不是ID,改成IDENTITY列还不是丢失了原来的数据?这样还不如增加一个新列。所以从这方面分析,SQL不用提供这种更改列的功能。
请大侠相助!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
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
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列。