C# - Indexek létrehozása

Transact SQL 15. rész

forráskód letöltése
Az adatbázis jobb teljesítményének eléréséhez érdemes indexeket létrehozni. Ebben a részben azt vizsgáljuk meg, hogy erre milyen lehetőségeink vannak
Indexet létrehozni a CREATE INDEX utasítással tudunk. Az INDEX kulcsszó után megadhatunk három kulcsszót, ezek a következők:
  • UNIQUE: egyedi index létrehozása. Ez azt jelenti, hogy nem lehet két olyan rekord, melyeknek az indexben szereplő mezőiben ugyanazok az adatok találhatók. Mielőtt ezt a kulcsszót használnák, meg kell bizonyosodnunk arról, hogy nincsenek az indexelt mezőkben egyforma adatokat tartalmazó rekordok.
  • CLUSTERED: fizikailag is rendezi a rekordokat, illetve a rekordok mindig az indexben meghatározott sorrendben lesznek tárolva. Ebből következik, hogy táblánként csak egy ilyen típusú index lehet. Ha ilyen indexet készítünk, akkor a tábla egészen addig zárolva lesz, amíg az indexelés véget nem ér, hiszen a rekordokat fizikailag is mozgatni kell.
  • NONCLUSTERED: az előző ellentéte. Ezt a kulcsszót nem kell használni, mert ha a CLUSTERED nincs megadva, akkor ez az alapértelmezett.
Az INDEX kulcsszó után kell megadni az index nevét. Ezután jön az ON kulcsszó, majd a tábla neve, majd zárójelben vesszővel elválasztva az indexben szereplő mezők (oszlopok) nevei, szóközzel elválasztva. Maximum 16 mezőt adhatunk meg, de a mezők együttes hossza semmiképpen sem haladhatja meg a 900 bájtos határt.
A WITH kulcsszó után különböző kapcsolókat adhatunk meg. A PADINDEX kapcsoló határozza meg, hogy egy index belső csomópontjai hány sort tartalmazzanak. Ez nem lehet kevesebb, mint két sor. A PADINDEX kapcsoló használatának a FILLFACTOR kapcsolóval együtt van értelme, mivel az abban megadott százalékarányt használja. A FILLFACTOR kapcsolóval állíthatjuk be, hogy az SQL Server mennyi helyet hagyjon fent az indexlapokon a még várható sorok számára. Azoknál a tábláknál növelheti a teljesítményt, amelyekbe gyakran szúrunk be adatokat. A FILFFACTOR utáni szám azt határozza meg, hogy az indexlapok hány százalékát kell szabadon hagyni. A kisebb érték azoknál a tábláknál használható, amelybe még biztosan több adat kerül. Olyan tábláknál, ahol az adatok nem változnak, a kapcsoló értékét akár 100-ra is állíthatjuk.
Az IGNORE_DUP_KEY kapcsoló azt határozza meg, hogy az egyedi index készítésekor (UNIQUE) hogyan viselkedjen az SQL Server, amikor két olyan rekordot talál, melyeknek az indexelt mezőkben lévő adataik megegyeznek. Ha ezt a kapcsolót megadjuk, akkor nem kapunk hibaüzenetet.
A SORTED_DATA használatával a CLUSTERED indexek esetén növelhetjük meg az index elkészítésének hatékonyságát. Akkor adjuk meg, ha biztosak vagyunk benne, hogy az indexelendő adatok már az indexnek megfelelő fizikai sorrendben vannak. Az SQL Server ellenőrizni fogja, hogy az adatok tényleg a helyes sorrendben vannak-e, és ha nem, akkor hibaüzenetet küld. Ellenkező esetben a kapcsoló használata jelentősen meggyorsítja az index elkészítését, hiszen az SQL Servernek nem kell rendeznie az adatokat, és ehhez kevesebb helyre is lesz szüksége.
A SORTED_DATA_REORG kapcsoló a nem CLUSTERED típusú index esetén is fizikailag sorba rendezi az adatokat az indexnek megfelelően, és eközben csökkenti az adatok töredezettségét is, miáltal jelentősen javul a teljesítmény.
Az IGNORE_DUP_ROW kapcsoló a nem egyedi indexszel (UNIQUE), de a CLUSTERED opcióval elkészített indexeknél használható. Ha az index elkészítése közben kettőződött adatokat talál az indexelt mezőkben, akkor törli a kettőzött értékeket, majd hibaüzenetet küld. A ROLLBACK-kel a törölt adatokat visszaállíthatjuk.
Ha az ALLOW_DUP_ROW kapcsolót használjuk nem egyedi, CLUSTERED indexek esetén, a kettőzött adatok megengedettek lesznek. Értelemszerűen az IGNORE_DUP_ROW kapcsolóval nem használható együtt.
Ha már volt előzőleg létrehozott CLUSTERED index, akkor a DROP_EXISTING kapcsolóval töröltethetjük, majd újra létrehozhatjuk azt. Az összes többi index is frissítésre kerül.
A STATISTICS_NORECOMPUTE kapcsolóval kikapcsolhatjuk az elosztási statisztikák frissítését. Ezt a beállítást az UPDATE STATISTICS paranccsal vonhatjuk vissza.
Az ON kulcsszó után megadhatjuk azt a fájlcsoportot, amelyet az index számára szeretnénk kijelölni. Ezzel a nem CLUSTERED indexek teljesítményét lehet javítani. CLUSTERED index esetén nem csak az index, hanem az összes adat is átkerül a megadott fájlcsoportba.
Most nézzük, hogy a példaprogramban mit is csináltunk.
01.sql
Létrehozunk egy teszt adatbázist.
02.sql
Létrehozunk egy táblát három mezővel. Ennek a táblának az adatait fogjuk indexelni.
03.sql
A táblába beszúrunk néhány rekordot, hogy lássuk majd az indexelés eredményét. A beszúrt adatok jelenleg még nincsenek rendezve.
04.sql
Létrehozunk egy egyedi, CLUSTERED (nyaláboló) indexet a tábla A mezőjére.
CREATE UNIQUE CLUSTERED INDEX TestIndex
ON Table1(A)
WITH FILLFACTOR = 10;
Ezáltal az adatok fizikailag is rendezve lesznek az A mezőben tárolt adatok szerint.
05.sql
Töröljük az indexet a DROP INDEX utasítással. Az utasítás után meg kell adni az index nevét, a tábla nevével bevezetve. Mivel az index úgy készült, hogy az adatok fizikailag is rendezve lettek, így a rekordok sorrendje továbbra sem változik meg, de ha új rekordot szúrnánk be, akkor a rendezés már nem történne meg.
DROP INDEX Table1.TestIndex;
06.sql
Töröljük a teszt adatbázist.

