Ponieważ SQL Serwer przechowuje dane w tabelach, ważne jest aby zrozumieć komendy T-SQL służące do tworzenia i zmiany tabel. Każda tabela należy do jednej bazy danych dlatego SQL Serwer chroni je w procesach tworzenia kopii zapasowej i przywracania, oprócz zachowań transakcyjnych opisanych w następujący sposób:

• Gdy stworzysz kopię zapasową bazy danych (backup), wszystkie tabele bazy są zapisane w kopii zapasowej. Kiedy baza zostaje przywrócona to wszystkie tabele są przywrócone z danymi które były zapisane w czasie tworzenia kopii.
• Gdy pobierasz dane z bazy danych, które docelowo są zapisane w tabelach albo tej bazy danych albo innej bazy danych do której odwołuje się zapytanie.
• Nawet dane systemowe w SQL Serwerze są przechowywane w specjalnych tabelach zwanych tabelami systemowymi.

W SQL Serwerze tabele zawierające dane są często nazywane tabelami bazowymi dla odróżnienia ich od innych obiektów lub wyrażeń które mogą być uzyskane z tabel jak np. widoki lub zapytania. Tabele bazowe są trwałe w tym sensie, że ich definicja i zawartość pozostaje w bazie danych nawet po jej wyłączeniu. Inne typy tabel:

• Tabele tymczasowe - są tabelami bazowymi które istnieją w bazie tempdb lecz tylko do momentu, aż sesja w których istnieją  lub odwołania do nich trwają.
• Zmienne tabelaryczne - są zmiennymi które przechowują dane ale tylko do momentu istnienia instancji zapytania T-SQL.
• Widoki - nie są tabelami ale pochodzą od zapytań na tabelach bazowych, używa się ich tak jak tabel lecz nie przechowują one danych.
• Widoki indeksowane - przechowują dane lecz są definiowane jako widoki i są aktualizowane wraz z aktualizacją tabel bazowych.
• Tabele pochodne (derived tables)i wyrażenia tablicowe- są to podzapytania do których odwołujemy się w zapytaniach jak do tabel.

Tworzenie tabel

Tabele można tworzyć na dwa sposoby:

• Używając wyrażenia CREATE TABLE w którym jawnie definiujemy komponenty tabeli.
• Używając wyrażenia SELECT INTO, które tworzy tabelę automatycznie na podstawie danych wyjściowych z zapytania.

