Dodanie kolumny z domyślną wartością

Rozszerzanie istniejącej tabeli

Przypuszczam, że każdy system informatyczny z czasem się rozrasta. Nie dałbym sobie ręki uciąć, ale kurczące się systemy w praktyce nie występują. Rozrost zaczyna się najczęściej od bazy danych, w której powstają dodatkowe tabele i tytułowe dodatkowe kolumny. Gdy pojawia się dodatkowa kolumna, pojawia się problem z istniejącymi już rekordami. Co wstawić w takie nowe pole? Odpowiedź nie jest jednoznaczna i nie ma jednej metody postępowania. Standard SQL definiuje coś takiego jak kolumny z domyślną wartością i nimi się dzisiaj zajmę. Temat niby prosty, ale warto poznać kilka możliwości i zobaczyć, gdzie czyha na nas licho, gdzie utopiec, a gdzie inne strzygi. Diabeł tkwi w szczegółach.

Popatrzmy zatem na pierwotną wersję tabeli:

CREATE TABLE dbo.DefaultColumn
(
Id int,
Column1 int
)

--Przykładowa wartość
INSERT INTO dbo.DefaultColumn
VALUES (1,1)

Tabela zawiera jeden wiersz z danymi, ale w praktyce tych wierszy będzie znacznie więcej. Tak przygotowani możemy przystąpić do dodawania domyślnych kolumn.

Nowa domyślna kolumna

Przyjrzyjmy się dwóm przykładowym instrukcjom dodającym po jednej kolumnie domyślnej. Należy zwrócić uwagę na to, że jedna z nich jest NULL, a druga NOT NULL.

--Dla typu NULL kolumna w istniejącym wierszu nie jest zmieniana
ALTER TABLE dbo.DefaultColumn
ADD Column2 int DEFAULT 2

--Dla typu NOT NULL istniejące wiersze są modyfikowane
ALTER TABLE dbo.DefaultColumn
ADD Column3 int NOT NULL DEFAULT 3

Różnica jest drobna, ale może mieć daleko idące konsekwencje. Każdy istniejący rekord, do którego dodajemy kolumnę NOT NULL jest uzupełniany domyślną wartością. Jeżeli kolumna może przyjąć NULL, będzie tam NULL.

Inną kwestią jest konieczność wewnętrznej przebudowy stron. Dane muszą być przecież gdzieś wciśnięte. Kolumny o stałej długości (int, decimal, varchar(4)) zajmują swój wymagany obszar niezależnie od tego, czy posiadają wartość. Jeżeli zatem w kolumnie int NULL jest wartość NULL, zajmuje ona 4 bajty! Co więcej, SQL Server przechowuje po 1 bicie znacznika dla każdej kolumny NULL. Jeżeli ten bit uda się wcisnąć w nieużywany obszar flag bitowych, jest dobrze. Jeżeli cały bajt jest zajęty bitami lub jest to pierwsza kolumna NULL, rezerwowany jest nowy, dodatkowy bajt, jako najmniejsza jednostka adresowaNajmniejszą jednostką adresową w powszechnie używanych systemach komputerowych jest jeden bajt. Jeden bit będzie zatem potrzebował jednego bajtu do zapamiętania. W praktyce, pomijając SQL Server, często stosuje się wyrównanie w pamięci na granicy słowa. Skoro rejestr procesora ma 32 bity, najlepiej jest mu pobrać właśnie 32 bity. Wydobycie 1 bitu z 32 to dodatkowy koszt. W takim przypadku zdarza się, że bit zajmuje 32 bity.. Dane zmiennej długości, jeżeli są puste, oprócz flagi bitowej nie zajmują pamięciZ drugiej strony istnieje pewna niedogodność związana z operacją UPDATE dla takiej kolumny. Jeżeli dane nie mieszczą się w przewidzianym dla nich miejscu, operacja może powodować podział strony (indeks CLUSTERED) lub przerzucenie nie mieszczących się danych do obszaru ROW_OVERFLOW. Obie operacje nie są pożądane. Lepiej, jak dane mieszczą się w jednym miejscu. Dlatego też wartości NULL o stałej szerokości zajmują swój obszar..

Popatrzmy na zawartość tabeli po wykonaniu dwóch pokazanych wyżej instrukcji:

Id	Column1	Column2	Column3
1	1	NULL	3

Pokazane instrukcje działają, ale są zapisem skróconym. Co może być z nimi nie tak?

Maszynowe nazwy kolumn domyślnych

Każda definicja kolumny domyślnej związana jest z odpowiadającym mu więzem integralności. Jeżeli sami nie podamy nazwy, zostanie ona utworzona automatycznie. Ten sam skrypt puszczony na dwóch różnych komputerach wygeneruje zatem dwie różne nazwy! Jeżeli ten sam wzorzec bazy wykorzystywany jest w wielu miejscach, lepiej żeby wszystko było jednakowe. Jeżeli już jest za późno, mozna skorzystać z odpowiedniego skryptu, który pokazałem we wpisie Jak usunąć wartość domyślną z kolumny w tabeli. Jeżeli nie jest za późno, lepiej nadać więzom swojskie nazwy. Przyjrzyjmy się pokazanemu poniżej skryptowi:

ALTER TABLE dbo.DefaultColumn
ADD Column4 int
CONSTRAINT DF_DefaultColumn_Column4 DEFAULT 4

