Comportamento extraordinariamente inconsistente do Sql Server em relação às definições de tabela em cache

Pré-aviso: Esta pergunta pode não fazer sentido à primeira vista, embora qualquer um possa jogar tentando isso em sua instância local do Sql Server.

Com tudo isso em mente, considere dois 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');

E a estranheza que é

WTF.sql (um script onde estou basicamente reconstruindo a tabela e renomeando Ca coluna Cxe adicionando uma nova linha a ela.) :

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');

Estou plenamente ciente da predileção muito irritante do Sql Server de às vezes não conseguir ver os metadados atualizados que muitas vezes exigem a implementação de um para Goforçá-lo a "atualizar" sua visão do esquema subjacente para o compilador.

No entanto, o que me levou a um loop absoluto foi o analisador aceitar a inserção nº 1 com sucesso, mas rejeitar a inserção nº 2 com:

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

Ambos usam exatamente a mesma lista de colunas, mas apenas uma é considerada aceitável em tempo de compilação. Vou arriscar um palpite e dizer que o Sql vê que o primeiro Insertestá usando uma tabela temporária e, portanto, pode estar olhando para os metadados em um nível diferente, tendo que fazer um Table Scane alimentá-lo no Table Insertoperador, em vez de ser capaz de usar uma inserção direta para a inserção #2 .

Agora, a boa notícia é que tenho um trabalho muito fácil:

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;

O que funciona bem. Não tenho certeza porque, porque certamente ainda cai encontrado se a regra sobre alterações de tabela e usos no mesmo lote, mas estou divagando…

E ainda ... tendo funcionado, o Sql Server fica ainda mais confuso porque, se alguém tentar executar novamente o original Create.Sqle WTF.sql na mesma sessão que a usada para executarWorkaround.sql , falhará ao analisar o Insert # 1 (o que é mais esperado) .

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

Se alguém executar novamente Create.sqle escolher uma nova sessão para executar WTF.sql, ela voltará a falhar na linha 30.

Isso me deixa totalmente intrigado. Como eu disse, tenho um trabalho ao redor para não ser assaltado, mas estou totalmente fascinado com o motivo de estar observando esse comportamento.

Alguém aí com alguma ideia?

Isso foi testado no Sql Server 2019 e no Sql Server 2022 - usando o SSMS e o Azure Data Studio .

Pelo que vale a pena, eu tenho um Sql Fiddle , mas não é muito útil porque aparentemente não gosta de tabelas #Temp.

NB : Há uma razão maior pela qual estou reconstruindo a tabela - apenas simplifiquei minhas etapas de código aqui depois que isso me atingiu no traseiro esta noite.