W tej lekcji zajmiemy się jawnym tworzeniem tabel. Pełną składnię wyrażenia CREATE TABLE można znaleźć w artykule: http://msdn.microsoft.com/en-us/library/ms174979.aspx. Pełna składnia jest zbyt skomplikowana. Poniżej przedstawiona jest składnia uproszczona:

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
[ AS FileTable ]
( { <column_definition> | <computed_column_definition>
| <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

Każdy z elementów może być rozszerzony a niektóre z nich mogą być rozbudowane. W tej lekcji omówione zostaną następujące elementy:

• Database name
• Schema name
• Table name
• Column definition
• Computed column definition
• Table constraint
• Table option

Przykładem niech będzie poniższy kod tworzący tabelę Production.Categories w bazie TSQL2012:

CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL)
GO

Gdy tworzysz tabelę, możesz wybrać schemat bazy danych w jakim zostanie ona utworzona, w tym wypadku to Production. Jeżeli nie zadeklarujesz schematu to SQL Serwer wypełni schemat zgodnie z domyślnym schematem twojego użytkownika. Tworząc tabele koniecznie trzeba określić:

• Nazwę tabeli - w tym wypadku Categories.
• Kolumny tabeli, zawierające nazwy kolumn i typy danych tych kolumn np: categoryid INT

Tworząc tabelę można również określić:

1. Dla kolumn:
   • Długość danych tekstowych np (15) dla categoryname.
   • Precyzję typów numerycznych i typów daty i czasu.
   • Opcjonalne typy kolumn(computed, sparse, IDENTITY, ROWGUIDCOL), np IDENTITY dla categoryid.
   • Sposób porównywania kolumn COLLATION (jeżeli chcemy użyć innego sposobu niż domyślny).
2. Ograniczenia, w tym:
   • Opcje dopuszczania wartości null np categoryid ... NOT NULL.
   • Domyślną wartość.
   • Sprawdzanie ograniczeń.
   • Klucz główny np PK_Categories.
   • Klucze obce.
   • Unikalność - UNIQUE CONSTRAINTS.
3. Możliwe kierunki przechowywania danych, w tym:
   • Filegroup np ON [PRIMARY].
   • Partition schema.
   • Kompresja tabeli.

Często ograniczenia tabel dodaje się po utworzeniu tabeli za pomocą komendy ALTER TABLE.

Określenie schematu bazodanowego - Database Schema.

Wszystkie tabele należą do grup obiektów w bazie danych zwanych schematami. Schemat bazy danych jest nazwanym kontenerem (przestrzenią nazw), którego można używać do grupowania tabel i innych obiektów w bazie danych. Podstawowym celem istnienia schematów bazodanowych jest grupowanie wielu obiektów bazy danych razem. Dzięki schematom w bazie danych może istnieć wiele obiektów o takiej samej nazwie, lecz każdy z nich musi istnieć w innym schemacie. Schemat jest częścią nazwy po której SQL Serwer identyfikuje tabelę. Jeżeli nie określisz schematu przy tworzeniu tabeli to SQL Serwer stworzy ją w domyślnym schemacie twojego użytkownika. Schemat bazy danych i schemat tabeli to inne obiekty. Schemat bazy danych to kontener na obiekty bazy danych natomiast schemat tabeli to definicja tabeli zawierająca deklarację CREATE TABLE wraz z definicją kolumn. Następujące schematy bazy danych nie mogą być usunięte:

• dbo - domyślny schemat bazy danych do tworzenia nowych obiektów przez użytkowników z rolami db_owner i db_ddl_admin.
• guest - schemat używany do przechowywania obiektów użytkownika gość.
• INFORMATION_SCHEMA - schemat używany przez widoki Information Schema, które zapewniają standardowy (ANSI) dostęp do metadanych.
• sys - schemat zarezerwowany dla obiektów systemowych.

Każdy schemat bazy danych musi mieć właściciela w postaci użytkownika bazy danych. Ten właściciel schematu może nadać uprawnienie odnośnie tego schematu innym użytkownikom. poniższy kod tworzy schemat Production którego właścicielem jest użytkownik dbo:

CREATE SCHEMA Production AUTHORIZATION dbo;
GO

Można przenosić tabele między schematami za pomocą wyrażenia ALTER SCHEMA TRANSFER. Przykładowo przeniesienie tabeli Categories do schematu Sales:

ALTER SCHEMA Sales TRANSFER Production.Categories;

Nazywanie tabel i kolumn

Użytkownik ma wolny wybór w nazywaniu schematów, tabel i kolumn. Istnieją jednak pewne zasady i najlepsze praktyki nazywania tych obiektów. Wszystkie schematy, tabele i nazwy kolumn muszą być poprawnymi identyfikatorami SQL Serwera. Identyfikatory muszą mieć co najmniej jeden znak długości i nie mogą być dłuższe niż 128. Istnieją dwa typy identyfikatorów: regularne i ograniczone (regular i delimited). Identyfikatory regularne są nazywane według pewnego zbioru zasad i nie muszą być otoczone przez nawiasy [] lub cudzysłów ''. Identyfikatory regularne zawierają:

• Litery zdefiniowane w standardzie Unicode 3.2.
• Liczby dziesiętne z alfabetu łacińskiego lub innych alfabetów narodowych.

Pierwszym znakiem musi być litera w standardzie Unicode 3.2 lub znak podkreślenia _ i nie może być to cyfra. Istnieją od tego dwa wyjątki:

• Zmienne muszą zaczynać się od znaku małpy @.
• Tymczasowe tabele lub procedury muszą zaczynać się od znaku krzyżyka #.

Kolejne znaki identyfikatora mogą zawierać:

• Litery standardu Unicode 3.2.
• Cyfry z alfabetu łacińskiego (0 - 9).
• Znaki: @, $, #, _.

Identyfikator regularny nie może być słowem zarezerwowanym przez T-SQL i zawierać białych znaków i znaków specjalnych które nie zostały wymienione powyżej. Używanie identyfikatorów regularnych jest uważane za dobrą praktykę, najlepiej używać tylko liter, cyfr i znaku podkreślenia. Dzięki temu użytkownik nie musi stosować ograniczników w postaci nawiasu lub cudzysłowowa aby odnieść się do obiektu po jego nazwie. Identyfikatory ograniczone to nazwy które nie spełniają zasad identyfikatorów regularnych. Aby ich używać do odwołań do obiektów, trzeba je otoczyć ogranicznikami w postaci nawiasów kwadratowych lub cudzysłowowa.  Użycie cudzysłowowa jest standardem ANSI SQL.  W SQL Serwerze domyślnym ogranicznikiem jest nawias kwadratowy. Cudzysłów może być włączany lub wyłączany przy użyciu komendy SET QUOTED_IDENTIFIER ON/OFF.Ponieważ można wyłączyć użycie cudzysłowowa to jego użycie jest ryzykowne. Generalnie najlepszą praktyką jest tworzenie nazw schematów, tabel i kolumn jako identyfikatory regularne o krótkiej nazwie opisową.

Wybór typów danych dla kolumn

Typ danych użyty dla każdej z kolumn jest bardzo ważny. Oto kilka krótkich wskazówek, które można wykorzystać do wyboru typów danych dla kolumn:

• Spróbuj użyć najbardziej efektywnego typu danych, takiego który zajmie jak najmniej pamięci na dysku i przechowa odpowiednio dane oraz nie będzie musiał być zmieniony później gdy tabela zostanie wypełniona danymi.
• Jeżeli trzeba przechować ciągi znakowe które różnią się długością to zastosuj raczej typ NVARCHAR lub VARCHAR zamiast typów NCHAR lub CHAR.
• Typy DATE, TIME i DATETIME2 mogą przechowywać dane bardziej efektywnie i z większą precyzją niż typy DATETIME lub SMALLDATETIME.
• Używaj VARCHAR(MAX), NVARCHAR(MAX) i VARBINARY(MAX) zamiast przestażałych TEXT, NTEXT i IMAGE.
• Używaj ROWVERSION zamiast przestażałego typu TIMESTAMP.
• DECIMAL i NUMERIC to taki sam typ danych, lecz generalnie ludzie preferują DECIMAL ponieważ jego nazwa jest bardziej opisowa. Używaj DECIMAL lub NUMERIC zamiast FLOAT lub REAL dopóki potrzebujesz zmiennoprzecinkowej precyzji. typy FLOAT i REAL są typami aproksymacyjnymi i zaokrąglają liczby.

NULL i wartość domyślna kolumn

Obsługa niewiadomych wartości jest trudnym problemem w teorii baz danych. T-SQL używa standardu ANSI SQL i zezwala na użycie wartości NULL jako niewiadomej. NULL nie jest wartością kolumny; jest raczej sposobem na zapis wartości niewiadomej. Można określić czy kolumna pozwala na użycie wartości NULL ustawiając właściwość NULL lub NOT NULL po prawej stronie deklaracji kolumny za typem danych. Można skorzystać z poniższych wskazówek:

• Jeżeli wiesz, że wartość dla kolumny może być nieokreślona lub nie mieć wartości to zdefiniuj kolumnę z użyciem NULL.
• Jeżeli kolumna nie może być nieokreślona, lecz chcesz aby miała pewną wartość domyślną możesz użyć NOT NULL i ograniczenia DEFAULT do zdefiniowania wartości domyślnej.

Jako przykład możemy zdefiniować kolumnę z opisem w tabeli Production.Categories z domyślną wartością zawierającą pusty ciąg tekstowy:

CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL DEFAULT ('')
) ON [PRIMARY];
GO

