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:
Is
SET ROWCOUNTan exception to the 'module' rule mentioned above?Is it NOT scoped to the module at all?
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.
