nhibernate/nhibernate-core — pull request #86 by julian-maughan (2012-02-26 14:58:24+00:00)
Title: NH-2214: Refine SQL Server 2005/2008 paging of distinct queries

MsSql2005Dialect can fail to return distinct paged results because the use of ROW_NUMBER makes each row unique. For example:

```sql
SELECT TOP (222) id
FROM (
SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
FROM fish fish_) as query
WHERE query.__hibernate_sort_row > 111
ORDER BY query.__hibernate_sort_row
```

A solution was put forward and I have refined it in this pull request. The above query will now be generated as:

```sql
SELECT TOP (222) id
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
FROM (
SELECT DISTINCT fish_.id
FROM fish fish_) as q_) as query
WHERE query.__hibernate_sort_row > 111
ORDER BY query.__hibernate_sort_row
```

The solution has a breaking change due to a scenario in which MsSql2005DialectQueryPager (a new class used by the dialect for paging) will throw an exception:

```csharp
throw new HibernateException(
"The dialect was unable to perform paging of a statement that requires distinct results, and "
+ "is ordered by a column that is not included in the result set of the query.");
```

As far as I know, there is no way around this limitation. However, previously the dialect would have probably failed silently - unexpectedly returning duplicate results.

https://nhibernate.jira.com/browse/NH-2214
https://nhibernate.jira.com/browse/NH-2492