CODEWITHSUNDEEP
sql-servert-sql
Philipp Grathwohl
Philipp Grathwohl

Reputation: 2836

Is ordering by a key necessary for paging

I read that when executing a TOP query without an ORDER BY that the results returned by the SQL Server may differ between executions because it's not guaranteed that you receive the same top x values due to the missing sorting.

I wondered if it is the same in the following sample:

In a table person there are columns id, name and age. The table contains thousands of rows, but all persons have the same age. The id is unique.

I now want to page through this table with a page size of e.g. 20 and order by age. 

SELECT *
FROM person
ORDER BY age
   -- ,id     -- is this necessary?
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

Does the same issue occur as with the TOP clause because the condition that it is ordered by matches a lot more records than the page size? Do I have to additionaly order by id to make sure that when going through the pages that every record occurs exactly once?

http://sqlfiddle.com/#!18/93988/3

Upvotes: 0

Views: 76

Answers (2)

codemonkey
codemonkey

Reputation: 608

Adding the Id will help to a certain extent but regardless of how you page, SQL select statements are not deterministic so you cannot make assumptions of uniqueness across query executions.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Yes. ORDER BY is the control you have to tell the server what ordering guarantees you want it to provide. Anything you fail to say in the ORDER BY clause is fair game for the system to fail to respect.

For dependable paging1, you need to ensure that you have enough expressions in your ORDER BY clause so that every row's position will be uniquely defined. For a single table SELECT, this is most easily achieved by including all primary key column(s) after any other ordering criteria.

1But note that this still assumes that the data in the table is static. Getting "once and only once" display of data when using paging and facing the possibilities of inserts, updates and deletes can be tricky and often here you'll wind up accepting the occasional paging mishap.

Upvotes: 2

Related Questions