For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC

If there is a clustered index there is virtually no difference in performance between the two queries.

This is becuase both will perform a Clustered Index Scan that will bear 100% of the query cost.

Performing the two queries on a column that does not have an index results in 3 operators being used in both execution plans.

The Top clause uses the Sort operator and the Max function uses a Stream Aggregate operator.

When there is no index, the MAX() function provides better performance.

Proof of concept can be found and full walkthrough of a test scenario can be found here:

Performance Comparison Top 1 Verses MAX() Function

Nobody mentioned IDENT_CURRENT('Table1') - blows them all away - of course it only works on identity columns, but that was the question...

In theory, they will use same plans and run almost same time.

In practice,

SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC

will more probably use a PRIMARY KEY INDEX.

Also, this one is more extendable if you will decide to select some else column along with id.

An actual plan on MAX() says:

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

, while plan for TOP 1 says:

SELECT <- TOP <- CLUSTERED INDEX SCAN

, i. e. aggregate is omitted.

Aggregate actually won't do anything here, as there is but one row.

P. S. As @Mehrdad Afshari and @John Sansom noted, on a non-indexed field MAX is slightly faster (of course not 20 times as optimizer says):

-- 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.