blocks|key|893149|text|不，您不能将自动增量选项添加到包含数据的现有列中，我认为您提到的选项是最好的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|893150|看一下here。|offset|length|893151|entityMap|0|LINK|mutability|MUTABLE|url|http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/^0|0|3|4|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

<p>No, you can not add an auto increment option to an existing column with data, I think the option which you mentioned is the best.</p>

<p>Have a look <a href="http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/" rel="nofollow">here</a>.</p>


blocks|key|676518|text|如果不想添加新列，并且可以保证当前int列是惟一的，那么可以将所有数据选择到一个临时表中，删除该表并使用指定的IDENTITY列重新创建。然后，可以使用SET+IDENTITY+INSERT+ON将临时表中的所有数据插入到新表中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|676519|entityMap^0|24|M|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<p>If you don't want to add a new column, and you can guarantee that your current int column is unique, you could select all of the data out into a temporary table, drop the table and recreate with the IDENTITY column specified. Then using <code>SET IDENTITY INSERT ON</code> you can insert all of your data in the temporary table into the new table.</p>


blocks|key|893248|text|更改IDENTITY属性实际上只是对元数据的更改。但是要直接更新元数据，需要在单用户模式下启动实例，并处理sys.syscolpars中的一些列，这是没有文档记录的/不受支持的，我不会推荐或给出任何额外的细节。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|893249|对于在SQL+Server+2012%2B上遇到这个问题的人来说，到目前为止，实现自动递增列的这个结果的最简单方法是创建一个SEQUENCE对象，并将next+value+for+seq设置为列的默认值。|893250|或者，对于以前的版本(从2005年起)，this+connect+item上发布的解决方案显示了一种完全受支持的方法，无需使用ALTER+TABLE...SWITCH进行任何大小的数据操作。也在MSDN+here上写了博客。虽然实现这一点的代码并不是非常简单，而且有一些限制-例如，被更改的表不能成为外键约束的目标。|893251|示例代码。|893252|设置不含identity列的测试表。|893253|CREATE+TABLE+dbo.tblFoo+
(
bar+INT+PRIMARY+KEY,
filler+CHAR(8000),
filler2+CHAR(49)
)


INSERT+INTO+dbo.tblFoo+(bar)
SELECT+TOP+(10000)+ROW_NUMBER()+OVER+(ORDER+BY+(SELECT+0))
FROM+master..spt_values+v1,+master..spt_values+v2|code-block|syntax|javascript|893254|将其更改为一个identity列(或多或少是即时的)。|893255|BEGIN+TRY;
++++BEGIN+TRANSACTION;

