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
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system objects
    • Synapse Analytics to Fabric Warehouse
      • Emulations
      • Limitations
    • 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
Powered by GitBook
On this page
  • Emulation context
  • Emulation strategy
  • Code example
  • Important notes
  1. Emulation Scenarios
  2. SQL Server to Fabric Warehouse

Result set limiting

Emulation context

In SQL Server, the SET ROWCOUNT statement can be used to limit the number of rows affected by subsequent SELECT, UPDATE, DELETE, or INSERT statements. This affects execution until explicitly reset using SET ROWCOUNT 0. This row-limiting behavior is procedural and stateful, affecting all qualifying statements that follow within the batch, stored procedure, or function. It is often used for batching, testing, or pagination logic.

For example:

SET ROWCOUNT 5;
SELECT * FROM Person.Person;

This would return only 5 rows from the Person table, even if the query would otherwise return more.

However, Microsoft Fabric Warehouse does not support the SET ROWCOUNT statement. It is listed among the unsupported features in Fabric’s official documentation. Use of this command in Fabric may result in undefined behavior or silent failure and must be removed or rewritten when migrating code from SQL Server.


Emulation strategy

SQL Tran emulates SET ROWCOUNT by removing the unsupported statement and rewriting each affected query using compatible Fabric syntax. The emulation logic includes:

  • Commenting out the original SET ROWCOUNT statements using /* SQLTRAN FIX */.

  • Injecting a TOP(n) clause into affected SELECT, UPDATE, DELETE, and INSERT ... SELECT statements to replicate the intended row-limiting behavior.

  • Applying the transformation sequentially, based on the most recent SET ROWCOUNT value in effect.

  • Choosing the more restrictive value if both SET ROWCOUNT and TOP(n) are present in a statement. SQL Tran does not emit a warning when it overrides existing TOP values, so the translated limit may differ from the original.

  • Stopping transformation after encountering SET ROWCOUNT 0, allowing all subsequent statements to execute without row limits.

  • Inserting traceable comments next to all changes for auditability.


Code example

SQL Server:

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;

Important notes

  • Incompatibility with OFFSET/FETCH syntax: Statements that use OFFSET/FETCH cannot also include TOP. In such cases, SQL Tran may still inject TOP(n), resulting in invalid SQL. SQL Tran does not emit any warning or comment when producing such combinations. This scenario requires manual review.

  • Disabling row-limit behavior: When SET ROWCOUNT 0 is encountered, SQL Tran stops applying row-limiting and does not inject any TOP(n) into subsequent statements.

  • Multiple row-limit statements: When multiple SET ROWCOUNT statements are present in the source SQL, each is handled independently. SQL Tran rewrites each affected statement using the most recent active value, applying TOP(n) until the limit is reset.

PreviousNamed procedure parametersNextMERGE statements

Last updated 4 days ago

Result set limiting emulation in SQL Tran