ALTER按批添加列，但使用NEWSEQUENTIALID()

Question

我有一个大表，但不太大(在老化的硬件上不超过200万行)，当向现有表中添加非空列时，我通常遵循这里设置的结构，以避免在部署时运行数据库迁移时脚本超时的问题(FYI -这不是全文索引问题)。

因此，总括而言，我：

• 更改表并将列添加为NULL，而不添加默认约束
• 分批回填该列
• 更改表并将列更改为NULL，并添加默认约束

但是，在下面的情况中，我想添加一个新的UNIQUEIDENTIFER列，并用NEWSEQUENTIALID()而不是NEWID()值填充它。

如果没有批量运行，我的脚本将如下所示：

IF NOT EXISTS (
    SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'Invoice' 
    AND COLUMN_NAME = 'InternalId')
BEGIN
    ALTER TABLE Invoice 
        ADD InternalId UNIQUEIDENTIFIER NOT NULL 
        CONSTRAINT [DF_Invoice_InternalId] DEFAULT (NEWSEQUENTIALID())
END
GO

但是，如果我将其分成几个批次，并尝试用以下内容填充可空的InternalId：

IF NOT EXISTS (
    SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'Invoice' 
    AND COLUMN_NAME = 'InternalId')
BEGIN
    ALTER TABLE Invoice 
        ADD InternalId UNIQUEIDENTIFIER NULL
END

DECLARE @MaxId INT, @LoopStart INT, @LoopEnd INT, @LoopSize INT = 50000
SELECT @MaxId = MAX(InvoiceId) FROM Invoice
SELECT @LoopStart = MIN(InvoiceId) FROM Invoice
SET @LoopEnd = @LoopStart + @LoopSize

PRINT 'Updating InternalIds to a new GUID'
WHILE @LoopStart <= @MaxId
BEGIN
    -- update internal id
    UPDATE I 
    SET InternalId = NEWSEQUENTIALID()
    FROM Invoice I
    WHERE I.InvoiceId BETWEEN @LoopStart AND @LoopEnd

    SET @LoopStart = @LoopEnd + 1
    SET @LoopEnd = @LoopEnd + @LoopSize
END

IF EXISTS (
    SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'Invoice' 
    AND COLUMN_NAME = 'InternalId' 
    AND IS_NULLABLE = 'YES')
BEGIN
    ALTER TABLE Invoice 
        ALTER COLUMN InternalId UNIQUEIDENTIFIER NOT NULL
END

IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE name = 'DF_Invoice_InternalId')
BEGIN
    ALTER TABLE Invoice
        ADD CONSTRAINT [DF_Invoice_InternalId]  
        DEFAULT ((NEWSEQUENTIALID())) FOR [InternalId]
END

我得到以下错误：

Msg 302、级别16、状态0、第40行-- newsequentialid()内置函数只能在CREATE或ALTER语句中类型为‘unique标识符’的列的默认表达式中使用。它不能与其他运算符组合形成一个复杂的标量表达式。

关于如何解决这个问题有什么建议吗？还是我想得太多了？

进行此更改的原因是在API中外部公开顺序is (InternalId或可称为PublicId)，以替代当前的顺序数字is (InvoiceId)。数字Id (主键)应该保持在内部，因为它公开了一个连续的、可猜测的内部值。序贯GUID仍然是连续的，但也不是那么容易猜测。为了说明这一点，我正在做类似的事情，但是@First是通过API调用提供的。它用于使用水印处理来轮询和处理新发票。

CREATE TABLE #Test (
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    DateCreated DATETIME NOT NULL DEFAULT(GETDATE()),
    Code NVARCHAR(50) NOT NULL
)

DECLARE @Id INT
DECLARE @NO_OF_CHARS INT = 10
SET @Id = 1

WHILE @Id <= 12000
BEGIN 
    
   INSERT INTO #Test (Code) VALUES (SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS))
   SET @Id = @Id + 1
END

ALTER TABLE #Test 
    ADD InternalId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWSEQUENTIALID())

DECLARE @First UNIQUEIDENTIFIER
SELECT * FROM #Test
SELECT @First = InternalId FROM #Test WHERE Id = 1
SELECT * FROM #Test WHERE InternalID > @First

DROP TABLE #Test

Answer 1

可以使用约束将列添加为可空列，然后使用默认值更新列。在概念上是这样的：

