Czy SQL Server zezwala (uwidacznia) DDL wewnątrz transakcji na transakcję przed zatwierdzeniem?

W PostgreSQL mogę utworzyć tabelę z niektórymi danymi testowymi, a następnie w ramach transakcji migrować ją do nowej kolumny innego typu, co spowoduje jedno przepisanie tabeli COMMIT,

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

Śledzony przez,

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

Jednak to samo w SQL Server Microsoftu wydaje się generować błąd. Porównaj to działające skrzypce db , gdzie polecenie ADD(kolumna) znajduje się poza transakcją,

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

na to skrzypce db, które nie działa,

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

Ale zamiast błędów

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

Czy jest tak, że aby transakcja była widoczna, w odniesieniu do DDL, zachowuj się jak PostgreSQL?

Ogólnie rzecz biorąc, nie. SQL Server kompiluje całą partię w bieżącym zakresie przed wykonaniem, więc muszą istnieć odwołania do encji (rekompilacje na poziomie instrukcji mogą się również zdarzyć później). Głównym wyjątkiem jest rozpoznawanie nazw odroczonych, ale dotyczy to tabel, a nie kolumn:

Odroczonego rozpoznawania nazw można używać tylko wtedy, gdy odwołujesz się do nieistniejących obiektów tabeli. Wszystkie inne obiekty muszą istnieć w momencie tworzenia procedury składowanej. Na przykład, gdy odwołujesz się do istniejącej tabeli w procedurze składowanej, nie możesz wyświetlić nieistniejących kolumn dla tej tabeli.

Typowe obejścia obejmują dynamiczny kod (jak w odpowiedzi Joe ) lub rozdzielenie DML i DDL na osobne partie.

W tym konkretnym przypadku możesz również napisać:

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;

Nadal nie będziesz mieć dostępu do kolumny o zmienionej nazwie bw tej samej partii i zakresie, ale wykona to zadanie.

Jeśli chodzi o SQL Server, istnieje szkoła myślenia, która mówi, że mieszanie DDL i DML w transakcji nie jest świetnym pomysłem. W przeszłości występowały błędy, w wyniku których niepoprawne rejestrowanie i nieodwracalna baza danych. Niemniej ludzie to robią, zwłaszcza przy tymczasowych stołach. Może to spowodować powstanie trudnego do naśladowania kodu.

Czy tego szukasz?

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;

Do stwierdzenia „generalnie nie” w odpowiedzi Paula White'a, mam nadzieję, że poniższa mam bezpośrednią odpowiedź na pytanie, ale służy także do pokazania systemowych ograniczeń takiego procesu i odsuwa cię od metod, które nie nadają się do łatwego zarządzania i ujawnienia ryzyko

To może być wymienione wiele razy nie robić DDL zmienia ten sam czas robicie DML. Dobre programowanie oddziela te funkcje, aby zachować wsparcie i uniknąć zmian ciągów spaghetti.

I jak zwięźle zauważył Paul, SQL Server działa partiami .

Teraz, dla tych, którzy wątpią w to, to prawdopodobnie nie działa w twoim przypadku, ale niektóre wersje, takie jak 2017, mogą faktycznie działać! Oto dowód:

[KOD TESTOWY - MOŻE nie działać w wielu wersjach 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

[WNIOSEK]

Więc tak, możesz wykonać DDL i DML w tej samej partii dla niektórych wersji lub poprawek SQL Server, jak wskazuje @AndriyM - dbfiddle na SQL 2017 wskazuje, ale nie wszystkie DML są obsługiwane i nie ma gwarancji, że zawsze tak będzie. Jeśli to działa, może to być aberracja Twojej wersji SQL Server, co może powodować dramatyczne problemy podczas łatania lub migracji do nowych wersji.

• Dodatkowo, ogólnie rzecz biorąc twój projekt powinien przewidywać zmiany. Rozumiem obawy związane z modyfikowaniem / dodawaniem kolumn w tabeli, ale możesz odpowiednio zaprojektować to w partiach.

[DODATKOWY KREDYT]

Jeśli chodzi o instrukcję EXISTS, jak stwierdził Paul, istnieje wiele innych sposobów sprawdzania poprawności kodu przed przejściem do następnego kroku w kodzie.

• Instrukcja EXISTS może pomóc w utworzeniu kodu, który działa na wszystkich wersjach SQL Server
• Jest to funkcja logiczna, która umożliwia złożone kontrole w jednej instrukcji