THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 01, 2009 9:46 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement