SQL 2016 - does a simple SELECT TOP 1 row do an entire Table scan?

techresearch7777777 1,796

Hello, does a simple SELECT TOP 1 [City] FROM [Locations_Table] do an entire Table scan (this Table having 300,000 rows)?

Thanks in advance.

2 answers

Greg Low 1,495 Reputation points Microsoft Regional Director

2024-04-18T00:35:51.36+00:00

No it doesn't.

I presume [Locations_Table] is actually a table, not a view. That might change things.

But for a table, no it doesn't. Where that would change is if you have an ORDER BY clause added. Then it might need to.

But as your query is written, you're saying you don't care which row is returned, so there's no reason for SQL Server to actually read it all.
Please sign in to rate this answer.
techresearch7777777 1,796 Reputation points

2024-04-18T01:22:15.3866667+00:00

Thanks Greg Low for your reply, appreciate it.

Just trying a simple query to see if there's something preventing it from running ruling out large number of rows.

Eventually I do need to get all 300,000 rows.

When I do use run a SELECT TOP 10 * FROM... ORDER BY it does returns some rows...but when I try to grab all of the data SELECT * FROM... ORDER BY it errors out mentioning resource limit.

Sorry...my example was slightly inaccurate now that you mention it the target is actually a VIEW and not a Table... would this cause a full Table scan or have any other negative slow performance affects?

Greg Low 1,495 Reputation points Microsoft Regional Director

2024-04-18T01:37:15.4366667+00:00

When it's a view, the code from the view gets expanded (by default) into the surrounding query that you are executing. The whole thing gets optimized. So the contents of the view can greatly impact the overall query.

If a TOP 10 doesn't have a resource impact, you must be using an ORDER BY that's supported by an index, so it can work out which rows to return.

It's very odd for a SELECT * FROM ... ORDER BY to error out in any way. But the contents of the view could definitely be the cause. What resource limit error is returned? Is it possibly just a timeout? What version and edition of SQL Server are you using?

techresearch7777777 1,796 Reputation points

2024-04-18T02:34:10.9566667+00:00

Yes it hangs & times out after 10 minutes when not using the ORDER BY and you're correct using it with SELECT TOP 10 * FROM... ORDER BY does return rows.

Running SELECT * FROM ... ORDER BY on this View returns:

The OLE DB provider "SQLNCLI11" for linked server "LinkedServerHere" reported an error. Execution terminated by the provider because a resource limit was reached.

Ultimately we're trying to grab all of the data SELECT * on remote SQL 2012 and INSERT INTO our local SQL 2016 via Linked Server.

Any further thoughts?

Thanks so much for your help.
Sign in to comment
CosmogHong-MSFT 23,166 Reputation points Microsoft Vendor

2024-04-18T01:51:10.0933333+00:00

Hi @techresearch7777777

Without ORDER BY, there is no need to do a sort. Is there any index on that table?

It is suggested to make sure your statistics are up to date, so that you give the optimizer accurate information to find a good plan.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
techresearch7777777 1,796 Reputation points

2024-04-18T02:37:22.9733333+00:00

Thanks also CosmogHong-MSFT for your reply.

From what I've been told that there is an Index on that particular Column Linked Server View we're trying to grab all of the data so seems like ORDER BY is helping in some way.
Sign in to comment