CODEWITHSUNDEEP
sql-serversql-server-2008-r2
dhblah
dhblah

Reputation: 10151

I'm very confused about SQL Server batches

Somehow this works when I put it into SQL Server 2008 R2 Management Studio:

if 1 = 1
begin
  create table foo (
    p int
  );

  alter table foo
  add v int;

  insert into foo values (1, 2)

  select v from foo;
end
GO

This says invalid column name v:

  create table foo (
    p int
  );
  go;

if 1 = 1
begin
/*  create table foo (
    p int
  );*/

  alter table foo
  add v int;

  insert into foo values (1, 2)

  select v from foo;
end
GO

This works fine:

  create table foo (
    p int
  );
  go

if 1 = 1
begin
/*  create table foo (
    p int
  );*/

  alter table foo
  add v int;

  insert into foo values (1, 2)

--  select v from foo;
end
GO

Here is another example and it works fine too:

  create table foo (
    p int
  );
  go


if 1 = 1
begin
  alter table foo
  add v int not null;

  alter table foo
    add constraint PK_foo primary key(v);   
end
GO

It's clearly said in doc: http://technet.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

A table cannot be changed and then the new columns referenced in the same batch.

It seems that if table is created inside batch that's fine, or if you're creating constraint for added column in the batch that's fine too. But if you're issuing DML query (select) for that column, you'll get an error.

Why does it behave like this?

Upvotes: 3

Views: 112

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

In my case (SQL Server 2008 R2 Dev) examples #1, #3 and #4 works.

Starting from Martin Smith's comment (deferred compile) I created a SQL Trace which shows the cause(see Table 7: Recompilation reasons reported for the SP:Recompile event) for [re]compilation(s)

In all cases (#1, #3, #4), this batches works because of deferred compile ("Recompile because of DNR (Deferred Name Resolution). Object not found at compile time, deferred check to run time.").

The output for example #3 (with COMMIT: "I misprinted 3rd case, there was a commit instead of go"): enter image description here

The output for example #4: enter image description here

Upvotes: 4

Related Questions