It's a common practice for people who write T-SQL scripts to test each
statement one at a time before finally running the entire script. This can lead
to unexpected surprises. When each statement in a script is executed one at a
time, each is separate batch. If there are no GO statements, the entire script
executed as whole is a single batch. Let's look at some code.
/*
Execute the following three queries as a batch.
The third query will fail. The error message
says #test exists even though the previous query was a drop
table.
But if you execute all three queries one at
time (assuming #test doesn't exist when you start),
all three queries will
succeed.
Or put a go after each statement and all
three will work. */
select 1 as a into #test;
drop table #test;
select
1 as a into
#test;
Most people looking at the code expect that if all three statements were
highlighted and F5 pressed, all three statements would execute successfully
without any error messages. Instead, the following error appears:
Msg
2714, Level 16, State 1, Line 3
There
is already an object named '#test' in the database.
The third statement fails. But if each statement is highlighted and executed
one by one, each statement succeeds. For more fun, execute this statement by
itself after executing the first three statements as a single batch.
select * from #test;
Msg
208, Level 16, State 0, Line 1
Invalid
object name '#test'.
Now you've seen error messages saying that your temporary table exists and
that it doesn't exist! It makes me think of Schrödinger's
cat. Schrödinger had it easy - his quantum mechanics theories weren't
complicated by T-SQL. Now change the code to use a permanent
table instead of a temporary table and everything works in a single batch:
select 1 as a into test;
drop table test;
select
1 as a into
test;
It's important to understand how things work. What do we do when a complete
script fails? We usually start executing it a statement at a time until we find
the problem. In a scenario like this, no problem will be found when executing a
single statement at a time. When testing, it's important to understand that
executing a statement at a time is not always predictive of executing all
statements as a single batch.