I'm using ActiveRecord-JDBC with SQL Server 2005. I found the attached implementation of add_limit_offset to be more
efficient than the one included in version 0.5. The downside is that it will not work with SQL Server 2000.
Maybe there's some way to distribute both versions of the adapter.
def add_limit_offset!(sql, options)
if limit = options[:limit] and offset = options[:offset]
if options[:order]
sql.sub!(/(SELECT.*)(\s+ORDER BY.*)/i) do
"SELECT B.* FROM (SELECT A.*, ROW_NUMBER() over (ORDER BY #{options[:order]}) AS internal$rownum FROM
(#{$1}) A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rownum <= #{limit + offset}#{$2}"
end
else
sql.sub!(/(SELECT.*)/i) do
"SELECT B.* FROM (SELECT A.*, ROW_NUMBER() over (ORDER BY internal$const) AS internal$rownum FROM (SELECT
C.*, 1 AS internal$const FROM ( #{$1} ) C ) A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rownum <=
#{limit + offset}"
end
end
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
sql.sub!(/^\s*SELECT([\s]*distinct)?/i) do
"SELECT#{$1} TOP #{options[:limit]}"
end unless options[:limit].nil?
end
end |
