Extraordinarily inconsistent behavior from Sql Server regarding cached table definitions

Question

Pre-warning: This question may not make sense at first blush, hopefully though anyone can play along by trying this out on their local Sql Server instance.

With all that in mind, consider two scripts:

Create.sql:

Drop Table If Exists dbo.MyMysteryTable;

Create Table dbo.MyMysteryTable
        (
             A                                          SmallInt        Not Null
            ,B                                          VarChar(32)     Not Null
            ,C                                          VarChar(128)    Not Null
            ,D                                          SmallDateTime   Null
            ,E                                          SmallInt        Not Null
            ,F                                          Char(1)         Not Null
            ,G                                          VarChar(16)     Not Null
        );

Insert   dbo.MyMysteryTable(A, B, C, D, E, F, G)
  Values (1, 'B1', 'C1', Null, 1, '1', 'G1')
        ,(2, 'B2', 'C2', Null, 2, '2', 'G2');

And the oddness that is

WTF.sql (a script where I'm basically rebuilding the table and renaming column C to Cx and then adding a new row to it.):

Drop Table If Exists #Temp; 
Select   A
        ,B
        ,C
        ,D
        ,E
        ,F
        ,G
  Into   #Temp
  From   dbo.MyMysteryTable;

Drop Table If Exists dbo.MyMysteryTable;
Create Table dbo.MyMysteryTable
(
     A                                          SmallInt        Not Null
    ,B                                          VarChar(32)     Not Null
    ,Cx                                         VarChar(128)    Not Null
    ,D                                          SmallDateTime   Null
    ,E                                          SmallInt        Not Null
    ,F                                          Char(1)         Not Null
    ,G                                          VarChar(16)     Not Null
);

-- Insert #1
Insert   dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Select   *
  From   #Temp;

-- Insert #2
Insert   dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
  Values (3, 'B3', 'C3', Null, 3, '3', 'G3');

I'm fully aware of Sql Server's very annoying predilection to sometimes fail to see updated metadata that quite often requires one to implement a Go to force it to "refresh" it's view of the underlying schema for the compiler.

However what threw me for an absolute loop was the parser accept Insert #1 succeed, but to reject Insert #2 with:

Msg 207, Level 16, State 1, Line 30
Invalid column name 'Cx'.

Both use the exact same column list, but only one is seen to be acceptable at compile time. I'm going to hazard a guess and say that Sql see's that the first Insert is using a temporary table, and so it may be looking at the metadata at a different level having to do a Table Scan and feed that into the Table Insert operator, as opposed to being able to use a direct insert for Insert #2.

Now the good news is that I have a very easy work around:

Workaround.sql:

Drop Table If Exists #Temp; 
Select   A
        ,B
        ,C
        ,D
        ,E
        ,F
        ,G
  Into   #Temp
  From   dbo.MyMysteryTable;

Drop Table If Exists dbo.MyMysteryTable;
Create Table dbo.MyMysteryTable
(
     A                                          SmallInt        Not Null
    ,B                                          VarChar(32)     Not Null
    ,Cx                                         VarChar(128)    Not Null
    ,D                                          SmallDateTime   Null
    ,E                                          SmallInt        Not Null
    ,F                                          Char(1)         Not Null
    ,G                                          VarChar(16)     Not Null
);

-- Insert #2
Insert   #Temp
  Values (3, 'B3', 'C3', Null, 3, '3', 'G3');
-- Insert #1
Insert   dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Select   *
  From   #Temp;

Which works fine. I’m not sure why, because surely it still falls found if the rule about table changes and usages in the same batch, but I digress…

And yet...having worked, Sql Server gets further confused that if one then tries to rerun the original Create.Sql and WTF.sql in the same session as the one used to execute Workaround.sql, then it fails to parse Insert #1 (which is more to be expected).

Msg 207, Level 16, State 1, Line 25
Invalid column name 'Cx'.

If one reruns Create.sql and then picks a new session to run WTF.sql then it'll be back to failing on line 30.

This has me totally puzzled. Like I say, I have a work around so i'm not being held up, but I am totally fascinated as to why I'm observing this behavior.

Anyone out there with any ideas?

This has been tested against both Sql Server 2019 and Sql Server 2022 - using both SSMS and Azure Data Studio.

For what it's worth, I do have a Sql Fiddle but it's not very helpful because it apparently doesn't like #Temp tables.

NB: There's a bigger reason why I'm rebuilding the table - I just simplified my code steps here after this bit me on the rear end tonight.

Answer 1

The reason that the following succeeds (if you comment out Insert 2)...

-- Insert #1
Insert   dbo.MyMysteryTable(A, B, Cx, D, E, F, G)
Select   *
  From   #Temp;

Is because #Temp does not exist when the batch is compiled so the statement is subject to deferred compile.

By the time the statement is recompiled (just before execution) the column exists in MyMysteryTable so there is no problem.

For statements like this that both would cause compilation errors due to referencing non existent columns in existing objects and are eligible for deferred compile as they also reference entirely non existent objects I've previously found it a bit "hit and miss" whether you will get the compile time error before compilation is deferred.

e.g. below the reference to the non existent table is sufficient to "save" statement 1 but statement 2 still throws a compile time error despite it.

DROP TABLE IF EXISTS dbo.T1, #T1;

CREATE TABLE dbo.T1(C1 INT);

GO

CREATE TABLE #T1(C1 INT);

ALTER TABLE dbo.T1 ADD C2 INT;

/* Statement 1

SELECT T1.C2
FROM   dbo.T1
WHERE  NOT EXISTS(SELECT * FROM   #T1) 
*/


/* Statement 2

SELECT A.C2
FROM   dbo.T1 A
       JOIN dbo.T1 B
         ON A.C2 = B.C2
WHERE  NOT EXISTS(SELECT * FROM   #T1) 

*/