Azure Synapse @@ROWCOUNT Alternative

The @@ROWCOUNT is one of the important system variable that holds the count of the last executed statement in SQL server and Azure SQL Database. This variable is often used in conditional statements such as IF statement to execute and skip certain T-SQL statements. Azure Synapse does not support @@ROWCOUNT variable. In this article, we will check Azure Synapse @@ROWCOUNT Alternative.

The @@ROWCOUNT in SQL server returns the number of rows affected by the last statement.

Azure Synapse @@ROWCOUNT Alternative

When you are migrating existing applications such as a Teradata BTEQ scripts to Azure Synapse, you will find it hard to find ACTIVITY_COUNT alternative. The ACTIVITY_COUNT in the Teradata is the number of rows affected by the last SQL statement.

You can use Azure synapse system tables such as sys.dm_pdw_request_steps and sys.dm_pdw_exec_requests to get the number of rows affected by the last T-SQL statement.

Query to get @@ROWCOUNT in Azure Synapse Data warehouse

Following is the query to get @@ROWCOUNT in Azure Synapse data warehouse system.

SELECT top 1 row_count
FROM sys.dm_pdw_request_steps s, sys.dm_pdw_exec_requests r
Where r.request_id = s.request_id 
and row_count > -1
and r.[label] = 'QueryID2'
order by r.[end_time] desc;

You can notice that we are using a query label in the rowcount query.

Query label in Azure Synapse Data Warehouse

You can use the query label option in Azure to identify the queries in DMVs. The query label is one of the handy feature to track your queries.

Simply use OPTION in your query to label a query that you are executing on an Azure environment.

For example, consider following query with OPTION to label it.

SELECT *
FROM sys.tables
OPTION (LABEL = 'MyQueryLabel')
;

You can use this label to track the queries in Azure synapse data warehouse system tables.

For example, you will get above query when you query system table using provided label.

select command from sys.dm_pdw_exec_requests
where [label] = 'MyQueryLabel'
;

Azure Synapse @@ROWCOUNT Alternative Example

You can use query mentioned in previous steps to get @@rowcount. Following is the example to get the number of rows updated by the update statement.

UPDATE TEST
SET COL1 = 1
WHERE COL1 = 2
OPTION (LABEL = 'QUERYID2');

DECLARE @row_count int;

SELECT top 1 @row_count = row_count
FROM sys.dm_pdw_request_steps s, sys.dm_pdw_exec_requests r
Where r.request_id = s.request_id 
and row_count > -1
and r.[label] = 'QueryID2'
order by r.[end_time] desc;

print @row_count;

Let me know if you use different approach.

Hope this helps 🙂