„Unikaj zapisywania zmian, które wymagają ponownego utworzenia tabeli” negatywne skutki

Preambuła

Modyfikowałem dziś kolumnę w SQL Server 2008, zmieniając typ danych z czegoś w rodzaju waluty (18,0) na (19,2).

Wystąpił błąd „Wprowadzone zmiany wymagają usunięcia i ponownego utworzenia poniższych tabel” z SQL Server.

Zanim spróbujesz odpowiedzieć, przeczytaj następujące informacje:

Wiem już, że istnieje opcja w Narzędzia ► Opcje ► Projektant ► Projektanci tabel i baz danych ► Odznacz pole „Zapobiegaj zapisywaniu zmian wymagających ponownego utworzenia tabeli”. ... więc nie odpowiadaj na to!

Rzeczywiste pytanie

Moje aktualne pytanie dotyczy czegoś innego, jak następuje:

Czy są jakieś negatywne skutki / możliwe wady tego działania?

Czy tabela faktycznie jest upuszczana i odtwarzana automatycznie, gdy to pole nie jest zaznaczone?

Jeśli tak, to czy kopia tabeli jest w 100% dokładną repliką tabeli źródłowej?

Tabela jest usuwana i ponownie tworzona tylko w przypadkach, gdy jest to jedyny sposób, w jaki SQL Server Management Studio został zaprogramowany, aby wiedzieć, jak to zrobić.

Z pewnością istnieją przypadki, w których będzie to robić, gdy nie będzie to konieczne, ale będą też przypadki, w których zmiany wprowadzone w Management Studio nie zostaną upuszczone i ponownie utworzone, ponieważ nie musi.

Problem polega na tym, że wyliczenie wszystkich przypadków i określenie, po której stronie linii spadają, będzie dość nużące.

Właśnie dlatego lubię używać ALTER TABLEw oknie zapytania, zamiast projektantów wizualnych, którzy ukrywają to, co robią (i szczerze mówiąc, mają błędy) - wiem dokładnie, co się wydarzy i mogę przygotować się na przypadki, w których jedyna możliwość polega na upuszczeniu i ponownym utworzeniu tabeli (która jest o kilka liczb mniejsza niż częstotliwość, z jaką SSMS to robi).

Narzędzia -> Opcje -> Węzeł projektantów -> Odznacz „ Zapobiegaj zapisywaniu zmian wymagających odtworzenia tabeli ”.

Odwołanie - wyłączenie tej opcji może pomóc uniknąć ponownego tworzenia tabeli, a także może doprowadzić do utraty zmian. Załóżmy na przykład, że włączono funkcję śledzenia zmian w SQL Server 2008, aby śledzić zmiany w tabeli. Podczas wykonywania operacji powodującej ponowne utworzenie tabeli pojawia się komunikat o błędzie wymieniony w sekcji „Symptomy”. Jeśli jednak wyłączysz tę opcję, istniejące informacje o śledzeniu zmian zostaną usunięte po ponownym utworzeniu tabeli. Dlatego firma Microsoft zaleca, aby nie obejść tego problemu, wyłączając tę ​​opcję.

SQL Server upuszcza i odtwarza tabele tylko wtedy, gdy:

• Dodaj nową kolumnę
• Zmień ustawienie Zezwalaj na wartości zerowe dla kolumny
• Zmień kolejność kolumn w tabeli
• Zmień typ danych kolumny

Korzystanie z ALTER jest bezpieczniejsze, ponieważ w przypadku utraty metadanych podczas ponownego tworzenia tabeli dane zostaną utracone.

Tak, są z tego negatywne skutki:

Jeśli wypiszesz zmianę zablokowaną przez tę flagę, otrzymasz coś w rodzaju skryptu poniżej (wszystko zmieniam kolumnę ID w Contact w automatycznie obciążoną kolumnę TOŻSAMOŚCI, ale tabela ma zależności). Zwróć uwagę na potencjalne błędy, które mogą wystąpić podczas działania następujących programów:

