SQL 如何将标识列添加到具有大量行的现有数据库表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7163275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add an identity column to an existing database table which has large number of rows
提问by Mohan
I have a database table which has ~ 40 000 000 rows. I want to add an identity column to this table. How to do it in a log-friendly manner?
我有一个数据库表，它有 ~ 40 000 000 行。我想在这个表中添加一个标识列。如何以日志友好的方式做到这一点？
When I do the following:
当我执行以下操作时：
ALTER TABLE table_1
ADD id INT IDENTITY
this just fills up the entire log space.
这只会填满整个日志空间。
Is there any way to do it in a log-friendly manner? The database is on SQL Server 2008.
有没有办法以日志友好的方式做到这一点？数据库位于 SQL Server 2008 上。
Thanks, Mohan.
谢谢，莫汉。
回答by Martin Smith
The overall process will probably be a lot slower with more overall locking overhead but if you only care about transaction log size you could try the following.
整个过程可能会因为更多的整体锁定开销而变慢，但如果您只关心事务日志大小，则可以尝试以下操作。
- Add a nullable integer non identity column (metadata only change).
- Write code to update this with unique sequential integers in batches. This will reduce the size of each individual transaction and keep the log size down (assuming simple recovery model). My code below does this in batches of 100 hopefully you have an existing PK you can leverage to pick up where you left off rather than the repeated scans that will take increasingly long towards the end.
- use
ALTER TABLE ... ALTER COLUMNto mark the column asNOT NULL. This will require the entire table to be locked and scanned to validate the change but not require much logging. - Use
ALTER TABLE ... SWITCHto make the column an identity column. This is a metadata only change.
- 添加一个可为空的整数非标识列（仅元数据更改）。
- 编写代码以批量使用唯一的连续整数更新它。这将减少每个单独事务的大小并保持日志大小（假设简单恢复模型）。我下面的代码以 100 个批次执行此操作，希望您拥有一个现有的 PK，您可以利用它来从上次中断的地方开始，而不是重复扫描，这将花费越来越长的时间。
- 用于
ALTER TABLE ... ALTER COLUMN将列标记为NOT NULL。这将需要锁定和扫描整个表以验证更改，但不需要太多日志记录。 - 使用
ALTER TABLE ... SWITCH使列标识列。这是仅元数据更改。
Example Code Below
下面的示例代码
/*Set up test table with just one column*/
CREATE TABLE table_1 ( original_column INT )
INSERT INTO table_1
SELECT DISTINCT
number
FROM master..spt_values
/*Step 1 */
ALTER TABLE table_1 ADD id INT NULL
/*Step 2 */
DECLARE @Counter INT = 0 ,
@PrevCounter INT = -1
WHILE @PrevCounter <> @Counter
BEGIN
SET @PrevCounter = @Counter;
WITH T AS ( SELECT TOP 100
* ,
ROW_NUMBER() OVER ( ORDER BY @@SPID )
+ @Counter AS new_id
FROM table_1
WHERE id IS NULL
)
UPDATE T
SET id = new_id
SET @Counter = @Counter + @@ROWCOUNT
END
BEGIN TRY;
BEGIN TRANSACTION ;
/*Step 3 */
ALTER TABLE table_1 ALTER COLUMN id INT NOT NULL
/*Step 4 */
DECLARE @TableScript NVARCHAR(MAX) = '
CREATE TABLE dbo.Destination(
original_column INT,
id INT IDENTITY(' + CAST(@Counter + 1 AS VARCHAR) + ',1)
)
ALTER TABLE dbo.table_1 SWITCH TO dbo.Destination;
'
EXEC(@TableScript)
DROP TABLE table_1 ;
EXECUTE sp_rename N'dbo.Destination', N'table_1', 'OBJECT' ;
COMMIT TRANSACTION ;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION ;
PRINT ERROR_MESSAGE() ;
END CATCH ;
回答by NG.
There are two ways of adding an identity column to a table with existing data:
有两种方法可以将标识列添加到具有现有数据的表中：
Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.
Create a new column with identity & drop the existing column
创建一个带有标识的新表，将数据复制到这个新表，然后删除现有表，然后重命名临时表。
创建具有标识的新列并删除现有列
Reference : http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/
参考：http: //cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/
回答by bds89
I just did this to my table that has over 2700 rows. Go to the design of the table, add the new column, set it to not allow nulls, set the column as an identity column in the column properties, and that should do it. I literally just did this less than 5 minutes ago and it worked for me. Please select as answer if this answers your question.
我刚刚对我的超过 2700 行的表做了这个。转到表的设计，添加新列，将其设置为不允许空值，在列属性中将该列设置为标识列，这样就可以了。我真的是在不到 5 分钟前做到了这一点，它对我有用。如果这回答了您的问题，请选择作为答案。
