SQLAlchemy code to to compile Microsoft SQL w/ latest version to support OFFSET/FETCH

sqla-ms-sql-compile.md

ChatGPT 5 solution after several attempts and corrections.

You're right—just setting _server_version_info isn’t enough; the MSSQL dialect caches feature flags at init. We need to refresh those flags so the compiler knows OFFSET/FETCH is supported.

Use this function (note the call to _setup_version_attributes) and make sure your statement has ORDER BY and .offset(...):

from typing import Optional, Tuple, Union

from sqlalchemy import select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.dialects import mssql


# --- ORM model setup (SQLAlchemy 2.x) ---

class Base(DeclarativeBase):
    pass


class Item(Base):
    __tablename__ = "items"
    id: Mapped[int] = mapped_column(primary_key=True, nullable=False, autoincrement=False)
    ci_type: Mapped[int] = mapped_column(nullable=False)
    serial_no: Mapped[Optional[str]] = mapped_column(nullable=True)
    inv_no: Mapped[Optional[float]] = mapped_column(nullable=True)
    product_key: Mapped[Optional[str]] = mapped_column(nullable=True)


# --- Compiler utility ---

def _parse_version(v: Union[str, Tuple[int, ...]]) -> Tuple[int, int]:
    if isinstance(v, tuple):
        t = v
    else:
        parts = [int(p) for p in str(v).split(".") if p.isdigit()]
        while len(parts) < 2:
            parts.append(0)
        t = tuple(parts[:2])
    return (t[0], t[1])


def compile_for_mssql(
    statement,
    mssql_version: Union[str, Tuple[int, ...]] = "15.0",  # 2019
    literal_binds: bool = True,
) -> str:
    """
    Compile a SQLAlchemy 2.x statement for SQL Server as if targeting a specific server version.

    - >= 11.0 (SQL Server 2012+) => OFFSET ... FETCH NEXT
    - < 11.0 => ROW_NUMBER() emulation
    - For limit-only queries, add .offset(0) to get FETCH NEXT (T-SQL requires OFFSET)
    - ORDER BY is required when using OFFSET/FETCH
    """
    dialect = mssql.dialect()

    ver = _parse_version(mssql_version)
    dialect.server_version_info = ver

    # Refresh version-gated feature flags so the compiler sees OFFSET/FETCH support
    if hasattr(dialect, "_setup_version_attributes"):
        dialect._setup_version_attributes()  # recalculates _supports_offset_fetch, etc.
    else:
        # Fallback for very old SQLAlchemy versions
        dialect._supports_offset_fetch = ver >= (11, 0)

    compiled = statement.compile(
        dialect=dialect,
        compile_kwargs={"literal_binds": literal_binds},
    )
    return str(compiled)


def print_compiled_for_mssql(statement, mssql_version="15.0", literal_binds=True) -> str:
    sql = compile_for_mssql(statement, mssql_version=mssql_version, literal_binds=literal_binds)
    print(sql)
    return sql


# --- Example usage ---

if __name__ == "__main__":
    # Must include ORDER BY and OFFSET for FETCH syntax
    stmt = (
        select(Item.id, Item.ci_type, Item.serial_no)
        .where(Item.ci_type == 2)
        .order_by(Item.id)
        .offset(20)
        .limit(10)
    )

    # 2012+ => OFFSET/FETCH
    print_compiled_for_mssql(stmt, mssql_version="11.0")
    # SELECT items.id, items.ci_type, items.serial_no
    # FROM items
    # WHERE items.ci_type = 2
    # ORDER BY items.id
    # OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

    # Pre-2012 => ROW_NUMBER() emulation
    print_compiled_for_mssql(stmt, mssql_version="10.50")
    # SELECT ...
    # WHERE mssql_rn > 20 AND mssql_rn <= 30

    # Limit-only? Add offset(0) to force FETCH
    stmt_limit_only = (
        select(Item.id, Item.product_key)
        .order_by(Item.id)
        .offset(0)
        .limit(5)
    )
    print_compiled_for_mssql(stmt_limit_only, mssql_version="16.0")
    # SELECT items.id, items.product_key
    # FROM items
    # ORDER BY items.id
    # OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

If you still see ROW_NUMBER():

• Ensure mssql_version >= 11.0.
• Ensure the statement has ORDER BY and uses .offset(...). For limit-only, use .offset(0).
• Some complex constructs may still use a subquery approach, but simple SELECTs like above will render OFFSET … FETCH NEXT.