3

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?

dhblah
  • 9,751
  • 12
  • 56
  • 92
  • Looking [here](http://stackoverflow.com/a/7426382/1114171), sql server will check columns when the query (batch) is compiled. As column `v` doesn't exist at that time then it can't be referenced. – T I Dec 25 '13 at 14:18
  • That's fine as batch mustn't be fully compiled prior it's execution, see sample explaination here: http://technet.microsoft.com/en-us/library/ms175502(v=sql.105).aspx – dhblah Dec 25 '13 at 14:25
  • 1
    In the first case because the table doesn't exist at all the statements referencing it are subject to deferred compile. So `insert into foo values (1, 2)` is eventually compiled after the column is added. I can't reproduce your third case. – Martin Smith Dec 25 '13 at 14:27
  • I misprinted 3rd case, there was a commit instead of go. With go it works fine. that's strange. – dhblah Dec 25 '13 at 14:32

1 Answers1

4

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

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57