Spis treści:

Kategoria:SQL Server


Dodawanie kolumny w SQL Server

Zanim dodamy kolumny, dodajmy tabelę

Wychodząc naprzeciw oczekiwaniom postaram się opisać najpowszechniejsze, przynajmniej według mnie, sposoby dodawania kolumn do istniejącej już tabeli w SQL Server. Nie trzeba chyba nikogo przekonywać, że jest to zadanie dość często wykonywane, a nie każdy dokładnie pamięta składnię poszczególnych instrukcji. Żeby ułatwić pokazywanie poszczególnych kroków zaczniemy tradycyjnie od stworzenia tabeli, na której w różny sposób będą dodawane kolejne kolumny. Tabela będzie miała na początku jedno pole o swojsko brzmiącej nazwie Kolumna1 i prostym typie int. Skrypt wygląda następująco:

CREATE TABLE dbo.Tabela
(
    Kolumna1 int
)

Dodawanie pojedynczej kolumny

Najczęstszym i najprostszym przypadkiem jest dodanie jednej kolumny. Żeby niepotrzebnie nie przedłużać, od razu przedstawiam odpowiedni skrypt:

ALTER TABLE dbo.Tabela
ADD Kolumna2 int

Do dodawania kolumny służy instrukcja ALTER TABLE, po której następuje nazwa tabeli, potem słowo kluczowe ADD, a potem właściwa definicja kolumny, czyli nazwa i typ.

Dodanie wielu kolumn

Dodawanie wielu kolumn jednocześnie także jest dość częstą potrzebą. Na tyle częstą, że składnia jest nieco uproszczona. Przykład dodania dwóch kolumn pokazany jest poniżej:

ALTER TABLE dbo.Tabela
ADD Kolumna3 nvarchar(16),
    Kolumna4 int NOT NULL

Składnia tej instrukcji jest następująca: najpierw tradycyjne ALTER TABLE, po którym pojawia się nazwa modyfikowanej tabeli. W dalszej kolejności idzie słowo kluczowe ADD z definicją pierwszej kolumny. Do tej pory wszystko wygląda tak, jak dodanie jednej kolumny. Po wpisaniu kodu dodającego jedną kolumnę wstawiamy przecinek, i piszemy definicję drugiej kolumny. Nic nie stoi na przeszkodzie, aby naszą przygodę z dodawaniem kontynuować i postaić kolejny przecinek i dodać definicję kolumny trzeciej i kolejnych. Co warte podkreślenia, instrukcje wykonywane są w jednej transakcji, atomowo. Jeżeli zatem wystąpi błąd podczas dodawania kolumny wymienionej jako druga w kolejności, wtedy kolumna dodawana jako pierwsza też się nie doda. Wykonując to jako dwie oddzielne instrukcje, piersza z kolumn zostanie wstawiona.

Dodanie kolumny będącej kluczem głównym z opcją IDENTITY

Nadszedł czas na nieco bardziej zaawansowane techniki. Jak podtytuł wskazuje, kolejnym zadaniem będzie dodanie kolumny, która stanie się jednocześnie kluczem głównym, na kolumnie pojawi się pole z autoinkrementacją, a oprócz tego wszystkiego na kolumnie zostanie założony indeks klastrowany. Zakładamy oczywiście, że tabela nie ma jeszcze klucza głównego ani indeksu klastrowanego. Naturalne jest, że sama kolumna też nie może istnieć. Fragment skryptu może wyglądać w sposób następujący:

ALTER TABLE dbo.Tabela
ADD ID int NOT NULL IDENTITY(1,1)
    CONSTRAINT PK_ID
    PRIMARY KEY CLUSTERED

Inny przykład

Jak mogliśmy się już przekonać, można dodawać jednocześnie wiele kolumn, można takie wstawianie kolumn połączyć z tworzeniem kluczy głównych. Można zakładać różne inne więzy integralności typu klucze obce i wartości domyślne, można takie nowo tworzone kolumny uzupełnić danymi. Przyjrzyjmy się przykładowemu fragmentowi skryptu zaprezentowanemu poniżej:

ALTER TABLE dbo.Tabela
ADD Kolumna5 int CONSTRAINT Default0 DEFAULT(0),
    Kolumna6 datetime CONSTRAINT DefaultNow
    DEFAULT GETDATE() WITH VALUES

