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)
Ukrainian ex-Rubyist blog 