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:
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 affectedSELECT
,UPDATE
,DELETE
, andINSERT ... 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
andTOP(n)
are present in a statement. SQL Tran does not emit a warning when it overrides existingTOP
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:
Fabric Warehouse (generated by SQL Tran):
Important notes
Incompatibility with
OFFSET
/FETCH
syntax: Statements that useOFFSET
/FETCH
cannot also includeTOP
. In such cases, SQL Tran may still injectTOP(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 anyTOP(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, applyingTOP(n)
until the limit is reset.
Last updated