If you’ve been working with dedicated SQL pools in Synapse, you may have noticed that the SQL Server system variable @@RowCount is not supported. I will show you a workaround in synapse to get the same information.
First, use the query label option to give your query an identifier that you can use to track it:
SELECT
*
FROM Dim.Date
OPTION (LABEL = ‘MyQuery’)
You can then use DMVs to find your query using the specified label and get some meaningful information about your query:
To get the row count for your query, extend the above query against the DMVs to include the DMV sys.dm_pdw_request_steps and return only the row_count in the output:
There is additional logic shown here to help capture the latest instance of the query in case the query is run multiple times. This is a very simple alternative to the @@RowCount variable in SQL Server. You can use this as part of a bigger code in a stored procedure, or like I did in a synapse pipeline to get the row count for a query and then create/send an export only if the row count is more than 0.
And there you have it, @@RowCount in synapse dedicated SQL pool!
