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 ROWCOUNT
an 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.
Best Answer
Most (all?)
set
statements take effect at the session level...... 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.