Início / dba / Perguntas / 237935
Accepted
Evan Carroll
Asked: 2019-05-12 17:42:45 +0800 CST

O SQL Server permite (torna visível) DDL dentro de uma transação para a transação antes da confirmação?

  • 772

No PostgreSQL eu posso criar uma tabela com alguns dados de teste e, em seguida, em uma transação migrá-la para uma nova coluna de um tipo diferente, resultando em uma reescrita de tabela COMMIT,

CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);

Seguido por,

BEGIN;
  ALTER TABLE foo ADD COLUMN b varchar;
  UPDATE foo SET b = CAST(a AS varchar);
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

No entanto, essa mesma coisa no SQL Server da Microsoft parece gerar um erro. Compare este db fiddle de trabalho , onde o ADDcomando (coluna) está fora da transação,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
COMMIT;

-- txn2
BEGIN TRANSACTION;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

para este violino db que não funciona,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

Mas em vez disso erros

Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.

Existe alguma maneira de tornar esta transação visível, no que diz respeito ao DDL, se comportar como o PostgreSQL?

sql-server transaction

3 respostas

  1. Best Answer

    De um modo geral, não. O SQL Server compila todo o lote no escopo atual antes da execução, portanto, as entidades referenciadas precisam existir (recompilações em nível de instrução também podem ocorrer posteriormente). A principal exceção é a resolução de nomes adiada , mas isso se aplica a tabelas, não a colunas:

    A resolução de nomes adiada só pode ser usada quando você faz referência a objetos de tabela inexistentes. Todos os outros objetos devem existir no momento em que o procedimento armazenado é criado. Por exemplo, quando você faz referência a uma tabela existente em um procedimento armazenado, não pode listar colunas inexistentes para essa tabela.

    As soluções alternativas comuns envolvem código dinâmico (como na resposta de Joe ) ou separando o DML e o DDL em lotes separados.

    Para este caso específico, você também pode escrever:

    BEGIN TRANSACTION;

    ALTER TABLE dbo.foo
        ALTER COLUMN a varchar(11) NOT NULL
        WITH (ONLINE = ON);

    EXECUTE sys.sp_rename
        @objname = N'dbo.foo.a',
        @newname = N'b',
        @objtype = 'COLUMN';

COMMIT TRANSACTION;

    Você ainda não poderá acessar a coluna renomeada bno mesmo lote e escopo, mas ela faz o trabalho.

    Com relação ao SQL Server, existe uma escola de pensamento que diz que misturar DDL e DML em uma transação não é uma boa ideia. Houve erros no passado em que isso resultou em log incorreto e um banco de dados irrecuperável. No entanto, as pessoas fazem isso, especialmente com mesas temporárias. Isso pode resultar em um código bastante difícil de seguir.

    • 17

  2. É isso que você está procurando?

    BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  EXEC sp_executesql N'UPDATE foo SET b = CAST( a AS varchar )';
  ALTER TABLE foo DROP COLUMN a;
COMMIT;
    • 12

  3. Para a declaração "geralmente não" da resposta de Paul White, espero que o seguinte ofereça uma resposta direta à pergunta, mas também sirva para mostrar as limitações sistêmicas de tal processo e o afasta de métodos que não facilitam o gerenciamento e expõem riscos.

    Pode ser mencionado muitas vezes para não fazer alterações no DDL ao mesmo tempo em que você está fazendo o DML. Uma boa programação separa essas funções para manter a capacidade de suporte e evitar alterações de strings de espaguete.

    E, como Paul apontou sucintamente, o SQL Server funciona em lotes .

    Agora, para aqueles que duvidam que isso funcione, provavelmente não funciona na sua instância, mas algumas versões como 2017 podem realmente funcionar! Aqui está a prova: insira a descrição da imagem aqui

    [CÓDIGO DE TESTE - PODE não funcionar em muitas versões do SQL Server]

    USE master
GO
CREATE TABLE foo (a VARCHAR(11) )
GO
BEGIN TRANSACTION;
    INSERT INTO dbo.foo (a)
    VALUES ('entry')
/*****
[2] Check Values
*****/
    SELECT a FROM dbo.foo
/*****
[3] Add Column
*****/
    ALTER TABLE dbo.foo
        ADD b VARCHAR(11)
/*****
[3] Insert value into this new column in the same batch
-- Again, this is just an example. Please do not do this in production
*****/
    IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        INSERT INTO dbo.foo (b)
        VALUES ('d')
COMMIT TRANSACTION;
/*****
[4] SELECT outside transaction
-- this will fail
*****/
    --IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
    --      AND name = 'b')
    --  SELECT b FROM dbo.foo
-- this will work...but a SELECT * ???
IF EXISTS (SELECT * FROM sys.columns WHERE object_ID('foo') = object_id
            AND name = 'b')
        SELECT * FROM dbo.foo

DROP TABLE dbo.foo

    [CONCLUSÃO]

    Então, sim, você pode executar DDL e DML no mesmo lote para determinadas versões ou patches do SQL Server como @AndriyM - dbfiddle no SQL 2017 aponta, mas nem todos os DML são suportados e não há garantia de que isso sempre será o caso. Se funcionar, pode ser uma aberração da sua versão do SQL Server e isso pode causar problemas dramáticos à medida que você corrige ou migra para novas versões.

    • Além disso, em geral, seu design deve antecipar as mudanças. Entendo que as preocupações de modificar/adicionar colunas podem ter em uma tabela, mas você pode projetar adequadamente em torno disso em lotes.

    [CRÉDITO EXTRA]

    Quanto à instrução EXISTS, como Paul afirmou, existem muitos outros meios para validar o código antes de passar para a próxima etapa do seu código.

    • A instrução EXISTS pode ajudá-lo a criar um código que funcione em todas as versões do SQL Server
    • É uma função booleana que permite verificações complexas em uma instrução
    • 2

relate perguntas