ALTER TABLE dbo.DefaultColumn
ADD Column5 int NOT NULL
CONSTRAINT DF_DefaultColumn_Column5 DEFAULT 5

Skrypt nie jest znacząco dłuższy, ale przynajmniej nazwy są jednolite. Przyjrzyjmy się teraz nazwom nadanym wszystkim czterem więzom integralności:

SELECT name FROM sys.default_constraints
WHERE parent_object_id=OBJECT_ID('dbo.DefaultColumn')

Wynik może być inny na innych komputerach, ale chodzi głównie o "dekorowanie nazw".

name
DF__DefaultCo__Colum__3B40CD36
DF__DefaultCo__Colum__3C34F16F
DF_DefaultColumn_Column4
DF_DefaultColumn_Column5

Wstawianie nowych wierszy

Na początku użyłem skróconej formy wstawiania nowych wierszy. Ta skrócona forma zakłada fizyczną kolejność kolumn i w taki właśnie sposób wstawia wymienione w nawiasie wartości. Dodanie kolumn domyślnych wszystko komplikuje:

--Msg 213, Level 16, State 1, Line 1
--Column name or number of supplied values does not match table definition.
INSERT INTO dbo.DefaultColumn
VALUES (1,1)

Mamy więcej kolumn, więc należałoby je teraz wymienić. Ale czy nie dlatego dodaliśmy kolumny z domyślnymi wartościami, żeby tego wymieniania uniknąć? Istnieje kilka technik wstawiania takich kolumn. Po pierwsze, można skorzystać ze standardowej formy wstawiania danych:

--Forma pełna - wymieniamy kolumny, do których wstawiamy
INSERT INTO dbo.DefaultColumn(Id, Column1)
VALUES (6,6)

INSERT INTO dbo.DefaultColumn(Id, Column1, Column4)
VALUES (7,7,7)

Czy oznacza to, że forma skrócona nie działa? Działa, ale w wielu przypadkach nie jest już tak bardzo skrócona. Wartości domyślne należy w takim przypadku zamienić na słowo kluczowe DEFAULT.

--Forma skrócona, należy wymienić wszystki kolumny
--Wartości domyślne reprezentowane są przez DEFAULT
INSERT INTO dbo.DefaultColumn
VALUES(8,8,DEFAULT,8,DEFAULT,8)

Po wykonaniu wszystkich operacji tabela powinna zawierać następujące wartości:

Id	Column1	Column2	Column3	Column4	Column5
1	1	NULL	3	NULL	5
6	6	2	3	4	5
7	7	2	3	7	5
8	8	2	8	4	8

Jeszcze ciekawiej dzieje się wtedy, gdy jedna z kolumn oznaczona jest atrybutem IDENTITY - to także kolumna w pewnym sensie domyślna.

Domyślne IDENTITY

Przyjrzyjmy się jeszcze jednej zagadkowej tabeli, mocno uproszczonej, z jedną kolumną IDENTITY:

CREATE TABLE dbo.DefaultIdentity
(
ID int IDENTITY
)

--Msg 8101, Level 16, State 1, Line 1
--An explicit value for the identity column in table 'dbo.DefaultIdentity' can only be specified when a column list is used and IDENTITY_INSERT is ON.
--INSERT INTO dbo.DefaultIdentity VALUES (DEFAULT)

--Msg 102, Level 15, State 1, Line 1
--Incorrect syntax near ')'.
--INSERT INTO dbo.DefaultIdentity VALUES ()

Jak do takiego cuda wstawić rekord? Od razu umieściłem dwie instrukcje, które nie działają, a które pojawiają się w głowach większości pytanych. Takie zadanie jest okazją do przedstawienia jeszcze jednej wersji operacji INSERT. Popatrzmy na kolejny przykład:

INSERT INTO dbo.DefaultIdentity DEFAULT VALUES

Pójdę dalej i dodam jeszcze jedną kolumnę:

ALTER TABLE dbo.DefaultIdentity
ADD EntryDate datetime NOT NULL
CONSTRAINT DF_DefaultIdentity_EntryDate DEFAULT GETDATE()

Tym razem, aby wstawić wszystkie domyślne wartości, mamy więcej opcji:

--Wszystkie domyślne wartości
INSERT INTO dbo.DefaultIdentity DEFAULT VALUES
--Domyślna wartość daty, IDENTITY jest pomijane
INSERT INTO dbo.DefaultIdentity VALUES (DEFAULT)

Obie operacje powinny się zakończyć sukcesem, a tabela powinna zawierać konkretne dane (naturalnie daty mogą być inne):

ID	EntryDate
1	2013-07-02 18:45:57.547
2	2013-07-02 18:46:00.633
3	2013-07-02 18:46:00.637

Podsumowanie

Przypuszczam, że pokazane powyżej przykłady obejmują większość przypadków, z którymi można się zetknąć podczas pracy z wartościami domyślnymi, w tym z automatycznie uzupełnianymi wartościami kolumny IDENTITY (więcej o IDENTITY można przeczytać we wpisie Identity jako identyfikator). Gdyby pojawiły się jakieś problemy z kolumnami domyślnymi, zachęcam do dzielenia się nimi w komentarzach.

Kategoria:Identity SQL Server

Paweł Dyl, 2013-12-20

Spis treści: