CODEWITHSUNDEEP
sql-server
johannes.colmsee
johannes.colmsee

Reputation: 175

MS-SQL offset n rows fetch next m rows only with dummy order by column - consistent results?

the "Offset n rows fetch m rows only" clause only works with an specified "order by" - which of course makes the query slower than without order.

My co-worker and me tried ordering by a constant:

select 0 as SORT_DUMMY, p.* from table p order by 1 Offset 0 rows fetch next 10 only

the command runs without Problems, however - I am not sure, if this would return the rows in a reproducable manner (we are using this for pagination).

Anybody has any insights on this?

Regards

Johannes Colmsee

Upvotes: 1

Views: 809

Answers (1)

johannes.colmsee
johannes.colmsee

Reputation: 175

To answer my own question....

You definitely should sort by a column(s) which give an unique sort.

A couple of reasons (all go for MS-SQL, but should apply to all other DB too, did some positive preformance tests on oracleas well):

  • if you don't, the database can (and probably will in the future with an update, or for certain selects) return the rows in any order it sees fit, if you don't force it to. BUG

  • there is NO COST involved, cause best practice is to just use the Primary key(s) as sort, in their "natural sort order" (whichever that is, the index direction determines it)

  • sometimes - the incorrect dummy column will result in WORSE query times (trust me, been there, done it) - the probable reason being that "0 as dummy" obviously has no unique index.

  • if you sort by unique columns, you can use it for "constant query time", no matter if you select page 1, or page 1000. basically you will Keep the "Offset n rows fetch next m rows only" constant, but introduce an additional where clause "where [uniquesort_column] > [previous_page_last_unique_sort]". That where clause will be lightning fast, cause it uses the index, just as the order by.

So yeah - I kinda feel stupid for posting this question in the first place...because the main assumption (order by makes query slower) is NOT true if you order by the Primary key(s), in it's natural index's order (assuming YOUR database does it like all databases i tested....which should be pretty much all of them...maybe it is even in the SQL-Standard...idk)

Upvotes: 2

Related Questions