StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In theory, they will use same plans and run almost same time.</p> <p>In practice,</p> <pre><code>SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC </code></pre> <p>will more probably use a <code>PRIMARY KEY INDEX</code>.</p> <p>Also, this one is more extendable if you will decide to select some else column along with <code>id</code>.</p> <p>An actual plan on <code>MAX()</code> says:</p> <pre><code>SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN </code></pre> <p>, while plan for <code>TOP 1</code> says:</p> <pre><code>SELECT <- TOP <- CLUSTERED INDEX SCAN </code></pre> <p>, i. e. <code>aggregate</code> is omitted.</p> <p>Aggregate actually won't do anything here, as there is but one row.</p> <p><em>P. S.</em> As <code>@Mehrdad Afshari</code> and <code>@John Sansom</code> noted, on a non-indexed field <code>MAX</code> is slightly faster (of course not <code>20</code> times as optimizer says):</p> <pre> -- 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. </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload