Result set limiting emulation

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: Result set limiting)

---

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 emulation in translated SQL