Teraz gdy do tabeli zostanie dodany wiersz , nie trzeba dodawać do niego opisu. Będzie on wypełniony pustym łańcuchem tekstowym. Można go będzie później zaktualizować.

Identity i numery sekwencyjne

W t-SQL właściwość IDENTITY może być przypisana do kolumny w celu automatycznego generowania sekwencji liczb. Można użyć jej tylko do jednej kolumny w tabeli. Dla właściwości IDENTITY można określić wartość startową i wartość przyrostu generowanej sekwencji. W trakcie definiowania właściwości w klauzuli CREATE TABLE można określić wartość startową i przyrostową w nawiasie jako argumenty właściwości IDENTITY. Najczęściej używane są wartości (1,1), tak jak w przykładzie tworzenia tabeli Production.Categories:

CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL DEFAULT ('')
) ON [PRIMARY];
GO

Wiele tabel posiada klucz główny na kolumnie z właściwością IDENTITY. SQL Serwer 2012 wprowadził opcjonalny sposób do generowania sekwencji numerów za pomocą obiektów SEQUENCE. Można użyć sekwencji jako opcjonalnego sposobu generowania unikalnych wartości numerycznych w tabeli.

Kolumny obliczane - Computed Columns

Tabele mogą zawierać również kolumny których wartość jest obliczana na podstawie wyrażenia. Wyrażenie to może być oparte na wartościach innych kolumn w wierszu lub funkcji T-SQL. Na przykład w tabeli Sales.OrderDetails możemy użyć dwóch kolumn - ceny jednostkowej i ilości do wyliczenia początkowego kosztu zamówienia. Można użyć wyrażenia unitprice * qty AS initialcost w klauzuli CREATE TABLE do stworzenia kolumny obliczanej:

CREATE TABLE Sales.OrderDetails
(
orderid INT NOT NULL,
…
initialcost AS unitprice * qty -- computed column
);

Kompresja tebel

Dane w tabelach mogą być kompresowane, tak aby zajmowały mniej miejsca w pamięci. Kompresja możliwa jest w edycji SQL Server 2012 Enterprise. Kompresja tabel może mieć dwa poziomy:

