MS SQL Server 6.5 - Zarządzanie indeksowaniem danych i kluczami









Indeks jest wykorzystywany jako pomoc w szybszym wyszukiwaniu
i sortowaniu rekordów. Działa on na podobnej zasadzie jak indeks w książce:
w celu znalezienia danych sprawdzane jest ich położenie w indeksie. Bez indeksów
SQL Server musiałby przeczytać każdy wiersz tabeli, by móc udzielić odpowiedzi
na zadane zapytanie. Możliwe jest utworzenie indeksu na podstawie jednego pola
(kolumny) lub na podstawie kilku pól (kolumn). Indeksy utworzone na podstawie
kilku pól umożliwiają odróżnianie rekordów, w których pierwsze pole ma taką samą
wartość. Indeksy mogą być tworzone w tym samym czasie, gdy jest tworzona tabela,
bądź w późniejszym okresie. Zwykle indeksem obejmowane są pola często
poszukiwane, sortowane lub pola, które są sprzężone z polami w innych tabelach
lub kwerendach.
Klucz podstawowy (PRIMARY KEY) jest to jedno lub więcej pól,
których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz
podstawowy nie może przyjmować wartości null. Klucz podstawowy jest używany w
relacji do odwoływania się do określonych rekordów w jednej tabeli z innej
tabeli. Klucz podstawowy tabeli jest indeksowany automatycznie. Tabela może
zawierać tylko jeden klucz podstawowy.
Przy tworzeniu klucza należy sprawdzić, czy spełnione są następujące
warunki:      
Spodziewane jest wyszukiwanie wartości przechowywanych w polu.
Spodziewane jest sortowanie wartości w polu.
Spodziewane jest przechowywanie wielu różnych wartości w polu. W przypadku
powtarzania się wielu wartości w polu, indeks nie musi przyspieszać znacząco
działania kwerend.
Klucz obcy (FOREIGN KEY) to jedno lub więcej pól tabeli
odwołujących się do pola lub pól klucza podstawowego w innej tabeli. Klucz obcy
wskazuje, w jaki sposób tabele są powiązane. Dane w polach klucza podstawowego
i obcego muszą być zgodne. Klucz obcy jest wykorzystywany do utrzymywania
integralności referencyjnej w bazie danych.

 
Tworzenie indeksów z wykorzystaniem SQL Enterprise
Manager
Sposób tworzenie indeksu przy użyciu SQL Enterprise Manager'a przedstawia
poniższy (przykładowy) algorytm:
Uruchamiamy SQL Enterprise Manager z SQL Server'a.
Wybieramy serwer, bazę danych i tabelę, na której chcemy pracować.

Część widoku okna SQL Enterprise Manager'a na tym etapie pracy przedstawiamy
poniżej :


Z menu Manage wybieramy Indexes. Okno dialogowe Manage
Indexes wygląda następująco:      
  


Wybieramy wartość (New Index) z pola kombi Index. To
wyczyści pole kombi Index umożliwiając wprowadzenie nowej nazwy
indeksu. Pola Columns In Index zostaną równocześnie wyczyszczone.
Wprowadzamy fk_au_id jako nazwę indeksu, Wybieramy kolumnę
au_id i naciskamy klawisz Add. Wybieramy opcje Unique
Keys i Ignore Duplicate Keys.


Naciskamy przycisk Build by zbudować indeks. Otworzy się okno
dialogowe z zapytaniem, czy indeksy mają być zbudowane teraz (Execute
Now) czy też ustawione jako zadanie do uruchomienia później (Schedule
As Task). Tworzenie indeksów ręcznie używając polecenia
CREATE INDEX
Składnia polecenia CREATE INDEX jest następująca:




CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX
index_nameON [[database.]owner.]table_name
(column_name [,
column_name]...)[WITH   [FILLFACTOR =
x]   [[,]
IGNORE_DUP_KEY]   [[,] {SORTED_DATA |
SORTED_DATA_REORG}]   [[,] {IGNORE_DUP_ROW |
ALLOW_DUP_ROW}]][ON segment_name]
Opis opcji polecenia CREATE INDEX:
UNIQUE - Indeks ma przyjmować wartości unikatowe. Przed utworzeniem
unikatowego indeksu należy usunąć powtarzające się dane w danej kolumnie.
CLUSTERED - Kolejność danych w bazie danych będzie zgodna z
indeksem.
NONCLUSTERED - Kolejność danych w bazie danych będzie zgodna z ich
wprowadzaniem. Domyślnie przyjmowana jest opcja NONCLUSTERED.
FILLFACTOR - Określa, w jakim stopniu dane mają być pakowane przy
tworzeniu indeksu. Ta opcja powinna być rzadko używana.
IGNORE_DUP_KEY - Określa, że dozwolone jest powtarzanie się danych
w indeksowej kolumnie. Domyślnie SQL Server zgłasza błąd przy próbie
wprowadzenia powtarzających się danych.
SORTED_DATA - Określa, iż dane są składowane w bazie danych w
sposób posortowany. Ta opcja ma sens, jeśli ustawiona jest opcja CLUSTERED.
SORTED_DATA_REORG - Wykorzystywane przy opcji NONCLUSTERED
do przeorganizowywania danych w porządku zgodnym z indeksem.
IGNORE_DUP_ROW - Ta opcja jest używana do tworzenia nieunikatowych
indeksów typu CLUSTERED. Po utworzeniu indeksu są z niego usuwane
powtarzające się wartości. Zwraca błąd w przypadku niepowodzenia.
ALLOW_DUP_ROW - Ta opcja jest używana do tworzenia nieunikatowych
indeksów typu CLUSTERED. Nie jest zwracany błąd gdy dane się
powtarzają.

 
Tworzenie kluczy z wykorzystaniem SQL Enterprise
Manager
Aby dodać podstawowy klucz postępujemy następująco:
Uruchamiamy SQL Enterprise Manager.
Wybieramy serwer, bazę danych i tabelę, na której chcesz pracować.
Z menu Manage wybierz Tables. Okno Manage Tables
wygląda następująco:


Klikamy przycisk Advanced Features z paska narzędziowego by
uaktywnić zaawansowane opcje. Zobacz poniżej:


Usuwamy istniejący podstawowy klucz klikając przycisk Remove, a
następnie wprowadzamy informacje.


Klikamy przycisk Add by dodać podstawowy klucz. Ikona klucza pojawi
się w tabeli.
Klikamy przycisk Save by zapisać zmiany w tabeli. Możemy otrzymać
komunikat o błędzie jeżeli została zdefiniowana relacja z tą tabelą. Należy
zignorować ten błąd i zamknąć okno bez zapisywania
zmian. Tworzenie kluczy z wykorzystaniem składni
CREATE TABLE...PRIMARY KEY
Składnia tego polecenia wygląda następująco:




CREATE TABLE table_name( column_name data_type
CONSTRAINT ...,...) 
Poniżej przedstawiamy przykładowe sposoby definiowania kluczy:

Tworzy tabelę, w której nazwa klucza nie jest określona i baza danych
przypisze ją:




Create TABLE TABLE_A(COLUMN_A smallint PRIMARY
KEY)go

Tworzy klucz podstawowy określając jego nazwę:




Create TABLE TABLE_BCOLUMN_B smallint CONSTRAINT
PK_COLUMN_B PRIMARY KEY)go

Tworzy klucz obcy powiązany relacją z tabelą TABLE_A:




Create TABLE TABLE_C( COLUMN_C smallint FOREIGN KEY
(COLUMN_C) REFERENCES
TABLE_A(COLUMN_A))go

Tworzy wielokolumnowy klucz podstawowy:




Create TABLE TABLE_D( COLUMN_D1 smallint CONSTRAINT
PK_D_COLUMNS PRIMARY KEY (COLUMN_D1, COLUMN_D2),
COLUMN_D2 smallint)go

Tworzy klucz obcy powiązany z wielokolumnowym kluczem podstawowym:




Create TABLE TABLE_E( COLUMN_E1 smallint FOREIGN KEY
(COLUMN_E1, COLUMN_E2)REFERENCES TABLE_D( COLUMN_D1,
COLUMN_D2),COLUMN_E2
smallint)go Tworzenie kluczy z
wykorzystaniem składni ALTER TABLE...ADD CONSTRAINT.
Poniżej przedstawiamy przykładowe sposoby definiowania kluczy.
Tworzenie tabeli:




Create TABLE TABLE_A( COLUMN_A
smallint)go
Dodanie podstawowego klucza bez podawania jego nazwy:




Alter Table TABLE_A ADD PRIMARY KEY
(COLUMN_A)go
Tworzenie tabeli




Create TABLE TABLE_B( COLUMN_B
smallint)go
Dodanie podstawowego klucza z podaniem jego nazwy




Alter Table TABLE_B ADD CONSTRAINT PK_COLUMN_B PRIMARY
KEY (COLUMN_B)go
Tworzenie tabeli




Create TABLE TABLE_C( COLUMN_C
smallint)go
Tworzenie obcego klucza powiązanego z tabelą TABLE_A




Alter Table TABLE_C ADD FOREIGN KEY (COLUMN_C)
REFERENCES TABLE_A(COLUMN_A)Go
Tworzenie tabeli




Create TABLE TABLE_D( COLUMN_D1 smallint,
COLUMN_D2 smallint)go
Dodanie wielokolumnowego klucza podstawowego




Alter Table TABLE_D ADD CONSTRAINT PK_D_COLUMNS PRIMARY
KEY (COLUMN_D1, COLUMN_D2)go
Tworzenie tabeli




