sql - 如何插入到使用顺序GUID作为主键的表中？

Question

这是我正在查看的表的简化版本:

CREATE TABLE [dbo].[FrustratingTable]
(
    [Id] Uniqueidentifier NOT NULL
    , [SecondField] [datetime]
    , [ThirdField] varchar(128)
)

我想在此表中插入新记录。我尝试了3种方法:

INSERT INTO [dbo].[FrustratingTable] (Id, SecondField, ThirdField)
    SELECT newid() as Id, 
           '6/25/2015' as SecondField, 'Example' as ThirdField

这种方法会插入，但是生成的键与表中的其他键不同，不是一个很好的顺序GUID

INSERT INTO [dbo].[FrustratingTable] (Id, SecondField, ThirdField)
SELECT NEWSEQUENTIALID() as Id, '6/25/2015' as SecondField, 'Example' as ThirdField

这失败并显示错误

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

INSERT INTO [dbo].[FrustratingTable] (SecondField,ThirdField)
SELECT '6/25/2015' as SecondField, 'Example' as ThirdField

失败并显示错误

Cannot insert the value NULL into column 'id', table 'mydatabase.dbo.frustratingtable'; column does not allow nulls. INSERT fails.

是否可以在不更改表定义的情况下解决此问题？

Answer 1

您可以通过使用表变量来执行此操作:

declare @t table (
    ID uniqueidentifier not null default newsequentialid(),
    SecondField datetime,
    ThirdField varchar(128)
)
insert into @t (SecondField,ThirdField)
    output inserted.ID,inserted.SecondField,inserted.ThirdField
    into FrustratingTable
values
('20150101','abc'),
('20150201','def'),
('20150301','ghi')

select * from FrustratingTable

结果:

Id                                   SecondField             ThirdField
------------------------------------ ----------------------- ------------
1FEBA239-091C-E511-9B2F-78ACC0C2596E 2015-01-01 00:00:00.000 abc
20EBA239-091C-E511-9B2F-78ACC0C2596E 2015-02-01 00:00:00.000 def
21EBA239-091C-E511-9B2F-78ACC0C2596E 2015-03-01 00:00:00.000 ghi

由于table变量通过 default设置值，因此我们可以使用 NEWSEQUENTIALID()。

当然，对于非常大的数据集，暂时隐藏两个数据副本是有代价的。

一种替代方法是使用一种称为COMB的旧解决方案，该解决方案在引入 NEWSEQUENTIALID()之前就已使用:

SELECT CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

生成比 uniqueidentifiers本身具有更好本地性的 NEWID()。