SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system features
    • Synapse Analytics to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Materialized views
      • Create table as select (CTAS)
      • Unsupported system features
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
  • Microsoft training - SQL Tran
    • Introduction
    • Licensing plans
    • Deploying and activating
    • Creating a new project
    • Exploring the Overview screen
    • Inside the Workspace screen
    • Code translation and emulations
      • Data type emulation
      • Case sensitivity emulation
      • Named parameters emulation
      • Result set limiting emulation
      • Merge statements emulation
      • Identity column emulation
      • Default values emulation
      • Detection of unsupported features
Powered by GitBook
On this page
  • Code example
  • Training video example
  1. Microsoft training - SQL Tran
  2. Code translation and emulations

Result set limiting emulation

PreviousNamed parameters emulationNextMerge statements emulation

Last updated 9 hours ago

In Synapse Analytics, the SET ROWCOUNT statement is used to limit the number of rows affected by subsequent operations such as SELECT, UPDATE, or DELETE. It applies in a procedural, stateful manner and remains in effect until explicitly reset.

However, Microsoft Fabric Warehouse does not support SET ROWCOUNT, and its usage can result in undefined behavior.

SQL Tran automatically handles this incompatibility by commenting out unsupported SET ROWCOUNT statements and rewriting affected queries to use the TOP clause instead.

This emulation preserves the original logic while ensuring compatibility with Fabric’s distributed architecture — eliminating the need for tedious manual rewrites and helping to maintain query correctness.

(For more information, see the following emulation reference page: )


Code example

Synapse Analytics:

CREATE PROCEDURE dbo.TestResultSetLimiting
AS
BEGIN
    SET ROWCOUNT 3;
    SELECT *
    FROM Person.Person
    ORDER BY BusinessEntityID DESC;
END;

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.TestResultSetLimiting
AS
BEGIN
    /* SQLTRAN FIX: SET ROWCOUNT 3; */
    SELECT TOP(3) *
    FROM Person.Person
    ORDER BY BusinessEntityID DESC;
END;

Training video example

In the example shown, a procedure uses SET ROWCOUNT at the beginning to limit the number of rows returned by a SELECT statement, and resets the limit with SET ROWCOUNT 0 at the end.

Since Fabric does not support SET ROWCOUNT, SQL Tran automatically comments out the unsupported statements in the target SQL and transforms the SELECT statement by injecting a TOP(@RowCount) clause to enforce the intended row limit.

This ensures that the result set limiting behavior remains intact after migration to Fabric, without requiring manual intervention.

Result set limiting
Result set limiting emulation in translated SQL