1. Nawet Microsoft ostrzega, że ​​może to spowodować utratę danych (ten komentarz jest generowany automatycznie)!
2. przez pewien czas klucze obce nie są egzekwowane.
3. jeśli ręcznie uruchomisz to w ssms, a „EXEC („ INSERT INTO ”się nie powiedzie, i zezwolisz na uruchomienie następujących instrukcji (co robią domyślnie, ponieważ są one podzielone przez„ go ”), wówczas wstawisz 0 wierszy, a następnie upuścisz stary stół.
4. jeśli jest to duża tabela, środowisko wykonawcze wstawki może być duże, a transakcja zawiera blokadę modyfikacji schematu, więc blokuje wiele rzeczy.

-

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION
GO
ALTER TABLE raw.Contact
    DROP CONSTRAINT fk_Contact_AddressType
GO
ALTER TABLE ref.ContactpointType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.Contact
    DROP CONSTRAINT fk_contact_profile
GO
ALTER TABLE raw.Profile SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE raw.Tmp_Contact
    (
    ContactID int NOT NULL IDENTITY (1, 1),
    ProfileID int NOT NULL,
    AddressType char(2) NOT NULL,
    ContactText varchar(250) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE raw.Tmp_Contact SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT raw.Tmp_Contact ON
GO
IF EXISTS(SELECT * FROM raw.Contact)
     EXEC('INSERT INTO raw.Tmp_Contact (ContactID, ProfileID, AddressType, ContactText)
        SELECT ContactID, ProfileID, AddressType, ContactText FROM raw.Contact WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT raw.Tmp_Contact OFF
GO
ALTER TABLE raw.PostalAddress
    DROP CONSTRAINT fk_AddressProfile
GO
ALTER TABLE raw.MarketingFlag
    DROP CONSTRAINT fk_marketingflag_contact
GO
ALTER TABLE raw.Phones
    DROP CONSTRAINT fk_phones_contact
GO
DROP TABLE raw.Contact
GO
EXECUTE sp_rename N'raw.Tmp_Contact', N'Contact', 'OBJECT' 
GO
ALTER TABLE raw.Contact ADD CONSTRAINT
    Idx_Contact_1 PRIMARY KEY CLUSTERED 
    (
    ProfileID,
    ContactID
    ) 

GO
ALTER TABLE raw.Contact ADD CONSTRAINT
    Idx_Contact UNIQUE NONCLUSTERED 
    (
    ProfileID,
    ContactID
    ) 

GO
CREATE NONCLUSTERED INDEX idx_Contact_0 ON raw.Contact
    (
    AddressType
    ) 
GO
ALTER TABLE raw.Contact ADD CONSTRAINT
    fk_contact_profile FOREIGN KEY
    (
    ProfileID
    ) REFERENCES raw.Profile
    (
    ProfileID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE raw.Contact ADD CONSTRAINT
    fk_Contact_AddressType FOREIGN KEY
    (
    AddressType
    ) REFERENCES ref.ContactpointType
    (
    ContactPointTypeCode
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.Phones ADD CONSTRAINT
    fk_phones_contact FOREIGN KEY
    (
    ProfileID,
    PhoneID
    ) REFERENCES raw.Contact
    (
    ProfileID,
    ContactID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE raw.Phones SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.MarketingFlag ADD CONSTRAINT
    fk_marketingflag_contact FOREIGN KEY
    (
    ProfileID,
    ContactID
    ) REFERENCES raw.Contact
    (
    ProfileID,
    ContactID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE raw.MarketingFlag SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.PostalAddress ADD CONSTRAINT
    fk_AddressProfile FOREIGN KEY
    (
    ProfileID,
    AddressID
    ) REFERENCES raw.Contact
    (
    ProfileID,
    ContactID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE raw.PostalAddress SET (LOCK_ESCALATION = TABLE)
GO
COMMIT