我有一个查询，它使用 select 运行得更快，top 100而没有top 100. 返回的记录数为0。您能否解释一下查询计划中的差异或分享解释这种差异的链接？
没有top文字的查询：
SELECT --TOP 100
*
FROM InventTrans
JOIN
InventDim
ON InventDim.DATAAREAID = 'dat' AND
InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
InventTrans.ITEMID = '027743' AND
InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
InventDim.ECC_BUSINESSUNITID = 'Казахстан';
上述查询计划（不带top）：
IO 和 TIME 统计信息（不带top）：
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 6256 ms, elapsed time = 13348 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
使用的索引（不带top）：
1. INVENTTRANS.I_177TRANSIDIDX
4 KEYS:
- DATAAREAID
- INVENTTRANSID
- INVENTDIMID
- RECID
2. INVENTTRANS.I_177ITEMIDX
3 KEYS:
- DATAAREAID
- ITEMID
- DATEPHYSICAL
3. INVENTDIM.I_698DIMIDIDX
2 KEYS:
- DATAAREAID
- INVENTDIMID
查询top：
SELECT TOP 100
*
FROM InventTrans
JOIN
InventDim
ON InventDim.DATAAREAID = 'dat' AND
InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
InventTrans.ITEMID = '027743' AND
InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
InventDim.ECC_BUSINESSUNITID = 'Казахстан';
查询计划（带TOP）：
查询 IO 和 TIME 统计信息（带 TOP）：
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 257 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
使用的索引（带TOP）：
1. INVENTTRANS.I_177TRANSIDIDX
4 KEYS:
- DATAAREAID
- INVENTTRANSID
- INVENTDIMID
- RECID
2. INVENTTRANS.I_177DIMIDIDX
3 KEYS:
- DATAAREAID
- INVENTDIMID
- ITEMID
3. INVENTDIM.I_698DIMIDIDX
2 KEYS:
- DATAAREAID
- INVENTDIMID
4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
3 KEYS
- DATAAREAID
- ECC_BUSINESSUNITID
- INVENTLOCATIONID
将非常感谢有关该主题的任何帮助！
SQL Server 使用不同的排序算法为 TOP 100 构建不同的执行计划。有时它更快，有时它更慢。
有关它的更简单示例，请阅读一行可以更改查询计划多少？第 1部分和第 2 部分。
有关深入的技术细节，以及 TOP 100 算法实际上较慢的示例，请阅读Paul White 的排序、行目标和 TOP 100 问题。
底线：在您的情况下，如果您知道不会返回任何行，那么...不要运行查询，是吗？最快的查询是您从未做过的查询。但是，如果您需要进行存在性检查，只需执行 IF EXISTS（此处粘贴查询），然后 SQL Server 将执行一个甚至不同的执行计划。
查看这两个计划，您可以通过显着不同的百分比成本对两者进行关键查找。如果将鼠标悬停在对象上，您将看到执行次数。
键查找是对聚集索引的查找，因为在索引查找（右上角）中使用的索引未覆盖所有列（选择 *，因此必须使用聚集索引）。
Top 100 能够以较少的索引读取次数获得所需的 100 行，然后执行 100 次查找，而不是针对表中的每一行执行查找。还解释了不做“顶部”时阅读的页数增加。