向现有列添加标识

不能更改标识的现有列。

你有两个选择,

1. 使用标识创建新表并删除现有表

2. 使用标识创建新列并删除现有列

方法1。( 新表 )在这里,您可以在新创建的标识列上保留现有的数据值。

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

方法2 新专栏 )您可以在新创建的标识列上保留现有的数据值,标识列将保留数字序列。

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

有关详细信息,请参阅以下Microsoft SQL Server论坛帖子:

How to alter column to identity(1,1)

在SQL2005及更高版本中,有一个技巧可以在不更改表的数据页的情况下解决这个问题。对于大表来说,这一点很重要,因为触摸每个数据页可能需要几分钟或几小时。即使标识列是主键,是聚集索引或非聚集索引的一部分,或者其他gotchas(可以触发更简单的“添加/删除/重命名列”解决方案),这种技巧也能起作用。

诀窍如下:您可以使用SQL Server的 ALTER TABLE...SWITCH 语句在不更改数据的情况下更改表的架构,这意味着您可以用相同的表架构(但不包含标识列)替换具有标识的表。同样的技巧也适用于向现有列添加标识。

通常情况下, 更改表…开关 用于有效地将分区表中的完整分区替换为新的空分区。但它也可以用于非分区表中。

我用这个技巧在5秒内将25亿行表中的一列从标识转换为非标识(为了运行一个查询计划对非标识列效果更好的多小时查询),然后在不到5秒钟的时间内恢复标识设置。

这是一个它如何工作的代码示例。

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

这显然比其他答案中的解决方案更为复杂,但如果您的桌子很大,这可能是一个真正的救生员。有一些警告:

• 据我所知,使用此方法只能更改表列的标识。不允许添加/删除列、更改可空性等。
• 在进行切换之前,您需要先放下外键,然后再恢复它们。
• 与模式绑定函数、视图等相同。
• 新表的索引需要完全匹配(相同的列、相同的顺序等)
• 新表和旧表必须在同一文件组中。
• 仅适用于SQL Server 2005或更高版本
• 我以前认为这个技巧只适用于SQL Server的企业版或开发人员版(因为分区只在企业版和开发人员版中受支持),但是Mason G.Zhwiti在下面的评论中说它也适用于SQL标准版。我假设这意味着对企业或开发人员的限制不适用于alter table…switch。

有一个好 article on TechNet 详细说明上述要求。

更新- Eric Wu 在下面添加了有关此解决方案的重要信息的评论。复制到这里以确保它得到更多关注:

这里还有另一个值得一提的警告。想到 新表将很高兴地从旧表接收数据,并且 新行将按照标识模式插入,它们将 从1开始,如果所述列是主键,则可能中断。 考虑运行 DBCC CHECKIDENT('<newTableName>') 之后立即 切换。见 msdn.microsoft.com/en-us/library/ms176057.aspx 为了更多 信息。

