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?

They should have the same execution plan and the same performance. — Gordon Linoff, May 06 '19 at 15:20
`Or is there a faster alternative...` <= You could use an aggregate: `SELECT MAX([Id]) AS [Id] FROM [MyTable]`. You would have to compare the execution plans if it is faster, depends on your index structure. See also [MAX vs Top 1 - which is better?](https://stackoverflow.com/a/21420643/1260204) — Igor, May 06 '19 at 15:21
You can check the faster way with __SET STATISTICS PROFILE__ key word before your __SELECT__ This way will try the request and return you the stats of your request :) — Kiloumap Mrz, May 06 '19 at 15:31
For some reason I'm exceptionally bad at understanding the execution plans. @KiloumapMrz I'll try that out, thanks! — Werns, May 06 '19 at 15:35
You only need to understand plans to answer _why_ one query is more expensive. SET STATISTICS PROFILE is sufficient to tell you _which_ query is more expensive. — David Browne - Microsoft, May 06 '19 at 16:02

Dwight Reynoldson · Accepted Answer · 2019-05-06T16:25:08.473

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.

Yeah I'm using a table with millions of records and more than 2 columns. I'll test out your suggestion and see what kind of difference it makes. — Werns, May 06 '19 at 17:30
This is faster than what I was doing before. Can't give examples because the rest of the query sucks but you've definitely helped speed it up. Thanks! Marked it as the answer. — Werns, May 06 '19 at 19:18

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

1 Answers1