Spis treści:

Kategoria:IndeksySQL Server


Zakładanie indeksu w SQL Server

Jak stworzyć indeks

Aby stworzyć prosty indeks w SQL Server należy opanować instrukcję CREATE INDEX. Ma ona mnóstwo opcji i nie wszystkie zostaną tutaj przedstawione. Wydaje mi się, że zaprezentowane tutaj przykłady wystarczą na większość przypadków. Najprostszy przykład mógłby wyglądać następująco:

--tworzenie tabeli
CREATE TABLE Tabela
(
Kolumna int
)
--tworzenie indeksu na kolumnie 'Kolumna'
CREATE INDEX IX_Kolumna ON Tabela(Kolumna)

Samo założenie indeksu sprowadza się do napisania słów kluczowych CREATE INDEX, nazwy indeksu, słowa kluczowego ON, a następnie nazwy tabeli, po której w nawiasie pojawia się nazwa kolumny. Wygląda prosto i takie w rzeczywistości jest. Nic nie stoi na przeszkodzie, aby stworzyć indeks z dwóch kolumn. Skrypt mógłby przybrać wtedy następującą postać:

--tworzenie tabeli
CREATE TABLE Tabela
(
Kolumna1 int,
Kolumna2 nvarchar(5)
)
--tworzenie indeksu na kolumnach 'Kolumna1' i 'Kolumna2'
CREATE INDEX IX_Kolumny ON Tabela(Kolumna1, Kolumna2)

Tworzenie indeksu unikalnego (inikatowego)

Czasami zachodzi taka potrzeba, aby zapewnić unikalność wartości w danej kolumnie. W takim przypadku należy na niej założyć indeks unikalny. Aby dało się założyć taki indeks, wartości w danej kolumnie (lub w wielu kolumnach, gdy zakładamy indeks na wielu kolumnach) muszą być różne. W zdecydowanej większości przypadków pola muszą być zdefiniowane jako NOT NULL, ponieważ dwie wartości NULL interpretowane są jako te same wartości. Trudno sobie wyobrazić, aby na dużej tabeli występowała tylko jedna wartość NULL. Dlaczego dużej tabeli? Bo na małej najczęściej nie ma sensu zakładać indeksów, chyba, że spodziewamy się, że wkrótce może urosnąć (lub jest to klucz główny, który automatycznie tworzy indeks). Przykład założenia indeksu unikalnego pokazany jest poniżej.

--tworzenie tabeli
CREATE TABLE Tabela
(
Kolumna1 int,
Kolumna2 nvarchar(50)
)
--tworzenie indeksu na kolumnie 'Kolumna1'
CREATE UNIQUE INDEX IX_Kolumna1 ON Tabela(Kolumna1)

Czas teraz na sprawdzenie, czy nasz indeks prawdziwie pilnuje unikalności wartości w kolumnie numer 1. W celu weryfikacji posłużę się następującym skryptem:

--próbujemy wstawić dwie takie same wartości do kolumny 1
INSERT INTO Tabela VALUES(1,'Zakładanie indeksu')
INSERT INTO Tabela VALUES(1,'Tworzenie indeksu')

W odpowiedzi otrzymamy następujący komunikat:

Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object 'dbo.Tabela' with unique index 'IX_Kolumna1'. The duplicate key value is (1).

Komunikat informuje nas, że uzyskaliśmy dokładnie to, czego oczekiwaliśmy.

Tworzenie indeksu klastrowanego i nieklastrowanego

Różnice w indeksach klastrowanych i nieklastrowanych oraz ich konsekwencje zostały poruszone w oddzielnym wpisie, więc nie będą tutaj poruszane. Dla leniwych napiszę, że indeks klastrowany określa rzeczywiste położenie danych na dysku, tj. ich fizyczny porządek, kolejność w jakiej są umieszczone. Logiczne w tym kontekście staje się fakt, że może być tylko jeden indeks klastrowany, bo nie da się jednocześnie ułożyć danych w dwóch różnych kolejnościach (tak jak nie da się, aby jeden ciąg kilku różnych liczb był jednocześnie rosnący i malejący). Różnica w tworzeniu tych dwóch różnych indeksów jest bardzo niewielka i została przedstawiona w kodzie poniżej:

--tworzenie tabeli
CREATE TABLE Tabela
(
Kolumna1 int,
Kolumna2 nvarchar(50)
)
--tworzenie indeksu klastrowanego na kolumnie 'Kolumna1'
CREATE CLUSTERED INDEX IX_Kolumna1 ON Tabela(Kolumna1)
--tworzenie indeksu nieklastrowanego na kolumnie 'Kolumna2'
CREATE NONCLUSTERED INDEX IX_Kolumna2 ON Tabela(Kolumna2)

Ta subtelna różnica to umieszczenie przed słowem kluczowym INDEX słowa kluczowego CLUSTERED dla indeksu klastrowanego, lub słowa kluczowego NONCLUSTERED dla indeksu nieklastrowanego.

Przy tworzeniu indeksów warto wiedzieć, że domyślnie tworzone są indeksy nieklastrowane. Słowo kluczowe NONCLUSTERED można zatem pominąć. W przykładzie zostało wstawione w celach edukacyjnych i porównawczych.

Definiowanie porządku kolumn

Dość powszechną praktyką jest definiowanie w indeksie porządku, w jakim klucze będą posortowane. Służą do tego dwa słowa kluczowe ASC i DESC. ASC definiuje porządek rosnący, a DESC malejący.

Domyślnie klucze posortowane są rosnąco, tj. od najmniejszych do największych. Jeżeli nie zaznaczymy inaczej, kolumny sortowane są tak, jakby były oznaczone słowem kluczowym ASC.

Fragment skrytpu, który definiuje porządek kluczy indeksu pokazany jest poniżej:

--tworzenie tabeli
CREATE TABLE Tabela
(
Kolumna1 int,
Kolumna2 nvarchar(50)
)
--tworzenie indeksu na kolumnach 'Kolumna1' i 'Kolumna2'
CREATE INDEX IX_Kolumny ON Tabela(Kolumna1 ASC, Kolumna2 DESC)

Podsumowanie

Zaprezentowane techniki w żadnym wypadku nie wyczerpują tematu zakładania indeksów. Bardziej zaawansowane techniki zostaną opisane oddzielnie. Zachęcam do dzielenia się spostrzeżeniami i sugestiami w komentarzach.

Kategoria:IndeksySQL Server

, 2013-12-20

Komentarze:

jahu (2014-05-26 14:11:08)
nienawidze tych baz danych
Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?