如果表正在使用新行进行主动扩展(意味着在添加标识和添加新行之间没有太多的停机时间,则不是 DBCC CHECKIDENT 您需要手动将新表架构中的标识种子值设置为大于表中现有的最大ID,例如。 IDENTITY (2435457, 1) . 您可以同时包含 ALTER TABLE...SWITCH 以及 校验码 在一个事务中(或者没有——还没有测试过这个),但似乎手动设置种子值会更容易、更安全。

显然,如果没有向表中添加新行(或者只是偶尔添加新行,比如每天的ETL过程),那么不会发生这种争用情况。 校验码 很好。

不能将列更改为标识列。您需要做的是创建一个新列,该列定义为get go中的标识,然后删除旧列,并将新列重命名为旧名称。

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

马克

这里有一个很酷的解决方案: SQL SERVER – Add or Remove Identity Property on Column

简而言之,在SQL管理器中手动编辑您的表,切换标识,不保存更改,只显示将为更改创建的脚本,复制它并在以后使用它。

它节省了大量的时间,因为它(脚本)包含与所更改的表相关的所有外键、索引等。手动写入…上帝禁止。

简单的解释

使用sp_rename重命名现有列

exec sp_rename'表名.现有列名','新列名','列'

重命名示例:

现有列userid重命名为olduserid

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

然后使用alter query添加一个新列,将其设置为主键和标识值

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

设置主键示例

新创建的列名是userid

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

然后删除重命名的列

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

删除重命名列的示例

ALTER TABLE Users DROP COLUMN OldUserID

现在,我们已经向表中现有的列添加了primarykey和标识。

我是一个Java开发人员,碰巧在没有DBA的情况下加入团队,而作为开发人员,我不能获得DBA权限。我的任务是在两个数据库之间移动一个完整的模式,因此在没有DBA的情况下,我必须通过运行脚本来完成这项工作,因为我没有管理权限,所以无法在SQL Server 2008中使用GUI。

但是,在新的schema.table上运行存储过程时,所有内容都被毫无问题地移动了,我发现在表中丢失了标识字段。我仔细检查了创建表的脚本,它就在那里,但是,当我运行脚本时,SQL Server没有得到它。后来一位DBA告诉我,他以前也见过这个问题。

在任何情况下,对于SQL Server 2008,这些步骤是我为解决这个问题所采取的,它们都有效的,所以我在这里发布这个消息,希望它能对某些人有所帮助。这就是我所做的,因为我对另一个表有FK依赖性,这使得这更加困难:

我使用这个查询来验证标识是否确实丢失,并查看表上的依赖项。

1.)在表中查找统计信息:

exec sp_help 'dbo.table_name_old';

2.)创建一个重复的、相同的新表,除了在以前的pk字段上添加一个标识字段。

3.)禁用标识以移动数据。

SET IDENTITY_INSERT dbo.table_name ON 

4.)传输数据。

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.)验证数据是否存在。

SELECT * FROM dbo.table_name_new

6.)重新启用身份。

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7) 这是我找到的最好的脚本,用于获取所有FK关系,以验证原始表作为依赖项引用的表。 我遇到了很多,所以它是一个守门员!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.)在下一步之前,请确保您拥有所有相关表的pk和fk脚本。

9.)您可以右键单击每个键并使用SQL Server 2008编写脚本。

10.)使用以下语法从依赖表中删除FK:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.)放下原来的桌子:

DROP TABLE dbo.table_name_old;

13.)这些后续步骤依赖于在步骤9中在SQL Server 2008中创建的脚本。

--将pk添加到新表中。

--将FK添加到新表中。

--将FK添加回依赖关系表。

14.)确认一切都是正确和完整的。我用图形用户界面查看表格。

15.)将新表重命名为原始表名。

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

最后,一切顺利!

考虑使用 SEQUENCE instead of IDENTITY .

在SQL Server 2014中(我不知道较低版本),您可以使用sequence简单地执行此操作。

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

从这里: Sequence as default value for a column

根据设计,没有简单的方法可以打开或关闭现有列的标识功能。唯一干净的方法是创建一个新列并使其成为标识列,或者创建一个新表并迁移数据。

如果使用SQL Server Management Studio除去列“id”上的标识值,将创建新的临时表,将数据移动到临时表,删除旧表,并重命名新表。

使用Management Studio进行更改,然后右键单击设计器并选择“生成更改脚本”。

您将看到,这就是SQL Server在后台所做的。

您不能这样做,您需要添加另一列、删除原始列并重命名新列或创建新表、复制数据并删除旧表,然后将新表重命名为旧表。

如果使用ssms并在设计器中将identity属性设置为on,那么SQL Server将在后台执行以下操作。因此,如果您有一个名为[user]的表,那么如果您创建userid和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

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

已经说过,有一种方法可以通过设置位值来破解系统表,但这是不受支持的,我不会这样做。

