Module Sequel::MSSQL::DatasetMethods
In: lib/sequel/adapters/shared/mssql.rb

Methods

Constants

BOOL_TRUE = '1'.freeze
BOOL_FALSE = '0'.freeze
SELECT_CLAUSE_ORDER = %w'with limit distinct columns from table_options join where group order having compounds'.freeze
TIMESTAMP_FORMAT = "'%Y-%m-%d %H:%M:%S'".freeze
WILDCARD = LiteralString.new('*').freeze
CONSTANT_MAP = {:CURRENT_DATE=>'CAST(CURRENT_TIMESTAMP AS DATE)'.freeze, :CURRENT_TIME=>'CAST(CURRENT_TIMESTAMP AS TIME)'.freeze}

Public Instance methods

MSSQL uses + for string concatenation

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 154
154:       def complex_expression_sql(op, args)
155:         case op
156:         when '||''||'
157:           super(:+, args)
158:         else
159:           super(op, args)
160:         end
161:       end

MSSQL doesn‘t support the SQL standard CURRENT_DATE or CURRENT_TIME

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 164
164:       def constant_sql(constant)
165:         CONSTANT_MAP[constant] || super
166:       end

When returning all rows, if an offset is used, delete the row_number column before yielding the row.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 170
170:       def each(&block)
171:         @opts[:offset] ? super{|r| r.delete(row_number_column); yield r} : super(&block)
172:       end

MSSQL uses the CONTAINS keyword for full text search

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 175
175:       def full_text_search(cols, terms, opts = {})
176:         filter("CONTAINS (#{literal(cols)}, #{literal(terms)})")
177:       end

MSSQL uses a UNION ALL statement to insert multiple values at once.

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 180
180:       def multi_insert_sql(columns, values)
181:         values = values.map {|r| "SELECT #{expression_list(r)}" }.join(" UNION ALL ")
182:         ["#{insert_sql_base}#{source_list(@opts[:from])} (#{identifier_list(columns)}) #{values}"]
183:       end

Allows you to do .nolock on a query

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 186
186:       def nolock
187:         clone(:table_options => "(NOLOCK)")
188:       end

MSSQL uses [] to quote identifiers

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 191
191:       def quoted_identifier(name)
192:         "[#{name}]"
193:       end

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 CTE which is selected from.

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

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 205
205:       def select_sql
206:         return super unless o = @opts[:offset]
207:         raise(Error, 'MSSQL requires an order be provided if using an offset') unless order = @opts[:order]
208:         dsa1 = dataset_alias(1)
209:         dsa2 = dataset_alias(2)
210:         rn = row_number_column
211:         unlimited.
212:           unordered.
213:           from_self(:alias=>dsa2).
214:           select{[WILDCARD, ROW_NUMBER(:over, :order=>order){}.as(rn)]}.
215:           from_self(:alias=>dsa1).
216:           limit(@opts[:limit]).
217:           where(rn > o).
218:           select_sql
219:       end

Microsoft SQL Server does not support INTERSECT or EXCEPT

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 222
222:       def supports_intersect_except?
223:         false
224:       end

MSSQL does not support IS TRUE

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 227
227:       def supports_is_true?
228:         false
229:       end

MSSQL supports timezones in literal timestamps

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 232
232:       def supports_timestamp_timezones?
233:         true
234:       end

MSSQL 2005+ supports window functions

[Source]

     # File lib/sequel/adapters/shared/mssql.rb, line 237
237:       def supports_window_functions?
238:         true
239:       end

[Validate]