NAME

DBIO::MSSQL::SQLMaker - MSSQL-specific SQL generation for DBIO

VERSION

version 0.900007

DESCRIPTION

DBIO::SQLMaker subclass for Microsoft SQL Server. Implements LIMIT/OFFSET via ROW_NUMBER() OVER() (the dialect SQL Server 2005+ supports) and overrides the default OVER() order expression to use (SELECT(1)) because MSSQL does not support an empty OVER() clause.

Used automatically by DBIO::MSSQL::Storage.

METHODS

_render_op_nulls

Renders the nulls_first / nulls_last ops SQL::Abstract builds for -nulls as CASE WHEN expr IS NULL THEN 0|1 ELSE 1|0 END ASC, expr [ASC|DESC]. The wrapped node is the sort entry expr ASC|DESC (or a bare expr).

_chunkify_order_by

The limit dialect (ROW_NUMBER() OVER()) takes the order_by apart in chunks, one per sort key, to select the order criteria into the inner query and to re-alias them in the OVER (ORDER BY ...). A -nulls entry is two sort keys here, so it is two chunks: the CASE WHEN expr IS NULL ... key (ASC) and the entry itself. As one chunk the comma inside it would end up in a select list as CASE ... ASC, col AS ORDER__BY__001. In the limited query the keys appear as ORDER__BY__001, ORDER__BY__002 in the OVER clause, each selected in the inner query.

_nulls_expr

The expression of a sort entry expr ASC|DESC (or a bare expr).

_render_nulls_key

Renders the leading sort key, CASE WHEN expr IS NULL THEN 0|1 ELSE 1|0 END ASC.

apply_limit

my $sql = $sqlmaker->apply_limit($sql, $rs_attrs, $rows, $offset);

MSSQL has no LIMIT/OFFSET keyword (before 2012's OFFSET ... FETCH). DBIO targets the broadly compatible ROW_NUMBER() OVER() windowing dialect: the query is wrapped in a derived table that numbers rows, then sliced by WHERE rno BETWEEN offset+1 AND offset+rows. Replaces the DBIx::Class sql_limit_dialect string dispatch.

NULLS FIRST / NULLS LAST

SQL Server has no NULLS FIRST / NULLS LAST; the -nulls key of an order_by entry is emulated. The sort key is preceded by a sort on whether it is NULL: { -desc => 'col', -nulls => 'first' } renders CASE WHEN [col] IS NULL THEN 0 ELSE 1 END ASC, [col] DESC, and -nulls => 'last' renders CASE WHEN [col] IS NULL THEN 1 ELSE 0 END ASC, [col] DESC -- the rows come back in the same order as on PostgreSQL. The leading key is always ASC, whatever the direction of the column. It is also in the OVER (ORDER BY ...) of the ROW_NUMBER() limit (this dialect builds no reversed middle or outer order) and in the order of a prefetch subquery. The column expression is rendered twice, so a bind inside it is bound twice, in order. There is no TOP limit dialect in this driver; the only TOP is the one that makes an ordered subselect legal ("ORDERED SUBSELECTS" in DBIO::MSSQL::Storage), which keeps the order_by as it is. See "_render_op_nulls".

SEE ALSO

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.