Differences in old and new pagings in SQL Server

I’ve added SQL SERVER 2011 support to Rails ActiveRecord adapter.
Main thing that I added was new paging.
(more about it here http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx)

I want to describe several problems that occurred while I implemented this.

First difference that ordering is necessary when paging is used.

Second difference is that offset must be used always, while limit isn’t necessary.

Third one is difference in limit 0.
select top(0) * from some_table
will return a list of columns.

select * from some_table order by id OFFSET 0 ROWS FETCH FIRST 0 ROWS ONLY
will raise error.

Forth difference with ordering with grouping.
For example: there are posts and comments tables.
We want select only 5 after latest posts where last comments was left.

select top(5) distinct id
from (
select posts.id, ROW_NUMBER() OVER (ORDER BY comments.posted_at) as rn_id
from posts left join comments on comments.post_id=posts.id) help_query
where help_query.rn_id > 1

In “Denali” we use only one query.
select distinct posts.id
from posts left join comments on comments.post_id=posts.id
order by comments.posted_at

is wrong. Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

OK. Lets do standard trick with grouping.

select posts.id
from posts left join comments on comments.post_id=posts.id
group by posts.id
order by max(comments.posted_at)

It is almost right. But now null values on first place.

So last variant is:
select posts.id
from posts left join comments on comments.post_id=posts.id
group by posts.id
order by CASE WHEN (max(comments.posted_at) is null) then 1 else 0 end DESC, max(comments.posted_at)

Advertisements

SQL or NoSQL

There is one good rule to find out what database you need.

LOLCATs rule: If LOLCATs can (and will) be added in database, NoSQL can be chosen with a clear conscience.

Otherwise, other reasons should be checked:
Should data be consistent?
What kind of statistics do you need?(real-time – NoSQL, data mining – SQL)
Are there lots of relations in database? Is a lot of joins planned?