Result set limiting

Emulation context

In Synapse Analytics, 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 Synapse Analytics.

---

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

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;

Result set limiting emulation in SQL Tran

---

Important notes

• 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.