Does SQL Server Management Studio (or SQL Server) evaluate *all* expressions?

Question

Here's my configuration:

• I have a re-runnable batch script that I use to update my database.
• Inside of that batch script, I have code that says the following:
  • If Table 'A' doesn't exist, then create Table 'A' and insert rows into it.
• Later on in that batch script, I create an schemabound indexed view on that table.
  • And if you didn't already know, indexed views require specific client settings.

• Sometimes, when I re-run the script, that is after the table has been created, SQL Server Management Studio evaluates the "insert rows" code, which is protected by the 'If this table doesn't exist' code, and yields the following error:

  Msg 1934, Level 16, State 1, Line 15 INSERT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING, ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

• Please note: If someone were to try this INSERT statement in a vacuum, I would fully expect SSMS to generate this error.
  • But not when it's protected by a conditional block.

My Question:

Does the SSMS compiler evaluate all expressions, regardless of whether they will actually be executed?

Answer 1

Yes, it evaluates all of them,take a look at this

declare @i int
select @i =1

if @i = 1
begin
    declare @i2 int
    set @i2 = 5
end
else 
begin
    declare @i2 int
    set @i2 = 5
end

Msg 134, Level 15, State 1, Line 12 The variable name '@i2' has already been declared. Variable names must be unique within a query batch or stored procedure.

Another example with temp tables is here: What is deferred name resolution and why do you need to care?

your only way out would be to wrap it inside dynamic SQL

Answer 2

Note that most of the settings you mention are connection-level, i.e. in case you set/change them they stay in effect unless you close the connection or explicitly change their value.

Returning to your question. The error you mention looks like runtime error, i.e. the INSERT is actually being executed. It would be better if you could show your script (omitting details, but keeping batches).

Edit: it is not SSMS compiler that evaluates SQL you try to execute - it is SQL Server. What do you meant by 'evaluate'? Is it 'execute'? When you run a batch (which is what actually is being executed by a server), SQL Server first does syntactic analysis and throws error in case it finds any syntactic error, nothing is being executed at this point of time. In case syntax is ok, the server starts executing you batch.

Again, the error you show seems to be runtime - so I guess you'd carefully watch for the conditions and track what happens (or provide us more details about 'sometimes').