ALTER TABLE Invoice 
    ADD InternalId UNIQUEIDENTIFIER NULL 
    CONSTRAINT [DF_Invoice_InternalId] DEFAULT (NEWSEQUENTIALID())

UPDATE I 
SET InternalId = DEFAULT
FROM Invoice I

 ALTER TABLE Invoice 
        ALTER COLUMN InternalId UNIQUEIDENTIFIER NOT NULL

但基于你的动机，我建议不要使用NEWSEQUENTIALID。这是可以猜测的。如果某人获得一个生成的If，他可以很容易地猜出以前的值和下面的值。看看这个StackOverflow 回答

在大多数情况下，下一个新序列可以通过获取当前值并将一个添加到第一个十六进制对来预测。换句话说，1E29E599-45F1-E311-80CA-00155D008B1C紧随其后的是1F29E599-45F1-45F1-80ca-00155D008B1C，其次是2029E599-45F1-E311-80CA-00155D008B1C。

Answer 2

一位同事指出了如何在这个实例中使用默认关键字，尽管我不能100%确定它是如何工作的，但它是如何工作的。

使用上面的示例，下面展示了这是如何工作的：

-- create the example table
CREATE TABLE #Test (
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    DateCreated DATETIME NOT NULL DEFAULT(GETDATE()),
    Code NVARCHAR(50) NOT NULL
)

DECLARE @Id INT
DECLARE @NO_OF_CHARS INT = 10
SET @Id = 1

WHILE @Id <= 12000
BEGIN 
    
   INSERT INTO #Test (Code) VALUES (SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS))
   SET @Id = @Id + 1
END

-- add the new column with a default but it is still nullable
ALTER TABLE #Test 
    ADD InternalId UNIQUEIDENTIFIER NULL DEFAULT(NEWSEQUENTIALID())
GO

DECLARE @MaxId INT, @LoopStart INT, @LoopEnd INT, @LoopSize INT = 50000
SELECT @MaxId = MAX(Id) FROM #Test
SELECT @LoopStart = MIN(Id) FROM #Test
SET @LoopEnd = @LoopStart + @LoopSize

PRINT 'Updating InternalIds to a new GUID'
WHILE @LoopStart <= @MaxId
BEGIN
    -- update internal id using the DEFAULT
    -- https://dba.stackexchange.com/questions/72604/adding-non-nullable-newsequentialid-column-to-existing-table
    UPDATE #Test 
    SET InternalId = DEFAULT
    WHERE Id BETWEEN @LoopStart AND @LoopEnd

    SET @LoopStart = @LoopEnd + 1
    SET @LoopEnd = @LoopEnd + @LoopSize
END

-- now make this not null
ALTER TABLE #Test ALTER COLUMN InternalId UNIQUEIDENTIFIER NOT NULL

-- do a basic test
DECLARE @First UNIQUEIDENTIFIER
SELECT * FROM #Test
SELECT @First = InternalId FROM #Test WHERE Id = 1
SELECT * FROM #Test WHERE InternalId > @First

DROP TABLE #Test

或者，这个SQL会显示它的作用：

SQL Fiddle

MS Server 2017架构设置

CREATE TABLE Test (
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    DateCreated DATETIME NOT NULL DEFAULT(GETDATE()),
    Code NVARCHAR(50) NOT NULL
)

查询1

DECLARE @Id INT
DECLARE @NO_OF_CHARS INT = 10
SET @Id = 1

WHILE @Id <= 10
BEGIN   
   INSERT INTO Test (Code) VALUES (SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS))
   SET @Id = @Id + 1
END

-- add the new column with a default but it is still nullable
ALTER TABLE Test 
    ADD InternalId UNIQUEIDENTIFIER NULL DEFAULT(NEWSEQUENTIALID())

结果

查询2

DECLARE @MaxId INT, @LoopStart INT, @LoopEnd INT, @LoopSize INT = 50000
SELECT @MaxId = MAX(Id) FROM Test
SELECT @LoopStart = MIN(Id) FROM Test
SET @LoopEnd = @LoopStart + @LoopSize

