Result set limiting emulation
Last updated
Last updated
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: )
Synapse Analytics:
Fabric Warehouse (generated by SQL Tran):
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.