SELECT * 
FROM tinytable t                          /* one narrow row */
JOIN smalltable s on t.id=s.tinyId        /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3                       /* doesn't match */
ORDER BY b.CreatedUtc          /* try with and without this ORDER BY */@H_502_3@ 
 
我知道我可以在bigtable.smallGuidId上有一个索引,但是,我认为在这种情况下实际上会让它变得更糟.
 
 
这是创建/填充表以供测试的脚本.奇怪的是,小型表具有nvarchar(max)字段似乎很重要.我似乎很重要的是我用一个guid加入bigtable(我猜它想要使用哈希匹配).
 
  
 
CREATE TABLE tinytable
  (
     id        INT PRIMARY KEY IDENTITY(1,1),foreignId INT NOT NULL
  )

CREATE TABLE smalltable
  (
     id     INT PRIMARY KEY IDENTITY(1,GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),tinyId INT NOT NULL,Magic  NVARCHAR(max) NOT NULL DEFAULT ''
  )

CREATE TABLE bigtable
  (
     id          INT PRIMARY KEY IDENTITY(1,CreatedUtc  DATETIME NOT NULL DEFAULT GETUTCDATE(),smallGuidId UNIQUEIDENTIFIER NOT NULL
  )

INSERT tinytable
       (foreignId)
VALUES(7)

INSERT smalltable
       (tinyId)
VALUES(1)

-- make a million rows 
DECLARE @i INT;

SET @i=20;

INSERT bigtable
       (smallGuidId)
SELECT GuidId
FROM   smalltable;

WHILE @i > 0
  BEGIN
      INSERT bigtable
             (smallGuidId)
      SELECT smallGuidId
      FROM   bigtable;

      SET @i=@i - 1;
  END@H_502_3@ 
 
我已经在sql 2005,2008和2008R2上测试了相同的结果.