Create TABLE TABLE_E( COLUMN_E1 smallint,
COLUMN_E2 smallint)go
Dodanie obcego klucza powiązanego z wielokolumnowym kluczem głównym




Alter Table TABLE_E ADD CONSTRAINT FK_E_COLUMNS FOREIGN
KEY (COLUMN_E1, COLUMN_E2)REFERENCES TABLE_D(
COLUMN_D1, COLUMN_D2)go

 
Informacje o indeksach
Informację o istniejących indeksach można uzyskać dzięki SQL Enterprise
Manager Index Manage lub poprzez polecenie sp_helpindex mającemu
następującą postać:




sp_helpindex table_name
Polecenie to zwraca osiem pierwszych indeksów założonych w tabeli danych
table_name.
Przykładowe działanie polecenia przedstawiamy poniżej :





/* sp_helpindex authors */



index_name
index_description
index_keys


--------------------
-----------------------------------------------------------------------
--------------------


UPKCL_auidind
clustered, unique, primary key located on default
au_id


aunmind
nonclustered located on default
au_lname, au_fname


barny
nonclustered, ignore duplicate key, unique located on default
au_id


fk_au_id
nonclustered, ignore duplicate key, unique located on default
au_id Usuwanie indeksów
SQL Server posiada dwa mechanizmy usuwania indeksów: z wykorzystaniem SQL
Enterprise Manager oraz poprzez linię poleceń wykorzystując komendę DROP
INDEX.
Pierwsza z metod została w zasadzie omówiona w przykładowym algorytmie
tworzenia indeksów z wykorzystaniem SQL Enterprise Manager, kiedy to przed
utworzeniem nowych indeksów usuwaliśmy wszystkie dotychczas istniejące w danej
tabeli.




DROP INDEX [owner.]table_name.index_name [,
[owner.]table_name.index_name...]
gdzie table_name jest tabelą z której usuniemy indeks o nazwie
index_name (można też usuwać indeksy zawierające kilka kolumn) .
Na przykład usuwamy indeks barny z tabeli authors :




Drop Index
authors.barny Informacje o
kluczach
SQL Server posiada dwa mechanizmy informacji o istniejących kluczach:
graficznie dzięki SQL Enterprise Manager Table Manage oraz z linii
poleceń poprzez komendy sp_help i sp_helpconstraint.
Polecenie sp_helpconstraint zwraca bezpośrednio informację o kluczach
w danej tabeli i jego składnia jest następująca :




sp_helpconstraint table_name
Przykładowe działanie tej komendy :




          /*
sp_helpconstraint
TABLE_D */



Object Name




------------------------




TABLE_D




constraint_type
constraint_name
constraint_keys


---------------------------------
-----------------------
-----------------------------------


PRIMARY KEY (clustered)
PK_D_COLUMNS
COLUMN_D1, COLUMN_D2


Table is referenced by




--------------------------------




       pubs.dbo.TABLE_E:
FK_COLUMNS

Polecenie sp_help zwraca informację o danych, które zawiera dana
tabela i część tych informacji dotyczy istniejących kluczy :




sp_help table_name
Przykładowe działanie :




/* sp_help
table_d */




Name
Owner
Type
When_created


-----------
----------
------------
------------------------


TABLE_D
  dbo
user table
Dec 11 1995 7:42PM




Data_located_on_segment------------------------------default




Column_name
Type
Length
Prec
Scale
Nullable


---------------
-------------
--------
------
--------
------------


COLUMN_D1
smallint
2
5
0
no


COLUMN_D1
smallint
2
5
0
no




Identity
Seed
Increment


-----------------------------
--------
------------


No Identity column defined.
(null)
(null)




index_name
index_description
index_keys


-----------------------
----------------------------------------------------------
--------------------------------------


PK_D_COLUMNS
clustered, unique, primary key located on default
COLUMN_D1, COLUMN_D2




constraint_type
constraint_name
constraint_keys


---------------------------------
-----------------------
-----------------------------------


PRIMARY KEY (clustered)
PK_D_COLUMNS
COLUMN_D1, COLUMN_D2


Table is referenced by




--------------------------------




       pubs.dbo.TABLE_E:
FK_COLUMNS
 Usuwanie kluczy
SQL Server umożliwia kasowanie kluczy z wykorzystaniem SQL Enterprise Manager
Manage Tables oraz z linii poleceń poprzez komendę ALTER, TABLE...DROP
CONSTRAINT, której składnia ma postać :




ALTER TABLE table_name DROP CONSTRAINT
constraint_name
Należy przy tym pamiętać, że nie można skasować w danej tabeli klucza
podstawowego jeśli odwołuje się do niego jakiś klucz obcy. Należy najpierw
skasować klucz obcy, a następnie klucz podstawowy.


Data ostatniej modyfikacji 6.II.2000.Wszelkie uwagi mile widziane