5

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.

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.