SQL Server – OFFSET FETCH Overlapping Results on Second Page

offset-fetchsql server

When using OFFSET ... FETCH, I'm having I'm getting…interesting results.

Here are two sqlfiddles to illustrate my problem.

http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8

The first fiddle is the first page, while the second is the…second page.

Here are the unpaginated results.

First thing, the paginated results are not in the same order as the unpaginated results. I'm cool with that, I guess, as I'm not showing the unpaginated results anyway.

However, event though I'm specified an offset of 0, and for some reason it decided to put them in the reverse order they were entered, it skips the first 3.

On the second page, I see see results that appear on the first page.

The fact that they all have the same value, although in this example is contrived, is a reality in the specific query I'm dealing with in real life. It's a table, and the user selected to sort by date.

Best Answer

This is not unexpected. Your ORDER BY does not determine a complete (unique) order. As described in MSDN documentation:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

The underlying data that is used by the query must not change. ...

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

You have order by date but all the rows have the same value in date. To have determinate results with TOP / OFFSET ... FETCH, you have to use an ORDER BY that completely determines an ordering.

You could use for example:

order by date asc,
         name asc

or (if (date, name) is not unique either:

order by date asc,
         eventID asc

or:

order by date asc,
         name asc,
         eventID asc

If the query is produced by some form and can be customized by the user, you could - behind the scenes - add the primary key column(s) (I guesseventID here) in the end of the order by list, so you have an order that is determinate and stays the same across different executions of the query.

Related Solutions

SQL Server – Windowing Offset Based on Timestamp

I suggest you go down a different path with this. It looks like a pattern of select top() .... order by should be sufficient.

select top(@NumberOfItems)
  P.PhotoId,
  P.[Key],
  P.Created
from Content.Photo as P
  inner join Content.UserPhotoAssociation as UPA
    on P.PhotoId = UPA.PhotoId
where
  -- Older than CutoffTime
  P.Created < @CutoffTime and
  (
  -- My photos
  UPA.UserId = @UserId or
  -- Photos by someone monitored by me
  UPA.UserID in (
                select CC.Monitored
                from [User].CurrentConnections as CC
                where CC.Monitor = @UserId
                )
  )
order by P.Created desc, P.[Key]

To handle cases where Created happens to be equal you need to include the Key value in the cutoff handling. Perhaps a where clause something like this.

P.Created < @CutoffTime or (P.Created = @CutoffTime and P.Key > @CutoffKey)

SQL Server – UniqueIdentifier / GUID Internal Representation

According to the Wikipedia article on Globally unique identifier (in the "Binary encoding" section), Microsoft's implementation of GUIDs uses "Native" endianness for the first half (the first 8 bytes), and Big Endian encoding for the second 8 bytes. Microsoft Windows and SQL Server are "Little Endian" due to the Intel architecture being Little Endian, hence the "Native" implies "Little Endian". The following chart is copied from that Wikipedia article:

Bits    Bytes   Name    Endianness
32      4       Data1   Native
16      2       Data2   Native
16      2       Data3   Native
64      8       Data4   Big

Regarding Little Endianness of Microsoft SQL Server, the Collation and Unicode Support MSDN page states:

Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.

Best Answer

Related Solutions

SQL Server – Windowing Offset Based on Timestamp

SQL Server – UniqueIdentifier / GUID Internal Representation

Related Question