++++/*Using+DBCC+CHECKIDENT('dbo.tblFoo')+is+slow+so+use+dynamic+SQL+to
++++++set+the+correct+seed+in+the+table+definition+instead*/
++++DECLARE+@TableScript+nvarchar(max)
++++SELECT+@TableScript+=+
++++'
++++CREATE+TABLE+dbo.Destination(
++++++++bar+INT+IDENTITY('+%2B+
+++++++++++++++++++++CAST(ISNULL(MAX(bar),0)%2B1+AS+VARCHAR)+%2B+',1)++PRIMARY+KEY,
++++++++filler+CHAR(8000),
++++++++filler2+CHAR(49)
++++++++)

++++++++ALTER+TABLE+dbo.tblFoo+SWITCH+TO+dbo.Destination;
++++'+++++++
++++FROM+dbo.tblFoo
++++WITH+(TABLOCKX,HOLDLOCK)

++++EXEC(@TableScript)


++++DROP+TABLE+dbo.tblFoo;

++++EXECUTE+sp_rename+N'dbo.Destination',+N'tblFoo',+'OBJECT';


++++COMMIT+TRANSACTION;
END+TRY
BEGIN+CATCH
++++IF+XACT_STATE()+<>+0+ROLLBACK+TRANSACTION;
++++PRINT+ERROR_MESSAGE();
END+CATCH;|893256|测试结果。|893257|INSERT+INTO+dbo.tblFoo+(filler,filler2)+
OUTPUT+inserted.*
VALUES+('foo','bar')|893258|给出|893259|bar+++++++++filler++++filler2
-----------+---------+---------
10001+++++++foo+++++++bar++++++|893260|清理|893261|DROP+TABLE+dbo.tblFoo|893262|entityMap|0|LINK|mutability|MUTABLE|url|http://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property|1|http://blogs.msdn.com/b/dfurman/archive/2010/04/20/adding-the-identity-property-to-a-column-of-an-existing-table.aspx^0|2|8|1H|E|0|1O|8|21|I|0|1R|K|K|H|0|2U|4|1|0|0|4|8|0|0|7|8|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1I|8|@$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1N|8|@$9|1O|A|1P|B|C]|$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1S|8|@$9|1T|A|1U|B|C]]|D|@$9|1V|A|1W|1|1X]|$9|1Y|A|1Z|1|20]]|E|$]]|$1|J|3|K|5|6|7|21|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|22|8|@$9|23|A|24|B|C]]|D|@]|E|$]]|$1|N|3|O|5|P|7|25|8|@]|D|@]|E|$Q|R]]|$1|S|3|T|5|6|7|26|8|@$9|27|A|28|B|C]]|D|@]|E|$]]|$1|U|3|V|5|P|7|29|8|@]|D|@]|E|$Q|R]]|$1|W|3|X|5|6|7|2A|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|P|7|2B|8|@]|D|@]|E|$Q|R]]|$1|10|3|11|5|6|7|2C|8|@]|D|@]|E|$]]|$1|12|3|13|5|P|7|2D|8|@]|D|@]|E|$Q|R]]|$1|14|3|15|5|6|7|2E|8|@]|D|@]|E|$]]|$1|16|3|17|5|P|7|2F|8|@]|D|@]|E|$Q|R]]|$1|18|3|-4|5|6|7|2G|8|@]|D|@]|E|$]]]|19|$1A|$5|1B|1C|1D|E|$1E|1F]]|1G|$5|1B|1C|1D|E|$1E|1H]]]]

<p>Changing the <code>IDENTITY</code> property is really a metadata only change.  But to update the metadata directly requires starting the instance in single user mode and messing around with some columns in <code>sys.syscolpars</code> and is undocumented/unsupported and not something I would recommend or will give any additional details about.</p>

<p>For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a <code>SEQUENCE</code> object and set the <code>next value for seq</code> as the column default.</p>

<p>Alternatively, or for previous versions (from 2005 onwards), the workaround posted on <a href="http://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property" rel="noreferrer">this connect item</a> shows a completely supported way of doing this without any need for size of data operations using <code>ALTER TABLE...SWITCH</code>. Also blogged about on MSDN <a href="http://blogs.msdn.com/b/dfurman/archive/2010/04/20/adding-the-identity-property-to-a-column-of-an-existing-table.aspx" rel="noreferrer">here</a>. Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.</p>

<h2>Example code.</h2>

<h3>Set up test table with no <code>identity</code> column.</h3>

<pre><code>CREATE TABLE dbo.tblFoo 
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)


INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2
</code></pre>

<h3>Alter it to have an <code>identity</code> column (more or less instant).</h3>

<pre><code>BEGIN TRY;
    BEGIN TRANSACTION;

    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
      set the correct seed in the table definition instead*/
    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        bar INT IDENTITY(' + 
                     CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        filler CHAR(8000),
        filler2 CHAR(49)
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@TableScript)


    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() &lt;&gt; 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;
</code></pre>

<h3>Test the result.</h3>

<pre><code>INSERT INTO dbo.tblFoo (filler,filler2) 
OUTPUT inserted.*
VALUES ('foo','bar')
</code></pre>

<h3>Gives</h3>

<pre><code>bar         filler    filler2
----------- --------- ---------
10001       foo       bar      
</code></pre>

<h3>Clean up</h3>

<pre><code>DROP TABLE dbo.tblFoo
</code></pre>


blocks|key|893312|text|如果要通过设计器执行此操作，可以按照此处的说明进行操作"Save+changes+is+not+permitted"+when+changing+an+existing+column+to+be+nullable|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|893313|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/7711026/save-changes-is-not-permitted-when-changing-an-existing-column-to-be-nullable#7711114^0|R|27|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<p>If you want to do this via the designer you can do it by following the instructions here <a href="https://stackoverflow.com/questions/7711026/save-changes-is-not-permitted-when-changing-an-existing-column-to-be-nullable#7711114">&quot;Save changes is not permitted&quot; when changing an existing column to be nullable</a></p>


<p>I have a table <code>table1</code> in SQL server 2008 and it has records in it. </p>

<p>I want the primary key <code>table1_Sno</code> column to be an auto-incrementing column.  Can this be done without any data transfer or cloning of table?</p>

<p>I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?</p>


SQL Server, How to set auto increment after creating a table without data loss?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在SQL server2008中有一个表table1，其中有记录。我希望主键table1_Sno列是一个自动递增的列。这可以在没有任何数据传输或表克隆的情况下完成吗？我知道我可以使用ALTER TABLE添加一个自动增量列，但是我能简单地将AUTO_INCREMENT选项添加到作为主键的现有列中吗？

问SQL Server，如何设置创建表后自动递增而不丢失数据？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server，如何设置创建表后自动递增而不丢失数据？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server，如何设置创建表后自动递增而不丢失数据？
EN