I am learning through the EF Core through the tutorials over here and I come across the following statements being used in the examples of the T-SQL queries:
SET NOCOUNT ON; Meaning (from here):
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
SELECT @@ROWCOUNT; Meaning (from here)
Returns the number of rows affected by the last statement.
The 1 and 2 are used simultaneously. And that is confusing for me. For instance, here:
exec sp_executesql N'SET NOCOUNT ON; UPDATE [Books] SET [AuthorId] = @p0 WHERE [BookId] = @p1; SELECT @@ROWCOUNT; ',N'@p1 int,@p0 int',@p1=4,@p0=1
I can not understand why would someone want to turn off the effect of the 2 with the help of 1 if it is possible to avoid the effect of the 2 by just not adding it into the query. I.e. the following query will do exactly the same as the above one:
exec sp_executesql N'UPDATE [Books] SET [AuthorId] = @p0 WHERE [BookId] = @p1; ',N'@p1 int,@p0 int',@p1=4,@p0=1
Am I missing something here or what is the purpose of using both the
SET NOCOUNT ON and the
SELECT @@ROWCOUNT simultaneously?
SET NOCOUNT ON stops the results from being printed on the console (screen).
The use of
@@ROWCOUNT captures the row count as a parameter in T-SQL and makes it possible to use it for further processing. For instance, you could have conditional logic to do something if no rows are updated.
SET NOCOUNT is often used is suppress the
DONE_IN_PROC TDS protocol messages (row counts) SQL Server would otherwise return to the client over the underlying TDS protocol stream. These messages can cause issues when using some SQL Server APIs unless the application is specifically coded to handle them (e.g. by invoking
ADODB.Recordset.NextRecordSet) to ensure subsequent statements in the batch are executed and result sets returned. Consequently, use of
SET NOCOUNT ON has become a common practice with SQL Server unless the row counts are actually needed by the application.
An additional benefit, called out in the doc reference in your question, is a performance benefit in cases where the batch contains many SQL statements.
SET NOCOUNT eliminates the overhead of returning the messages that would be discarded anyway.