Transact SQL cikksorozat

Transact SQL Referencia - Transact SQL 1. rész
Transact SQL további belső változói - Transact SQL 2. rész
Dátumkezelés a Transact SQL-ben - Transact SQL 3. rész
Sztringkezelés a Transact SQL-ben - Transact SQL 4. rész
Folyamatvezérlés eszközei a Transact SQL-ben - Transact SQL 5. rész
Összesítő függvények - Transact SQL 6. rész
Adatbázisok létrehozása, törlése - Transact SQL 7. rész
Adatbázis módosítás lehetőségei - Transact SQL 8. rész
Táblák létrehozása - Transact SQL 9. rész
Táblák módosítása, törlése - Transact SQL 10. rész
Rendszer funkciók - Transact SQL 11. rész
Adatkonvertálási lehetőségek - Transact SQL 12. rész
Tárolt eljárás létrehozása - TransactSQL 13. rész
Triggerek kezelése - Transact SQL 14. rész

Indexek létrehozása - Transact SQL 15. rész

Függvények definiálása - Transact SQL 16. rész
Függvények felüldefiniálása és törlése - Transact SQL 17. rész
Szabályok létrehozása - Transact SQL 18. rész
Alapértelmezett értékek megadása - Transact SQL 19. rész
Nézet táblák létrehozása - Transact SQL 20. rész
Nézettáblák módosítása - Transact SQL 21. rész
Adatbázis és tranzakciós napló mentése - Transact SQL 22. rész
Elmentett adatbázis és tranzakciós napló visszaállítása - Transact SQL 23. rész
Full-Text Catalog létrehozása, használata - Transact SQL 24. rész
Full-Text Catalog további lehetőségei - Transact SQL 25. rész
Tranzakció-kezelés az MS SQL-ben - Transact SQL 26. rész
Tranzakció-kezelés az MS SQL-ben - Transact SQL 27. rész
SET parancsok - Transact SQL 28. rész