Sql server® 2012 t-sql fundamentals

a Batch as a Unit of resolution

A batch is a unit of resolution. This means that checking the existence of objects and columns hap-

pens at the batch level. Keep this fact in mind when you are designing batch boundaries. When you 

apply schema changes to an object and try to manipulate the object data in the same batch, SQL 

Server might not be aware of the schema changes yet and fail the data manipulation statement with 

a resolution error. I’ll demonstrate the problem through an example and then recommend best prac-

tices.

Run the following code to create a table called T1 in the current database, with one column 

called col1.

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; 

CREATE TABLE dbo.T1(col1 INT);

Next, try to add a column called col2 to T1 and query the new column in the same batch.

ALTER TABLE dbo.T1 ADD col2 INT; 

SELECT col1, col2 FROM dbo.T1;

Even though the code might seem to be perfectly valid, the batch fails during the resolution phase 

with the following error.

Msg 207, Level 16, State 1, Line 2 

Invalid column name 'col2'.

At the time the SELECT statement was resolved, T1 had only one column, and the reference to the 

col2 column caused the error. One best practice you can follow to avoid such problems is to separate 

DDL and DML statements into different batches, as in the following example.

ALTER TABLE dbo.T1 ADD col2 INT; 

GO 

SELECT col1, col2 FROM dbo.T1;