Search code examples
sql-serverquery-optimization

Performance difference between SELECT * and SELECT TOP(1)

In our legacy code we have VB statement that translates to:

SELECT Id FROM Table
WHERE IdentifierId = 1

This returns just short of 900 000 entries and the count is increasing each day. The whole idea is to get the last Id + 1 (achieved in VB by taking the highest Id and adding 1 to it), so I'm wondering if, and by what margin, the following would be better:

Select TOP (1) Id FROM Table
WHERE IdentifierId = 1
ORDER BY ID DESC

Any insigth on the performance difference is appreciated.

PS: Id and IdentifierId are forming a composite key for Table.

Solution

  • For anyone interested, or in a similar situation, I've measured the two approaches. The SELECT Id FROM Table on average took about 1.1** seconds, whereas Select TOP (1) Id FROM Table took 0.00* seconds. The TOP (1) statement was, as @The Impaler suggested, almost instantaneous.