W tym fragmencie połączone są różne techniki. Dodawane są jednocześnie dwie kolumny, na obu ustawiana jest wartość domyślna. Na kolumnie z datą wykonywana jest dodatkowo operacja uzupełniania rekordów wartością zwracaną z funkcji. Instrukcja WITH VALUES sprawia, że do istniejących już wcześniej rekordów także jest ta wartość wstawiana. Bez tej instrukcji istniejące już rekordy będą miały w nowej kolumnie wartości równe NULL.

Warto też zajrzeć do artykułu Dodawanie kolumny w SQL Server tylko wtedy, gdy nie istnieje. Opisałem tam w jaki sposób poradzić sobie z problemem, w którym skrypt musi wcześniej sprawdzić, czy dana kolumna istnieje.

Jeżeli temat nie został wystarczająco opisany, proszę o ślad w komentarzach. Ciekaw jestem, jakich Wy używacie technik do dodawania kolumn.

Kategoria:SQL Server

, 2013-12-20

Komentarze:

Tomek (2013-04-24 16:16:17)
Jak zmienić kolejność kolumn już istniejących, lub jak dodać kolumnę w konkretnym miejscu tabeli?
PD (2013-04-27 12:15:31)
Zanim napiszę odpowiedź muszę przestrzec przed traktowaniem tabel SQL jako tworów fizycznych. SQL Server nie definiuje czegoś takiego jak kolejność kolumn z bardzo prostego powodu - koncepcja relacyjnych baz danych opiera się on na algebrze zbiorów. A zbiór i elementy zbioru, w sensie matematycznym, nie mają określonego porządku. Podkreślę to jeszcze raz - tabele w bazie danych nie są tabelami, do jakich przywykliśmy (choć językowo jest tu całkowita spójność), lecz zbiorami (zob. Georg Cantor).
To, że SQL zwraca kolumny w takim samym, zdefiniowanym porządku, to tylko szczegół implementacyjny. Podobnie zresztą jest z wierszami - dopóki nie użyjemy jawnie instrukcji ORDER BY, nie mamy żadnej gwarancji, że kolejność będzie zachowana. W praktyce (podkreślam, że jest to szczegół implementacyjny SQL Server!) kolejność zależy od wygenerowanego planu wykonania, czyli najczęściej od użytego indeksu.

Kolejność kolumn, o której mowa, ma znaczenie tylko w przypadku użycia instrukcji SELECT *, której swoją drogą też należałoby unikać, bo teoretycznie nikt nam nie daje gwarancji kolejności kolumn (wyjątkiem są instrukcje, dla których porządek nie ma znaczenia np. EXISTS).

Jeżeli mimo wszystko zależy nam na fizycznej* kolejności kolumn, to trzeba taką tabelę zrobić od początku lub poprzestawiać kolumny w SQL Server Management Studio (opcja Desing na tabeli - w praktyce także tworzona jest nowa tabela, ale jest to przed nami ukryte).
Użyte słowo fizycznej* jest tu bardzo istotne. Bazę danych SQL Server należy traktować jako logiczną warstwę, nie fizyczną. Wtedy nie będzie problemu ze zrozumieniem tego i wielu innych pojęć. Jako zagadkę i temat do przemyśleń przedstawię taki oto skrypt:
SELECT * FROM
  (SELECT A FROM (VALUES (2),(1)) A(A)
  ORDER BY A) T
Dlaczego bez ORDER BY zadziała, a w pokazanej postaci nie? Podpowiedź - ma to związek z matematyczną definicją zbioru.
Adam (2013-07-10 10:21:32)
Chcę dodać jednocześnie wiele kolumn :
ALTER TABLE `RakJelitaGrubegoOd2007`
ADD
pT int NULL,
pN int NULL,
pM int NULL,
MaximumTumourDiameter int NULL,
DistanceOfTumourToNearerCutEnd int NULL
Wyskakuje bład :
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pN int NULL,
pM int NULL,
MaximumTumourDiameter int NULL,
Distanc' at line 4

??
Nie potrafię poprawić składni .
TIA
PD (2013-07-12 08:53:11)
Dobrze, że jest komunikat i zauważyłem, że jest to MySQL. Składnia jest minimalnie inna niż w SQL Server, czego dotyczy wpis. Dla MySQL będzie to coś takiego:
ALTER TABLE RakJelitaGrubegoOd2007
  ADD pT int NULL,
  ADD pN int NULL,
  ADD pM int NULL,
  ADD MaximumTumourDiameter int NULL,
  ADD DistanceOfTumourToNearerCutEnd int NULL
PZ (2020-08-13 11:35:58)
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!