Sql-server – Why does SET ROWCOUNT not reset after GO

sql servert-sql

According to the documentation for SCOPE_IDENTITY:

A scope is a module: a stored procedure, trigger,
function, or batch. Therefore, if two statements are in the same
stored procedure, function, or batch, they are in the same scope.

According to SQL Server Utilities Statements – GO:

GO is not a Transact-SQL statement; it is a command recognized by the
sqlcmd and osql utilities and SQL Server Management Studio Code
editor. SQL Server utilities interpret GO as a signal that they should
send the current batch of Transact-SQL statements to an instance of
SQL Server.

The current batch of statements is composed of all
statements entered since the last GO, or since the start of the ad hoc
session or script if this is the first GO.

Armed with that information I wrote (in SMSS) this T-SQL:

SET ROWCOUNT 1    -- Limit to 1 row returned

-- Returns 1 row as expected
SELECT *
FROM MyTable

GO -- Batch ends

-- Still returns one row
SELECT *
FROM MyTable

From my understanding SET ROWCOUNT (I know it is deprecated in DML but not SELECTs) is scoped to its module (from what I have seen examining other posts at any rate).

My questions:

  1. Is SET ROWCOUNT an exception to the 'module' rule mentioned above?

  2. Is it NOT scoped to the module at all?

  3. Is there a more complete definition of T-SQL scope?

I ran a query, then a stored procedure, that only sets rowcount to 4 (I picked 4 out of the blue) and then ran query again: so

SELECT * FROM MyTable 
EXE MySproc 
SELECT * FROM MyTable 

…returns all rows then 4 rows then all rows again so it is definitely scoped to at least sprocs.

Best Answer

Most (all?) set statements take effect at the session level...

provides several SET statements that change the current session handling

... with the normal set of caveats.

See SET Statements (Transact-SQL) for more details.


Quite a few of those set statements listed @ that link are obviously session-level settings, eg:

  • set dateformat
  • set fips_flagger
  • set indentity_insert
  • set statistics time/io
  • set nocount
  • set transaction isolation level

And by session-level I mean that they remain in place across batches/go statements.