Method: Sequel::MSSQL::DatasetMethods#select_sql

Defined in:
lib/sequel/adapters/shared/mssql.rb

#select_sql ⇒ Object

MSSQL Requires the use of the ROW_NUMBER window function to emulate an offset. This implementation requires MSSQL 2005 or greater (offset can’t be emulated well in MSSQL 2000).

The implementation is ugly, cloning the current dataset and modifying the clone to add a ROW_NUMBER window function (and some other things), then using the modified clone in a subselect which is selected from.

If offset is used, an order must be provided, because the use of ROW_NUMBER requires an order.

Raises:



396
397
398
399
400
401
402
403
404
405
406
407
 
# File 'lib/sequel/adapters/shared/mssql.rb', line 396

def select_sql
  return super unless o = @opts[:offset]
  raise(Error, 'MSSQL requires an order be provided if using an offset') unless order = @opts[:order]
  dsa1 = dataset_alias(1)
  rn = row_number_column
  subselect_sql(unlimited.
    unordered.
    select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}.
    from_self(:alias=>dsa1).
    limit(@opts[:limit]).
    where(SQL::Identifier.new(rn) > o))
end