What is the fastest way to select a single row in SQL? (SQL Server)

Question

I know of two different ways to select a single row from a table (without a where clause that guarantees a single record).

SELECT TOP 1 [Id]
FROM [MyTable]
ORDER BY [Id] ASC

or

SELECT [Id]
FROM [MyTable]
ORDER BY [Id] ASC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY

Which of these is statistically faster? ([Id] is a primary key)

Or is there a faster alternative to both of these that I'm unaware of?

Answer 1

There is no difference between the plans. The top syntax and order by syntax will utilise the same query plan. However for certain circumstances there may be a slightly better way to query out this information.

Below query fakes 100,000 rows of data in a table with a primary key to search on. Then executes populating the data, using the top 1 syntax, the offset fetch syntax and finally the direct search with a where clause.

declare @t table 

(
    id int primary key clustered,
    sometext nvarchar(150)
);

declare @runs int = 1;

while @runs<=100000
    begin
        insert @t (id, sometext)
        values
        (@runs, 'abc123');
        select @runs=@runs+1;
    end


SELECT TOP 1 [Id]
FROM @t
ORDER BY [Id] ASC;


SELECT [Id]
FROM @t
ORDER BY [Id] ASC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;


select id from @t where id=1;

The image then shows the 3 query plans. The first 2 queries show the exact same plan, an index scan using the primary key. However the third query with the where clause is showing an index seek. This is because the whole data set does not need to be scanned to find the top if you already know where in the heap you are looking.

Index seeks tend to perform better than scans, particularly in large tables with a lot of columns of different data types. In this scenario with a very simple table structure and 100,000 rows, the cost is exactly the same in the current batch (17%).

For a larger data set I'd argue for an approach which looks something like this.

declare @id int = 0;
select @id=min(id) from @t;

Then you can use this id variable in a where clause like this.

select id, sometext from @t where id=@id;

Which can take advantage of the index seek plan in a table which has more rows/is more complicated that this test.