SQL max row of a table

Which one is better in SQL Server 2008 or it doesn开发者_高级运维't matter as the database optimizes.

SELECT * FROM table
WHERE datecolumn = (SELECT max(datecolumn) FROM table);


SELECT TOP 1 * FROM table ORDER BY datecolumn DESC;

Looking at the actual SQL execution plan for these queries, it results in exactly the same execution plan and the same execution cost. However it might differ depending on the index setup and collected statistics, therefore it is best to do the check yourself.

One thing to be aware of is that the two queries might not return the same result. The 2nd SQL will always return one record where the first record might return multiple rows if datecolumn is not unique.

SELECT * FROM table WHERE datecolumn = (SELECT max(datecolumn) FROM table);

SELECT TOP 1 * FROM table ORDER BY datecolumn DESC;

these query will return difference result in cost. from sql server execution plan the first query. the sub query will run first and scan the table tuples and get the max datecolumn after that the outer query will scan the table and search for datcolumn=..... this way is not efficiency.

second query is more efficiency because there is just one scan tuples of the table at one times.

for note: if the datacolumn is a primary key or index column, it is not significant difference of both query. but if this column not an index and primary key, it can significantly difference in a cost

You need to look at execution plan to the answer.