对于自动增量字段:MAX(ID)vs TOP 1 ID ORDER BY ID DESC

我想从字段中找到最高的AutoIncremented值.(它不会在我可以使用的插入后获取@@SCOPE_IDENTITY等)这两个查询中的哪一个会运行得更快或提供更好的性能. Id是主要的关键和autoincrement领域Table1.这适用于Sql Server 2005.

SELECT MAX(Id) FROM Table1

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

[编辑]
在这种情况下,Id是我已定义聚簇索引的字段.
如果索引是ID DESC那么..
是的,如果
1. Id是聚簇索引+主键,那么知道如何影响性能会很好.
2. Id是聚簇索引而不是主键.
3. Id是非聚集索引ASC +主键.
4. Id是非聚集索引ASC而不是主键.
5. Id是非聚集索引DESC +主键.
6. Id是非聚集索引DESC而不是主键.
我只是AutoIncrement

希望它不是一个很高的命令!

1> John Sansom..：

---

如果存在聚簇索引,则两个查询之间的性能几乎没有差异.

这是因为两者都将执行将承担100%查询成本的聚集索引扫描.

对没有索引的列执行两个查询会导致在两个执行计划中使用3个运算符.

Top子句使用Sort运算符,Max函数使用Stream Aggregate运算符.

当没有索引时,MAX()函数提供更好的性能.

可以找到概念证明,并且可以在此处找到测试场景的完整演练

性能比较Top 1 Verses MAX()Funciton

2> Mike DeFehr..：

---

没有人提到IDENT_CURRENT("表1") -打击他们全都冲去-当然,这仅适用于标识列,但这是问题...

---

投票,但不总是*适用,因为`IDENT_CURRENT`不一定在范围内.为了最快和最安全,事务应该在存储过程中并使用`SCOPE_IDENTITY()`

3> Quassnoi..：

---

从理论上讲,他们将使用相同的计划并运行几乎相同的时间.

在实践中,

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

将更可能使用一个PRIMARY KEY INDEX.

此外,如果您决定选择其他一些列,则此扩展名更具扩展性id.

实际计划MAX()说:

SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN

,而计划TOP 1说:

SELECT <- TOP <- CLUSTERED INDEX SCAN

,即aggregate省略.

Aggregate实际上不会在这里做任何事情,因为只有一行.

PS As @Mehrdad Afshari并@John Sansom指出,在非索引字段MAX上稍快一些(当然不是20优化器所说的那样):

-- 18,874,368 rows

SET LANGUAGE ENGLISH
SET STATISTICS TIME ON
SET STATISTICS IO ON
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC
PRINT 'MAX'
SELECT MAX(id) FROM master
PRINT 'TOP 1'
SELECT TOP 1 id FROM master ORDER BY id DESC

Changed language setting to us_english.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 20 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 447, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5452 ms,  elapsed time = 2766 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6813 ms,  elapsed time = 3449 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 44, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5359 ms,  elapsed time = 2714 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6766 ms,  elapsed time = 3379 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5406 ms,  elapsed time = 2726 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6780 ms,  elapsed time = 3415 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5392 ms,  elapsed time = 2709 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6766 ms,  elapsed time = 3387 ms.
MAX

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5374 ms,  elapsed time = 2708 ms.
TOP 1

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(????? ??????????: 1)
Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 6797 ms,  elapsed time = 3494 ms.