正如我在一般情况下所理解的,我们创建的表 主键 正在拥有 标识属性
所以 重命名 或 删除 与关联的列 主键 约束 将不可能,因为约束规则正在验证列结构。
要实现这一点,我们必须按以下方式处理一些步骤:
让我们假设 tablename='员工' 和 columnname='employeeid'

1。在“Employee”表中添加新列“EmployeeID_New”
更改表employee add employeeid_new int identity(1,1)

2. 现在从“Employee”表中删除列“EmployeeID”
   更改表EMPLOYEE DROP列EMPLOYEEID

3. 这将引发错误,因为主键约束规则适用并验证列结构。
   *β ’ msg 5074,级别16,状态1,第1行对象[pk_dbo.employee]依赖于colmn[employeeid]。' β1

4. 因此,我们必须首先从表“Employee”中删除主键约束,然后才能删除列
   alter table employee drop约束[pk_dbo.employee]

5. 现在,我们可以从“Employee”表中删除列“EmployeeID”,就像上一步出错时那样。
   更改表EMPLOYEE DROP列EMPLOYEEID

6. 现在,列“employeeid”已从表中删除 因此,我们将新添加的列“employeeid_new”重命名为“employeeid”
   sp_rename'employee.employeeid'、'employeeid'u new'、'column'

7. 要按原来的格式重新排列表,我们必须为“EmployeeID”列添加主键约束。
   alter table employee add约束[pk_dbo.employee]主键(employeeid)

8。 现在,为标识规则和现有主键约束修改了带有“employeeid”的表“employee”

遗憾的是,没有一个;Identity属性属于表而不是列。

更简单的方法是在图形用户界面中执行,但是如果这不是一个选项,那么您可以在复制数据、删除列、使用标识重新添加列以及将数据放回这些方面做很多工作。

见 here 为了一个简单的解释。

右键单击对象资源管理器中的表名。你会得到一些选择。单击“设计”。将为此表打开一个新选项卡。可以在“列属性”中添加标识约束。

要修改列的标识属性:

• 在服务器资源管理器中,右键单击要修改标识属性的表,然后单击“打开表定义”。 表将在表设计器中打开。
• 清除要更改的列的“允许空值”复选框。
• 在“列属性”选项卡中,展开“标识规范”属性。
• 单击is identity子属性的网格单元,然后从下拉列表中选择Yes。
• 在标识种子单元格中键入值。此值将分配给表中的第一行。默认情况下,值1将被指定。

就这样,而且对我很有效

如果原始海报真的想将现有的列设置为 PRIMARY KEY 对于表,实际上不需要列是 IDENTITY 列(两种不同的事情),然后可以通过T-SQL完成:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

请注意,在 主键 选择权。

虽然这篇文章很旧,我对请求者的需求做了一个假设,但我认为这些附加信息可能有助于用户遇到这个线程,因为我相信会话可能会导致人们相信,如果不先将现有列添加为新列,就无法将其设置为主键,这是不正确的。

根据我目前的情况,我采用这种方法。我想在通过脚本插入数据后为主表提供标识。

因为我想附加标识,所以它总是从1开始到我想要的记录计数结束。

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

这将创建具有标识的同一主键列

我使用了以下链接: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/

Add primary key to existing table

我不相信您可以使用TSQL将现有列更改为标识列。但是,您可以通过Enterprise Manager设计视图来完成。

或者,您可以创建一个新行作为标识列,删除旧列,然后重命名新列。

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

基本上有四个逻辑步骤。

1. 新建标识列。启用此新列的插入标识。

2. 将源列(要转换为标识的列)中的数据插入此新列。

3. 关闭新列的插入标识。

4. 删除源列并将新列重命名为源列的名称。

可能还有一些更复杂的问题,比如跨多个服务器工作等。

有关步骤(使用ssms&t-sql),请参阅以下文章。这些步骤适用于对T-SQL掌握较少的初学者。

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx

为主键=bigint且没有标识集的所有表生成脚本;这将返回每个表生成的脚本列表;

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;