PRINT 'Updating InternalIds to a new GUID'
WHILE @LoopStart <= @MaxId
BEGIN
    -- update internal id using the DEFAULT
    -- https://dba.stackexchange.com/questions/72604/adding-non-nullable-newsequentialid-column-to-existing-table
    UPDATE Test 
    SET InternalId = DEFAULT
    WHERE Id BETWEEN @LoopStart AND @LoopEnd

    SET @LoopStart = @LoopEnd + 1
    SET @LoopEnd = @LoopEnd + @LoopSize
END

-- now make this not null
ALTER TABLE Test ALTER COLUMN InternalId UNIQUEIDENTIFIER NOT NULL

结果

查询3

-- do a basic test
SELECT * FROM Test

结果

|  Id |              DateCreated |       Code |                           InternalId |
|-----|--------------------------|------------|--------------------------------------|
| 191 | 2022-01-28T13:59:08.947Z | D61874FEB6 | BFC0423E-F36B-1410-80BF-800000000000 |
| 192 | 2022-01-28T13:59:08.947Z | C596780C9F | C0C0423E-F36B-1410-80C0-800000000000 |
| 193 | 2022-01-28T13:59:08.947Z | 3356A26232 | C1C0423E-F36B-1410-80C1-800000000000 |
| 194 | 2022-01-28T13:59:08.947Z | D6D509EDE7 | C2C0423E-F36B-1410-80C2-800000000000 |
| 195 | 2022-01-28T13:59:08.947Z | E956CBCF4B | C3C0423E-F36B-1410-80C3-800000000000 |
| 196 | 2022-01-28T13:59:08.947Z | E32EAB6E92 | C4C0423E-F36B-1410-80C4-800000000000 |
| 197 | 2022-01-28T13:59:08.947Z | B2137001AA | C5C0423E-F36B-1410-80C5-800000000000 |
| 198 | 2022-01-28T13:59:08.947Z | 0FB26856C9 | C6C0423E-F36B-1410-80C6-800000000000 |
| 199 | 2022-01-28T13:59:08.947Z | 5DFCD86CAC | C7C0423E-F36B-1410-80C7-800000000000 |
| 200 | 2022-01-28T13:59:08.947Z | C4BFCCDC59 | C8C0423E-F36B-1410-80C8-800000000000 |

查询4

-- test the watermark
DECLARE @First UNIQUEIDENTIFIER = (SELECT TOP 1 InternalId FROM Test ORDER BY Id ASC)
--SELECT 'Watermark is: ' AS [Note], @First AS [WatermarkId]
SELECT * FROM Test WHERE InternalId > @First

结果

|  Id |              DateCreated |       Code |                           InternalId |
|-----|--------------------------|------------|--------------------------------------|
| 192 | 2022-01-28T13:59:08.947Z | C596780C9F | C0C0423E-F36B-1410-80C0-800000000000 |
| 193 | 2022-01-28T13:59:08.947Z | 3356A26232 | C1C0423E-F36B-1410-80C1-800000000000 |
| 194 | 2022-01-28T13:59:08.947Z | D6D509EDE7 | C2C0423E-F36B-1410-80C2-800000000000 |
| 195 | 2022-01-28T13:59:08.947Z | E956CBCF4B | C3C0423E-F36B-1410-80C3-800000000000 |
| 196 | 2022-01-28T13:59:08.947Z | E32EAB6E92 | C4C0423E-F36B-1410-80C4-800000000000 |
| 197 | 2022-01-28T13:59:08.947Z | B2137001AA | C5C0423E-F36B-1410-80C5-800000000000 |
| 198 | 2022-01-28T13:59:08.947Z | 0FB26856C9 | C6C0423E-F36B-1410-80C6-800000000000 |
| 199 | 2022-01-28T13:59:08.947Z | 5DFCD86CAC | C7C0423E-F36B-1410-80C7-800000000000 |
| 200 | 2022-01-28T13:59:08.947Z | C4BFCCDC59 | C8C0423E-F36B-1410-80C8-800000000000 |

“水印”值为'BFC0423E-F36B-1410-80BF-800000000000'

Answer 3

请注意，NEWSEQUENTIALID不能保证100%连续GUID的存在。在每个服务器重新启动后，NEWSEQUENTIALID可能以较低的值启动。请回顾一下这方面的微软。如果这是一个问题，您可能需要重新评估它在初始更新中的使用。

据我所知，NEWSEQUENTIALID的主要目的是减少使用GUID作为聚集密钥时的痛苦，以避免在数据页和页面分割中随意插入