THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Fun With Batches - Make Your Query Fail

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.

Published Wednesday, July 1, 2009 9:46 PM by John Paul Cook



Bernd Eckenfels said:

So, what is the reason for that? Is the batch analysing the existence of objects at parse time or something?

July 2, 2009 7:53 PM

John Paul Cook said:

Yes. Instead of executing the three statements, use the parse button in SSMS and you'll see the same error message.

July 5, 2009 7:27 PM
Anonymous comments are disabled

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a registered nurse recently completed the education to become a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement