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

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,ThirdField)
SELECT NEWSEQUENTIALID() as Id,'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,错误
 
  
 
 
  
Cannot insert the value NULL into column ‘id’,table ‘mydatabase.dbo.frustratingtable’; column does not allow nulls. INSERT fails.
 
 
 
 
是否有可能在不改变表定义的情况下解决这个问题？

解决方法

 您可以通过使用表变量来执行此操作： 
  
  
 
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
 
 
由于表变量通过默认值设置值,因此我们允许使用NEWSEQUENTIALID().
 
 
当然,对于非常大的数据集,暂时存在两个潜伏的数据副本会受到惩罚.
 
 
另一种方法是使用名为COMB的旧解决方案,它在引入NEWSEQUENTIALID()之前使用：
 
  
 
SELECT CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)
 
 
生成具有比NEWID()更好的局部性的uniqueidentifier.
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!