如何插入到使用顺序GUID作为主键的表中? [英] How to INSERT into a table that uses sequential GUIDs as a primary key?

问题描述

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

Here is a simplified version of the table I am looking at:

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

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

I want to insert new records into this table. I have tried 3 approaches:

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

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

This approach inserts, but the resulting key isn't a nice sequential GUID like the other ones in the table

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

此操作失败，并显示错误

This fails with error

newsequentialid()内置函数只能在CREATE TABLE或ALTER TABLE语句中的'uniqueidentifier'类型的列的DEFAULT表达式中使用.它不能与其他运算符组合以形成复杂的标量表达式.

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

这失败，并显示错误

无法将值NULL插入表"mydatabase.dbo.frustratingtable"的"id"列中；列不允许为空. INSERT失败.

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

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

Is it possible to solve this without altering the table definition?

推荐答案

您也许可以通过使用表变量来做到这一点:

You may be able to do this by way of using a table variable:

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

由于表变量是通过default设置值的，因此我们允许使用NEWSEQUENTIALID().

Since the table variable sets the value via a default, we're allowed to use NEWSEQUENTIALID().

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

Of course, for very large data sets, there's a penalty in temporarily having two copies of the data lurking around.

另一种选择是使用称为COMB的旧解决方案，该解决方案是在引入NEWSEQUENTIALID()之前使用的:

An alternative would be to use an older solution, called COMBs, which were used before NEWSEQUENTIALID() was introduced:

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

生成的uniqueidentifiers具有比NEWID()更好的局部性.

Generates uniqueidentifiers with better locality than NEWID() by itself does.

这篇关于如何插入到使用顺序GUID作为主键的表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！