这是我正在查看的表的简化版本：
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.
