在聚簇索引上使用顺序GUID键的INSERT不会明显更快

在聚簇索引上使用顺序GUID键的INSERT不会明显更快

您可以尝试修改后的脚本并发布结果吗？

    SET NOCOUNT ON    CREATE TABLE TestGuid1 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,    SomeDate DATETIME, batchNumber BIGINT, FILLER CHAr(100))    CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,    SomeDate DATETIME, batchNumber BIGINT, FILLER CHAr(100))    DECLARE @BatchCounter INT = 1    WHILE (@BatchCounter <= 20)    BEGIN     BEGIN TRAN    DECLARE @LocalCounter INT = 0        WHILE (@LocalCounter <= 100000)        BEGIN        INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)        SET @LocalCounter +=1        END    SET @LocalCounter = 0        WHILE (@LocalCounter <= 100000)        BEGIN        INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)        SET @LocalCounter +=1        END    SET @BatchCounter +=1    COMMIT     END    DBCC showcontig ('TestGuid1') WITH tableresults    DBCC showcontig ('TestGuid2')  WITH tableresults    SELECt batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWID()]    FROM TestGuid1    GROUP BY batchNumber    SELECt batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]    FROM TestGuid2    GROUP BY batchNumberDROP TABLE TestGuid1DROP TABLE TestGuid2

我发现各个运行之间的结果差异很大（在我的笔记本电脑上而不是服务器上！），但是连续运行的明显趋势是更快。

NEWID()

均值5168.9

batchNumber          NEWID()-------------------- -----------1         42702         24803         27064         33335         74806         53467         43068         77139         731310        476011        468012        411313        343314        268615        496316        804017        531318        816019        953320        2750

NEWSEQUENTIALID()

均值3000.85

batchNumber          NEWSEQUENTIALID()-------------------- -----------------1         20162         18203         18864         18705         48736         34737         37308         36909         198310        202011        190612        559613        210014        195015        209616        187617        519618        211019        211320        7713