• ROW (wiersz) - Do kompresji na poziomie wierszy, SQL Serwer stosuje bardziej kompaktowy format przechowywania każdego wiersza w tabeli.
• PAGE (strona) - Kompresja na poziomie strony zawiera kompresję na poziomie wierszy plus dodatkowy algorytm kompresji na poziomie stron w pamięci.

Poniższa komenda dodaje kompresję do tabeli Production.OrderDetails jako fragment deklaracji tej tabeli w klauzuli CREATE TABLE:

CREATE TABLE Sales.OrderDetails
(
orderid INT NOT NULL,
…
)
WITH (DATA_COMPRESSION = ROW);

Można również użyć komendy ALTER do ustawienia kompresji jak na przykładzie:

ALTER TABLE Sales.OrderDetails
REBUILD WITH (DATA_COMPRESSION = PAGE);

SQL Serwer posiada procedurę składowaną sp_estimate_data_compression_savings która analizuje czy tabela będzie miała korzyści z kompresji. Więcej o kompresji danych można przeczytać na stronach:

• http://msdn.microsoft.com/en-us/library/cc280449.aspx
• http://msdn.microsoft.com/en-us/library/cc280574.aspx

Zmiana tabel

Po utworzeniu tabeli można zmienić jej strukturę i właściwości za pomocą komendy ALTER TABLE. ALTER TABLE może być używana do:

• Dodawania i usuwania kolumn - nowe kolumny są dodawane na końcu tabeli.
• Zmiana typu danych kolumn.
• Zmiana możliwości dodawania NULL do kolumn.
• Dodawanie i zmiana ograniczeń:  kluczy głównych, unikalności, kluczy obcych, wartości domyślnej i sprawdzania ograniczeń (check constraint).

Chcąc zmienić definicję ograniczenia lub definicję wyliczanej kolumny trzeba usunąć ograniczenie lub kolumnę ze starą definicją i dodać nową definicję. Za pomocą ALTER TABLE nie można:

• Zmienić nazwy kolumn.
• Dodać właściwości IDENTITY.
• Usunąć właściwości IDENTITY.

Ćwiczenia

I. Użycie ALTER TABLE do dodawania i modyfikacji kolumn.

1. Przyjrzyj się instrukcji CREATE TABLE tworzącej tabelę Production.Categories.

   /* From TSQL2012.sql:
   -- Create table Production.Categories
   CREATE TABLE Production.Categories
   (
   categoryid INT NOT NULL IDENTITY,
   categoryname NVARCHAR(15) NOT NULL,
   description NVARCHAR(200) NOT NULL,
   CONSTRAINT PK_Categories PRIMARY KEY(categoryid)
   );
   */

2. Użyj poniższego skryptu CREATE TABLE do utworzenia tabeli Production.CategoriesTest.

   USE TSQL2012;
   GO
   CREATE TABLE Production.CategoriesTest
   (
   categoryid INT NOT NULL IDENTITY
   );
   GO

3. Dodaj kolumny categoryname i description aby były takie jak w tabeli Production.Categories.

   ALTER TABLE Production.CategoriesTest
   ADD categoryname NVARCHAR(15) NOT NULL;
   GO
   ALTER TABLE Production.CategoriesTest
   ADD description NVARCHAR(200) NOT NULL;
   GO

4. Użyj opcji IDENTITY_INSERT ON aby skopiować dane z tabeli Production.Categories do Production.CategoriesTest.

   SET IDENTITY_INSERT Production.CategoriesTest ON;
   INSERT Production.CategoriesTest (categoryid, categoryname, description)
   SELECT categoryid, categoryname, description
   FROM Production.Categories;
   GO
   SET IDENTITY_INSERT Production.CategoriesTest OFF;
   GO

II. Praca z NULL w kolumnach

1. Rozszerz kolumnę description do rozmiaru 500.

   ALTER TABLE Production.CategoriesTest
   ALTER COLUMN description NVARCHAR(500);
   GO
   

2. Zmień tabelę tak aby kolumna description mogła mieć wartość NULL.

   ALTER TABLE Production.CategoriesTest
   ALTER COLUMN description NVARCHAR(500) NULL ;
   GO
   

3. Zmień tabelę tak aby kolumna description nie mogła mieć wartości NULL.

   ALTER TABLE Production.CategoriesTest
   ALTER COLUMN description NVARCHAR(500) NOT NULL ;
   GO
   

Podsumowanie

1. Tworząc tabelę określamy schemat bazy danych jako kontener dla tabeli.
2. Nazwy tabel i kolumn są ważne i trzeba wybierać je ostrożnie.
3. Kolumny powinny mieć najbardziej dopasowane i dokładne typy danych.
4. Kolumny mogą posiadać właściwości IDENTITY.
5. Kolumny mogą lub nie mogą zawierać wartości NULL w zależności od ich definicji.
6. Tabele mogą być kompresowane.
7. Większość właściwości bazy danych może być zmienione przez ALTER TABLE.