I want to find the highest AutoIncremented value from a field. (its not being fetched after an insert where I can use @@SCOPE_IDENTITY
etc)
Which of these two queries would run faster or gives better performance.
Id
is the primary key and autoincrement
field for Table1
. And this is for Sql Server 2005.
SELECT MAX(Id) FROM Table1
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
[Edit]
Yes in this case Id
is the field on which I have defined the clustered index.
If the index is ID DESC
then what..
And yes it would be nice to know how the performance would be affected if
1. Id is a clustered index + primary key.
2. Id is a clustered index and not primary key.
3. Id is a non clustered index ASC + primary key.
4. Id is a non clustered index ASC and not primary key.
5. Id is a non clustered index DESC + primary key.
6. Id is a non clustered index DESC and not primary key.
7. Id is just AutoIncrement
Hope its not a tall order!
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.