Performance effect of using TOP 1 in a SELECT query

Question

I have a User table where there are a Username and Application columns. Username may repeat but combination of Username + Application is unique, but I don't have the unique constraint set on the table (for performance)

Question: will there be any difference (performance-wise) between :

SELECT * FROM User where UserName='myuser' AND Application='myapp' 

AND -

SELECT TOP 1 * FROM User where UserName='myuser' AND Application='myapp' 

As combination of Username + Application is unique, both queries will always return no more than one record, so TOP 1 doesn't affect the result. I always thought that adding TOP 1 will really speed things up as sql server would stop looking after it found one match, but I recently read in an article that using TOP will actually slow things down and it's recommended to avoid, though they haven't explained why.

Any comments?

Thank you! Andrey

Answer 1

You may get some performance difference from just using top, but the real performance you get by using indexes.

If you have an index for the UserName and Application fields, the database doesn't even have to touch the table until it has isolated the single record. Also, it will already know from the table statistics that the values are unique, so using top makes no difference.

Answer 2

If there's more than one row in the results and no ORDER BY clause, the "TOP 1" saves a ton of work for the server. If there's an order by clause the server still has to materialize the entire result set anyway, and if there's only one row it doesn't really change anything.