SELECT TOP X vs Offset Fetch? Want to limit query results but not force a sort and scan?

Question

SELECT TOP X vs Offset Fetch? Want to limit query results but not force a sort and scan?

Maxer

SSCrazy Eights

Points: 8964
More actions
February 16, 2018 at 1:49 pm

#344053

I am making some views to use for AdHoc reporting and wanted to put a 100K row limit on them.
However, I am concerned if I do a simple SELECT TOP 100000 blah FROM TableView1 that I would have to do an ORDER BY and then it would result in forcing the entire table to scan and order itself.
When in reality I just don't want people returning resultsets over 100K rows.
Does OFFSET FETCH accomplish that? I had read it is basically a different way to do SELECT TOP X or SELECT TOP X, discard then select next TOP X.
Is there a better way to limit how many rows are returned from a view without forcing an actual ORDER BY?
Thanks!
EDIT: SET ROWCOUNT?
Does that work without forcing a sort? I should test that query plan...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

Well one difference is you have to use an order by with OFFSET you don't with TOP X, but functionally if you do have an order by TOP 100000 would be the same as OFFSET 0 ROWS FETCH NEXT 100000 ROWS ONLY and just based on some very limited testing on a single table generate the same execution plans.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Top and Rowcount both work without sorts, however when you use them such, there's no guarantee as to which rows you get, and the rows you get can be different on subsequent executions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

I also would NOT make a view of this. Rather, I'd create an iTVF as a bit of a "parameterized view" so that you don't have to change the actual code for everyone's thoughts on what the limits should be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Maxer SSCrazy Eights Points: 8964 More actions · Answer 4

Thanks for all the info. In this scenario the user (at a minimum) is supposed to apply filters on year, month, and customer (an orders history table with 100 million plus rows, but provided they filter should not be more than 40k rows ever returned).

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 5

Maxer - Monday, February 19, 2018 8:09 AM
Thanks for all the info. In this scenario the user (at a minimum) is supposed to apply filters on year, month, and customer (an orders history table with 100 million plus rows, but provided they filter should not be more than 40k rows ever returned).

Why can't that control be applied at the application level?

Maxer SSCrazy Eights Points: 8964 More actions · Answer 6

ZZartin - Monday, February 19, 2018 9:00 AM
Maxer - Monday, February 19, 2018 8:09 AM
Thanks for all the info. In this scenario the user (at a minimum) is supposed to apply filters on year, month, and customer (an orders history table with 100 million plus rows, but provided they filter should not be more than 40k rows ever returned).
Why can't that control be applied at the application level?

Because Microsoft... Sigh. PowerBI. It is what it is, not my choice in tool selection. It is in direct query mode.