Programming | SQL » MS SQL 2000 tételek

1. Általában az SQL Serverről Előzmények A hatvanas években adatokat nagy tömegben jellemzően mágneses háttértárakon tároltak. Az állományok fájlokat jelentettek, a fájlok rekordokból álltak, a rekordokat pedig igény szerint mezőkre lehetett osztani. Hogy egy ilyan adatállományon milyen alapműveleteket lehet elvégezni, azt előszőr a CODASYL szabványosította 1969-ben, amikor leírta a COBOL nyelvet. Az első adatbázis-kezelő rendszert az IBM készítette el szintén 1969-ben (Information Management System, IMS). Ez a rendszer fastruktúrában tárolta az adatokat A CODASYL szabvány később többször módosult, jelentős újításokat is hozott, voltak azonban problémái. 1972-ben mutatta be E. F Codd a relációs adatmodellt A modell a logikailag hasonló egyedeket táblába rendezi, az egyes egyedeket pedig bizonyos tulajdonságai alapján keresi vissza,és ezeket a tulajdonságokat kulcsoknak nevezi el. Az adatmodell elnevezése a matematikai

halmazelmélettel való rokonságra utal. 1976-ban P Chen továbbfejlesztette Codd modelljét, melynek neve ezek után Entity Relationship Model, ERM. Ebben az évben Codd, Chen és C.Date az IBM-nél egy olyan adatbázisnyelv létrehozásán dolgoztak, amely szabványosítja mind az adatszerkezetek létrehozására, mind az adatok kezelésére vonatkozó utasításokat. 1976-ban az IBM be is jelentette a SEQUEL-t (Structured Query Language), aminek a neve a későbbiek folyamán SQL-re rövidült. A nyolcvanas években több más cég is saját SQL változatot fejlesztett ki, az Oracle 1983-ban, illetve a Relational Co. 1984-ben 1986-ban született meg az első ANSI szabvány, 1987-ben pedig az első ISO szabvány. Időközben új cégek jelentek meg a piacon, mint például a Sybase, és a Relational Co. kettéválásával az Informix és az Ingres 1989-ben megszületett a második ISO szabvány. (ISO 9075:1989) 1989 után az objektum orientált programszemlélet és a hálózatok

elterjedése újabb fejlesztéseket tett lehetővé és szükségessé. A harmadik, és máig utolsó szabvány az ISO 9075:1992, vagy röviden SQL92. A szabvány 3 fejlesztési szintet ismer el: entry level SQL92, intermediate level és full SQL92. A Microsoft SQL Server 70 az entry level SQL92-t valósítja meg Mire jó az SQL Server? Az SQL szerver Microsoft Windows platformra fejlesztett rugalmas és nagy teljesítményű, kliens-szerver (ügyfél-kiszolgáló) architektúrájú adatbázis-kezelő rendszer. Képes kezelni akár terabájtnyi méretű adatbázisokat is, ugyanakkor alkalmas kisvállalati és laptop adatbázisok támogatására.Méretezhető, vagyis Windows 95-öt vagy Windows 98-at futtató számítógépektől egészen a Windows NT Server Enterprise Editiont futtató többprocesszoros fürtökig mindegyik felületen ugyanaz a kód futtatható. Az SQL Server automatikus finombeállítást és adatbázis adminisztrációt végez, telepítés után operátori

finomhangolás nélkül is kiváló teljesítményt nyújt. Illeszkedik a Windows NT-hez, az Internet Information Serverhez és a Site Serverhez, így ideális felület elektronikus kereskedelmi alkalmazások fejlesztéséhez. Integrált OLAP szolgáltatásokat nyújt (Online analitikus feldolgozás, a 3. fejezetben lesz róla szó) Az SQL Server részei Az SQL Server kiszolgáló és ügyfél oldali komponensekből áll, valamint azokból a adatbázis objektumokból, amelyek az adatszerkezetek felépítését és az adatintegritási mechanizmusok megvalósítását támogatják. Kiszolgáló oldali komponens az SQL Server szolgáltatás (SQL Server service), az SQL Server Agent szolgáltatás és a Distributed Transaction Coordinator (MS DTC). Az SQL Server szolgáltatás a rendszer legfontosabb komponense. Ő hajtja végre az ügyfél-alkalmazások által küldött SQL utasításokat és ő kezeli az adatbázisok összes állományát. Elosztja a rendelkezésre álló

erőforrásokat egyidejű elérés esetén, felügyeli és megőrzi az adatbázisok integritását. Az SQL Server Agent ütemezési és risztási feladatokkal foglalkozik. A feladatok kötegekbe rendezhetők, és egy tetszőleges időpontban indíthatók, vagy akár bizonyos időközönként többször is végrehajthatók. A risztások eseményhez kötöttek, ilyenkor üzenetet küldhetünk emailben vagy személyi hívóra , ütemezett feladatot indíthatunk Az MS DTC, vagyis elosztott tranzakció kezelő, ahogy neve is mutatja, olyan tranzakciók lebonyolításáért felelős, amelyek különböző helyeken lévő adatforrásokat használnak egyidejűleg. Az MS DTC felel azért, hogy a tranzakcióban előírt módosítások mindegyik adatforráson végrehajtásra kerüljenek, vagy hiba esetén sehol se történjen változás. Az ügyfél oldali komponensek lehetőséget nyújtanak az adatbázisok megtervezésére, az adatok karbantartására és lekérdezésére, egyéb

adminisztrációs feladatok elvégzésére. Legfontosabb eszközünk az SQL Enterprise Manager. Az SQL Server 70-ban az Enterpise Manager felülete a Microsoft Management Console (MMC). Az MMC a Microsoft egységes adminisztrációs felülete, része az operációs rendszernek, és az egyes programok csak snap-in modulokat mellékelnek hozzá. Egy ilyen MMC snap-in az Enterprise Manager is Nézzük meg, telepítés után milyen fontosabb ügyfél oldali programokat kapunk az Enterprise Manageren kívül. • Performance Monitor: A Windows NT Server saját Performance Monitorjához tartozó kiegeszítés, az SQL Server működésének és teljesítményének átfogó monitorozását teszi lehetővé. • Profiler: A kiszolgáló segédprogram. • Query Analyzer: Alapvetően lekérdezések lekérdezéseink optimalizálására is alkalmas. • Client Network Utility: Az ügyfél oldali programkomponensek konfigurációjának kezeléséhez használatos segédprogram. • Service

Manager: A szolgáltatások (services) elindítására és leállítására szolgáló program. működésének figyelésére és futtatására naplózására szolgáló használható program, de Az előbb felsorolt grafikus felülettel rendelkező programok mellett meg kell említeni két fontos pareancssori eszközt is. A bcp adatexportra és -importra használható Paraméterekkel megadható formátumba írhatjuk ki (vagy éppen olvashatjuk be) az adattáblák tartalmát. Az osql-t SQL utasítások parancssori végrehajtására használhatjuk. Az SQL Serverrel ODBC-n keresztül kommunikál. Az SQL Server adatbázisai jórészt táblákból épülnek fel. A táblák mellett azonban más adatbázis objektumok is szerepet játszanak. A legfontosabbak a következők (későbbi fejezetekben mindegyik elemről részletesen lesz szó): Tábla: Az adatok tárolásának helye. Az egyed-előfordulásokat sorok reprezentálják, az egyedek tulajdonságait az oszlopok. Adattípus:

Az egyes oszlopok lehetséges adatértékeit határozza meg. Constraint: Az oszlopok lehetséges értékeire szabályokat adhatunk meg, ezek a szabályok a constraint-ek. Default: Ha egy oszlopban nem adunk meg értéket, az automatikusan a default értékét veszi fel. Index: Az adatok gyorsabb elérését lehetővé tevő tárolási szerkezet. View: Fizikailag különböző helyeken lévő adatokat logikailag egy táblába, view-ba rendezhetjük. Stored procedure: SQL utasítások egy halmaza, egy rutin, ami meghívásakor egyszerre hajtódik végre. Trigger: Bizonyon események bekövetkeztekor automatikusan végrehajtódó tárolt eljárás. Az adatbáziskezelésről általában (SQL Server szemszőgből) A relációs adatbázisok alapelemei a táblázatok. Nevezzük egyednek azt a valamit, amit le akarunk írni, és hívjuk tulajdonságnak azokat a valamiket, amivel le akarjuk írni az egyedet. Az egyes egyed-előfordulások a táblázatban sorokba rendeződnek úgy, hogy azonos

jellegű tulajdonságai oszlopokat alkotnak. Példaképpen álljon itt egy tábla az SQL Server példa adatbázisából. Employees EmployeeID LastName Title City BirthDate 1 Davolio Sales Representative Seattle 1948.1208 2 Fuller Vice President, Sales Tacoma 1952.0219 Ez tábla a NorthWind cég alkalmazottait tartalmazza. Az előző nómenklatúra szerint Fuller egy egyed, aki az alkalmazott nevű egyedtípusba tartozik. Az egyedtípus tulajdonképpen egy absztrakt osztály, ahová az egyedek tartoznak. Példánkban az egyedek alkalmazottak, és nem katonák vagy mesehősők. Ugyanígy a tulajdonságok is tulajdonságtípusba tartoznak Fuller egy tulajdonsága például, hogy 1952.0219, ami tulajdonság a születési idő tulajdonság típusba tartozik. Az SQL Serverben az egyes tábláknak egyedi nevük van, a neveket célszerű úgy megválasztani, hogy az utaljon a tárolt információ jellegére. A táblák sorokból és oszlopokból épülnek fel A sorokat rekordoknak is

nevezzük, az oszlopok a rekordokat mezőkre osztják. A sorok és oszlopok sorrendje közömbös. Egy táblán belül minden oszlop nevének egyedinek kell lennie Az táblanevek és oszlopnevek egyediségéről az SQL Server fizikai szinten gondoskodik. A táblában található minden rekordnak is különbözőnek kell lennie. Erre a korlátozásra nincs direkt módon ellenőrzés, tehát elvileg kreálhatunk azonos egyedeket tartalmazó táblát. Ám ennek nem sok értelme van, hiszen így nem tudnánk eldönteni egy adott sorrol, hogy az most melyik egyedre vonatkozik. Ezt a korlátozást egy speciális rendeltetésű oszlop vagy oszlopcsoport érvényesíti, az elsődleges kulcs. A kulcs értéke minden sorban különböző Léteznek oszlopszintű korlátozások is, ilyen például a Not Null vagy a Unique korlátozás. A Not Null korlátozás megköveteli, hogy az oszlopban legyen adat. Ha nics ilyen korlátozás, az oszlop értéke lehet Null, ami gyakorlatilag azt jelenti,

hogy nincs adat. Nem azonos a 0 vagy üres sztring adattal. A Unique korlát megakadályozza, hogy egy oszlop két azonos tulajdonságot tartalmazzon, vagyis garantálja, hogy a mezők értékei egyediek és nem ismétlődnek. Saját magunk is létrehozhatunk tetszőleges oszlop szintű korlátozást. Elsődleges kulcs (primary key) az az oszlop vagy oszlopcsoport, melynek értékei egyértelműen azonosítják a tábla egyes sorait. Példánkban ez az EmployeeID mező Ennek értéke minden egyedben más, ezzel biztosítjuk, hogy az egyedek minden körülmények között azonosíthatók legyenek. Minden táblában kell legyen elsődleges kulcs, de egy táblában csak egy elsődleges kulcs lehet definiálva. Az elsődleges kulcs értéke nem lehet Null érték Egy kicsit nehezebb fogalom az idegen kulcs fogalma. Idegen kulcs (foreign key) az a mező vagy mezőcsoport, melynek értéke azonos egy ún. kapcsolt tábla elsőfleges kulcsának értékével Egy elsődleges kulcs tehát nem

csak a saját táblára vonatkozhat, hanem idegen kulcsa is lehet egy másik táblának. Az idegen kulcs haszna, hogy összekapcsolhatunk két (vagy akárhány) táblát Az ilyen módon összekapcsolt táblák közti integritást az SQL Server biztosítja, vagyis garantálja, hogy az adatbázisba nem kerül olyan rekord, aminek egy foreign key mezeje nem szerepel a kapcsolódó tábla primary key mezejében. Nézzünk erre egy példát publishers pub id, PRIMARY KEY pub name city titles title id, PRIMARY KEY title pub id, FOREIGN KEY price A példában két tábla szerepel. A kiadók (publishers) táblában szerepel a kiadó neve és a városa, a tábla elsődleges kulcsa a pub id nevű mező. A címek táblában szerepel a cím és az ár, az elsődleges kulcsként funkcionáló title id mező, és a pub id idegen kulcs. Ez mutat a kiadók tábla pub id mezejére, és az adott címhez hozzárendeli a kiadót. Világos, hogy a két pub id mező azonos értékeket vehet csak fel,

ellenkező esetben lenne olyan könyv, amihez nem tudunk kiadót párosítani. Az SQL Server gondoskodik arról, hogy ne lehessen kitörölni egy olyan kiadót, akihez ilyen módon könyvek tartoznak 2. Telepítés Rendszer követelmények Az S QL S erver f uttatásához e gy Intel ( vagy a zzal kom patibilis) a lapú rendszerre, va gy D EC Alpha ( vagy azzal ko mpatibilis) a lapú r endszerre va n s zükség. A z E nterprise vá ltozat memóriaigénye legalább 64MB, más kiadások ennél kevesebbel is beérik. Ha mindent telepíteni akarunk, 1 80MB m erevlemez t erületre l esz s zükségünk. A z E nterprise vá ltozat ope rációs rendszer igénye Windows NT Server Enterprise Edition 4.0 SP4 A Standard verzió a Windows NT Server 4.0 SP4-et szereti látni maga alatt, a Desktop változat mind Windows NT Workstation 4.0 S P4-en, m ind W indows 95/ 98 ope rációs r endszereken f ut M indegyik i gényli a zonban a Microsoft Internet Explorer 4.01 SP1-et Az S QL S erver s

zolgáltatásai ( MSSQLServer, S QLServerAgent) egy b ejelentkezési f iók ( user account) j ogosultsági kö rnyezetében f utnak. E zért m ég i nstallálás előtt célszerű létrehozni egy ilyet. E z az a ccount (bejelentkezési f iók) l ehet Local s ystem, Local u ser v agy Domain u ser típusú. Hálózati jogosultsággal csak az utóbbi rendelkezik, ezért SQL Serverek közti interakciók végrehajtásához a D omain us er t ípusú account s zükséges. ( Ilyen tevékenység p éldául a replikáció, hálózati meghajtóra történő backup, az SQL Server Agent levélküldési funkciója.) Még a telepítés előtt el kell döntenünk, hogy milyen karatkterkészletet, sorbarendezést és Unicode r endezést ál lítunk be. Ezeket a telepítés során kell kiválasztanunk, és későbbi módosításuk meglehetősen körülményes, gyakorlatilag újra kell építeni az adatbázisunkat és visszatölteni az adatokat. A k aralterkészlet ( vagy code p age) a j elek az on h

almaza, am iket az S QL S erver k arakter al apú mezeinek kitöltéséhez igénybe vehetünk. Minden karakterkészlet 256 értéket tartalmaz Az első 128 é rték m inden ké szletben u gyanaz, a m ásodik 128 ka rakter t artalmazza a n yelv s pecifikus betűket és jeleket. Az SQL Serverben a következő karakterkészletek választhatók Code page 1252 850 437 874 932 936 949 950 1250 1251 1253 1254 Leírás ISO k arakterkészlet ( Egyéb el nevezések: ISO-8859-1, Latin 1, ANSI), kom patibilis a windows ope rációs r endszerek A NSI karakterkészletével. Többnyelvű karakterkészlet, az amerikai kontinens nyelvei és a legtöbb európai nyelv számára. U. S English Thaiföldi Japán Kínai (egyszerűsített) Koreai Kínai (hagyományos) Közép-európai karakterkészlet. Magyar nyelvű adatbázisokhoz ezt k ell h anszálni. A t elepítés f olyamán ez az alapértelmezett kódlap. Cirill Görög Török 1255 Héber 1256 Arab 1257 Balti A sorbarendezés megadásával

meghatározhatjuk, hogy a lekérdezések eredményhalmazát hogyan rendezze az S QL S erver m egjelenítéskor. Ugyanakkor a l ekérdezések v égrehajtásakor i s számtalanszor va n r á s zükség, ho gy ké t karakterlánc egyenlőségét eldöntsük. A sorbarendezés megadásával eh hez d efiniálunk s zabályokat. C ase-insensitive esetben a nagybetű és a kisbetű között ni ncs kül önbség ( A= a). A ccent-insensitive esetben az ékezetes és ékezet nélküli betűk között n incs kül önbség ( a= á). Bináris r endezéskor az eg yes k arakterek a k arakterkészletben meghatározott sorszámuk szerint rendeződnek. Ez a leggyorsabb rendezési mód Magyarországon a Hungarian dictionary order, case insensitive sorrendet érdemes választani. Az Unicode rendezés a Unicode típusú adatok sorbarendezését határozza meg. Ez nem ugyanazt jelenti, m int s orbarendezés, d e aj ánlott u gyanazokat a p aramétereket v álasztani a b eállításánál, tehát

Magyarországon a Hungarian, case insensitive beállítást. Telepítés és az SQL Server szolgáltatás futtatása A t elepítés ak ár a C D-ről. akár hálózati meghajtóról futtatható A telepítéshez Adminisztrátori jogosultság szükséges. A t elepítés i ndítása u tán b e k ell í rnunk a f elhasználó n evét, a cé g n evét és a s orozatszámot. Ezeket a mezőket kötelező kitölteni. Ha már van korábbi verziójú SQL Server a gépen, a telepítő megkérdi, hogy a telepítés után indítsa-e az SQL Server Upgrade Wizard-ot; válaszoljunk Yes-t. Ezután három telepítés-típus közül választhatunk. Typical: Az SQL Server mellé feltelepül az összes kezelőeszköz és a Books Online. A l egtöbb felhasználónak ez a telepítési mód ajánlott. A telepítő a telepítés során nem kérdez rá mindenre Minimum: Azt a legkisebb konfigurációt telepíti, ami még szükséges az SQL Server futtatásához. Nem telepíti a dokumentációt, a menedzsment

eszközöket, az upgrade eszközöket. Custom: A Typical telepítési mód beállításait kínálja, amit módosíthatunk. Ez ut án m eg ke ll h atároznunk, hog y a p rogramok hova t elepüljenek. T ömörítést ha sználó fájlrendszert nem adhatunk meg az adatállományok céljaként. Ezután kell beállítanunk az előző szakaszban már tárgyalt karakterkészletet, sorbarendezést és Unicode rendezést. A következő képernyőn ki kell választanunk, hogy mely hálózati könyvtárak települjenek. A hálózati könyvtárak s zolgálnak az S QL S erver és az ü gyfél-alkalmazások köz ti kom munikáció lebonyolítására. E gyszerre tö bb il yen k önyvtárát is te lepíthetünk, a lapértelmezés s zerint min d települ. A h álózati k önyvtárakat ( Net-Library) a Server Network Utilityvel tudjuk később konfigurálni. Alapértelmezett könyvtár a Named Pipes Ez után kell az SQL Server szolgáltatásaihoz felhasználói fiókot rendelnünk. A t elepítés b

efejezése u tán az S QL S erver az onnal h asználható, n em s zükséges a s zámítógépet újraindítani. Ha el kell távolítanunk az SQL Servert gépünkről, válasszuk a Start menüből a Microsoft SQL Server 7.0 szakasz Uninstall SQL Server 70 pontját Telepítéskor célszerű a szolgáltatások automatikus indítását kiválasztani, így a számítógép indulásakor azok is elindulnak. Ellenkező esetben nekünk kell elindítanunk Ezt megtehetjük az Enterprise Manager-ben, a S ervice Manager-ben, illetve a C ontrol Panel Services programjával. Mint minden szolgáltatást, az SQL Server szolgáltatásait is lehet parancssorból indítani: net start mssqlserver, net start sqlserveragent. Frissítés korábbi verziókról Lehetőségünk van korábbi (6.x) verziókról frissíteni, ehhez az SQL Server Upgrade Wizard-ot kell használnunk. A wizard frissíteni fogja az összes adatbázist a benne lévő adatokkal együtt, átveszi a r eplikációs b eállításokat, a

z S QL E xecutive b eállításait i lletve az S QL S erver 6 .x konfigurációs beállításait. A frissítés után két SQL szerver installáció lesz a gépünkön, egy SQL Server 7.0 és egy SQL Server 6x A régebbi verzió is működni fog, az adatbázisok egymástól függetlenek. T ehát a w izard a r égi S QL S ervert ne m t ávolítja e l, ha már ni ncs r á s zükség, magunknak kell törölnünk a meghajtóról. Frissítés előtt célszerű ellenőriznünk a számítógépet, hogy az SQL Server 7.0 hardverkövetelményeinek i s el eget t esz-e. Továbbá elegendő helyet kell biztosítani a keletkező adatbázis és log állomány számára. Ha f rissítés u tán m eg is ta rtjuk a r égi S QL S erver-ünket, a gépen e gyszerre cs ak az e gyiket futtathatjuk. A f rissítés után az ú j v erzió l esz ak tív H a a r égit s zeretnénk h asználni, át k ell kapcsolnunk a M icrosoft S QL S erver-Switch al kalmazás i ndításával, v agy akár p arancssorból a

vswitch.exe indításával 3. Rendszer felépítés Logikai komponensek Egy SQL Server adatbázist egyszerre több különböző adatbázis objektum reprezentál. Ezek a következők: Table, Constraint, Default, Trigger, Index, View, Stored procedure. Table. A z ad atbázis adatai t áblákban t árolódnak M inden t ábla az ad atok v alamilyen szempontból hasonló halmazát tárolja. Például egy iskolát leíró adatbázisban bizonyára találunk olyan táblákat, mint tanárok táblája, diákok táblája, osztályok táblája, stb. Az SQL Server táblái sorokból és os zlopokból állnak. Az os zlopok t ulajdonság-típusoknak megfelelő tulajdonságokat tartalmaznak, a s orok p edig egyed-előfordulásokat tartalmaznak. Például a tanárokat (egyedtípus) nyilvántartó tábla egyik sorában Torna József (egyed-előfordulás) szerepel Ha kíváncsiak vagyunk a t anárok ha jszínére ( tulajdonság-típus), akkor tárolnunk kell egy ennek megfelelő mezőt, aminek Torna

József esetén legyen az értéke barna (tulajdonság). Az egyes objektumok tulajdonságai nagyon sokfélék és különbözőek lehetnek Az oszlopok egyik fontos jellemzője az adat típus, ami meghatározza, hogy az oszlopban milyen típusú adatok tárolhatók. A legfontosabb adat típusok a követezők: binary, bit, char, datetime, float, int, varchar. Ha a meglevő adat típusok nem elegendőek, saját adattípust hozhatunk létre Egy kicsit előreszaladva készítsünk például egy birthday nevű adat típust, ami olyan, mint a datetime típus, és megengedi a Null értéket: EXEC sp addtype birthday, datetime, NULL GO Készítsünk egy olyan táblát, ahol az egyik oszlop ilyen típusú: CREATE TABLE employee( emp id char(5), emp first name char(30), emp last name char(40), emp birthday birthday) Tekintsük át a legfontosabb adat típusokat kicsit részletesebben. Egész érték tárolására 4 típus áll rendelkezésre. A bit típusú adat lehet 1, 0 vagy Null Az int

típus 4 b yte-os előjeles egész, vagyis -2^31-től (-2,147,483,648) 2^ 31-1-ig ( 2,147,483,647) ve het fel értékeket. K isebb t estvére a s mallint, ez cs ak k ét b yte-on t árolódik, t ehát -32,768 é s 32,767 közötti értékeket vehet fel. A család legkisebb tagja a tinyint (1 byte, 0-255) Két lebegőpontos típust használhatunk, a float és a real típusokat. Ezek úgynevezett pontatlan számtípusok, vagyis pontosságuk korlátozott. A float típus ábrázolási tartománya -179E+308-tól 1.79E+308-ig t erjed 4 vagy 8 b yte-on t árolódik egy opcionális n paramétertől függően (float [n]), és pontossága 7 va gy 15 jegy. A real típus 4 byte-os, ábrázolási tartománya –340E+38-tól 3.40E+38-ig terjed Két nagyobb pontosságú adat típus a numeric és a decimal. A két típus ugyanazt valósítja meg, az ANSI kompatibilitás miatt lettek beépítve. Megadásuknál meg kell határoznunk a pontosságot és az ábrázolandó nagyságrendet. A tárolás

mérete ezektől a paraméterektől függ Példa: numeric(20,6) ol yan s zám t ípusa, a melyikben 2 0 s zámjegyet s zeretnénk t árolni, és ebből 6 tizedes jegy. Karakteres (nem U nicode) ad atok t árolására 3 t ípus ál l r endelkezésre. A ch ar t ípus e gy fix hosszúságú ka rakterláncot t árol, m aximális m érete 8000 ka rakter. A v archar t ípusú vá ltozó ugyanezt t eszi, d e cs ak an nyi m emóriát f oglal l e, am ilyen h osszú az ak tuális k arakterfüzér. A 8000 ka rakternél hos szabb a datok ke zelésére s zolgál a t ext t ípus. E bben l egfeljebb 2 31-1 (2,147,483,647) karaktert tárolhatunk. A dá tumok ke zelésére két t ípus á ll r endelkezésre. A 8 b yte-os d atetime, é s a 4 b yte-os smalldatetime. Utóbbi 1900-tól 2079-ig használható, nagyobb testvére 1753-tól 9999-ig Még egy különbség van a két típus között, az első sokkal pontosabb, millimásodperces pontossággal tárolja az időt, utóbbi csak perces

pontossággal. View. A view nem más, mint egy virtuális tábla Egy viewban látott adathalmaz nem feltétlenül tárolódik e gyetlen obj ektumban. Legtöbbször több t ábla a dataiból á ll, a mit e gy S ELECT utasítással v álogatunk l e. A v iewkat el l ehet n evezni, és u gyanúgy v iselkednek, m int a t áblák Mire jó hát akkor egy view? • Egy vi ewn ke resztül a táblának c sak bi zonyos s orait m utatjuk meg a f elhasználónak. Például egy alkalmazott a tábla csak rá vonatkozó részét nézheti meg. • Egy viewn keresztül a táblának csak bizonyos os zlopait mutatjuk meg a felhasználónak. Például egy alkalmazottnak meg szeretnénk mutatni a többi alkalmazott nevét, osztályát, beosztását, stb., de a fizetési információkat nem • Logikailag összetartozó adatokat gyűjthetünk össze, és úgy mutathatjuk meg őket, mintha egyetlen táblában lennének. Stored Procedure. A tárolt eljárás SQL utasítások egy előre lefordított

halmaza A t árolt eljárások az alkalmazásban megvalósítandó logika leképezésének legfőbb eszközei. A későbbiekben bővebben lesz róla szó. Constraint. A constraint-ek segítségével szabályokat definiálhatunk arra vonatkozólag, hogy az egyes oszlopokba az adatokat milyen megszorításokkal írhatjuk csak be. Ötféle constraint van: NOT NULL: Az oszlopban nem fordulhat elő Null érték. CHECK: Egy logikai kifejezéssel megszűrhetjük az oszlopba írható adatokat. A következő példában a tábla cust id oszlopába csak 0 és 10000 közti értékeket engedünk meg. CREATE TABLE cust sample ( cust id int PRIMARY KEY, cust name char(50), cust address char(50), cust credit limit money, CONSTRAINT chk id CHECK (cust id BETWEEN 0 and 10000 ) ) UNIQUE: Ez a constraint biztosítja, hogy egy oszlopban a nem Null értékek közül biztosan nem lesz két megegyező. Ezt a tulajdonságot a Primary key is megköveteli, de ő a Null értékeket sem engedi meg. PRIMARY K EY:

E z a constraint j elöli ki a zt a z os zlopot va gy os zlopcsoportot, am i az eg yes rekordokat megkülönbözteti egymástól. Minden táblában kell primary keyt deklarálni FOREIGN K EY: E z a constraint a zonosítja a t áblák köz ti ka pcsolatokat. A F oreign ke y egy másik tá bla e gyedi azonosítójára k ell mu tasson, a mi le het a bban a tá blában a kár pr imary k ey vagy unique constraint-tel ellátott oszlop. Default. A de fault-ok határozzák meg egy mező értékét, ha beszúráskor annak mi nem adunk értéket. A d efault b ármi l ehet, am i eg y é rtéket ad er edményül: l ehet k onstans, f üggvény v agy matematikai kifejezés. Egy példa default létrehozására: CREATE TABLE test defaults (keycol smallint, process id smallint DEFAULT @@SPID, date ins datetime DEFAULT getdate(), mathcol smallint DEFAULT 10 * 2, char1 char(3), char2 char(3) DEFAULT xyz) GO Trigger. A tr igger egy s peciális tá rolt e ljárás, ami a kkor ha jtódik vé gre a

utomatikusan, ha e gy táblára kiadunk egy Update, egy Insert vagy egy Delete utasítást. A triggerek arra jók, ho gy az adatok m ódosításával kapcsolatos s zabályokat a utomatizálni l ehessen. A későbbiekben részletesen lesz róla szó. Index: A z i ndexek ol yan t áblához ka csolt s truktúrák, a mik a z a datok g yors vi sszakeresését hivatottak m eggyorsítani. A r ekordokra kul csokkal hi vatkozunk, e zeket a kul csokat t ároljuk a z indexben ol yan m ódon, hog y az SQL Server azt a lehető legrövidebb időn belül megtalálja. Szintén részletes tárgyalásra kerül később. Fizikai komponensek Az alapvető adattárolási egység az SQL Serverben a lap (page). Egy lap mérete 8 KB, vagyis 1 megabájtnyi a dat 128 l apon t árolódik. M inden lap egy 96 byte hosszú fejléccel kezdődik, itt tárolódik például a lap típusa. Hat különböző típusú lap van: Data (ilyen l apokon t árolódik minden adat, kivétel a text, ntext és image típusúak),

Index (index bejegyzések), Text/image (text, ntext, i mage), és m ég há rom s peciális t ípusú l ap, a min r endszerinformációk t árolódnak. A z adatsorok köz vetlenül a f ejléc ut án h elyezkednek e l. M ásik l apra s oha ne m t erjednek át, í gy maximális méretük 8060 byte. A táblák és indexek tárolásának alapegységei az extent-ek. Egy extent 8 e gymás után következő lapból áll (64kB). Az SQL Server soha nem foglal le egyetlen táblának egy extent-et, így kisebb méretű táblák és indexek megosztozhatnak egy-egy extent-en. A log (napló) állományok más struktúrában tárolódnak. Az SQL Server az adatbázisokat fájlokban tárolja. Az adat és log információk sohasem kerülnek ugyanabba a fájlba, és a különböző adatbázisok is különböző fájlokban tárolódnak. Háromféle fájltípus van: • Elsődleges adatfájl. Minden adatbázisnak van Kiterjesztése mdf • Másodlagos adatfájl. Az adatbázis azon része tárolódik

itt, ami belső szervezési okokból nem kerül az elsődleges fájlba. Lehet, hogy egy adatbázisban nincs ilyen, de az is lehet, hogy az elsődleges adatfájl mellett több is van. Kiterjesztése: ndf • Naplófájl (log): A tranzakciós naplót tartalmazza. Több is lehet belőle, kiterjesztése: ldf Tehát minden adatbázis rendelkezik egy elsődleges adatfájllal és egy, esetleg több naplófájllal. Az adatbázisban a lapok szekvenciálisan sorszámozva helyezkednek el, az első lap száma 0. minden ad atfájlnak e gyedi az onosítója v an, ezz el a k ét s zámmal az SQL S erver m inden l apot azonosítani tud. A t ranzakciós n apló t árolja az Insert, D elete és U pdate u tasítások v égrehajtásakor t örtént módosításokat. H a va lamilyen hi ba m eghiúsítja a t ranzakciót, a n apló alapján a b efejezetlen tranzakciókat vissza lehet görgetni. Adattárházak és az OLAP 4. Adatbázisok kezelése Rendszer adatbázisok Az SQL Serverben négy ún.

rendszer adatbázis kerül telepítésre Master: A master adatbázis tárol az SQL Server működésével kapcsolatos minden rendszer szintű információt, így g yakorlatilag a z SQL Server le lke. I tt tárolódnak a felhasználói f iókok, a rendszer konfigurációs beállításai, minden más adatbázis összes paramétere, pl. az őket tároló elsődleges adatfájlok pontos helye. Tempdb: E z az ad atbázis a h elye az át meneti t ábláknak és t árolt el járásoknak. A l ekérdezések során generálódó munkatáblák is itt jönnek létre. Minden SQL Server induláskor újra létrejön Ez az adatbázis is képes növelni méretét, ha szükséges, de újrainduláskor mindig az eredeti méretben jön létre. Model: E z e gy min taadatbázis, ú j a datbázis l étrehozásakor k ap s zerepet, az ú j ad atbázis egyszerűen ennek a másolata lesz. Fontos, hogy ez az adatbázis mindig létezzen, hiszen a tempdb-nek minden induláskor elő kell állnia, ehhez pedig szükség

van a model mintára. Msdb: E zt a z ad atbázist az S QL S erver Agent ha sználja, e bben t árolódnak a z üt emezett feladatok. Adatbázis létrehozása Az adatbázis létrehozása előtt el kell dönteni az adatbázis nevét, a tulajdonosát, a méretét és azoknak a fájloknak a ne vét és h elyét, a hol m ajd a z a datbázis t árolódni f og. A datbázis létrehozására többféle mód is kínálkozik, mi ebből kettőt fogunk megmutatni. Enterprise Manager: 1. Kattintsunk az SQL Server Group-ra, majd az SQL Server nevére 2. Jobb oldali gombbal kattintsunk a Databases-re, majd New Database 3. Töltsük ki az adatbázis neve mezőt Ekkor létrejön az elsődleges adatfájl és a tranzakciós napló, ha pl. MyDB az adatbázis neve, egy MyDB Datamdf és egy MyDB Logldf nevű fájl jön létre. Amikor egy adatbázis létrejön, nem történik más, mint hogy másolat készül a model nevű adatbázisról, ezért a kezdeti mérete is ugyanakkora lesz. Az elsődleges adatfájl

ekkor még csak rendszertáblákat tartalmaz. 4. Az elsődleges adatfájl paramétereinek megváltoztatásához a General fülön, a tranzakciós napló f ájl p aramétereinek v áltoztatásához a T ransaction Log f ülön ke ll m atatnunk. Mindkét fülön szerepelnek a File name, a Location, Initial Size oszlopok, ide beírhatjuk a fájl ne vét, ki jelölhetjük a pont os he lyét a m eghatón é s m eghatározhatjuk, hog y m ilyen méretű legyen a fájl. 5. Ha az ad atbázis n övekedése során eléri az őt tároló fájl méretét, az SQL Server automatikusan me gnöveli a f ájl mé retét. H ogy miképpen, a zt mi állíthatjuk b e Lehetőségünk van a növekedést megabájtokban (In megabytes) vagy százalékban (By percent) m egadni. H a n em a karjuk, ho gy a f ájl m érete m agától v áltozzon, n e l egyen kiválasztva a z A utomatically grow f ile. M egadhatunk e gy m aximális fájl mé retet is (Restrict f ilgrowth) va gy e ngedhetjük, hog y a növe kedés s

zükség e setén m indig megtörténjen (Unrestricted filegrowth). 6. Ugyanezek a kapcsolók természetesen a tranzakciós naplóra is igazak T-SQL: (A T-SQL az SQL Server 7.0 programozására szolgáló kiszolgáló programnyelv Részletesen a 6. fejezetben kerül bemutatásra) Az adatbázis létrehozásához a master adatbázisban kell lennünk. Az adatbázis létrehozásakor azzal egyidejűleg a tranzakciós napló is létrejön. Adatbázis létrehozása a CREATE DATABASE utasítással történik: CREATE DATABASE adatbázisnév [ ON [PRIMARY] [ fájl [,.n] ] ] [ LOG ON { fájl [,.n]} ] Ahol a fájl ::= ( [ NAME = logikai fájlnév, ] FILENAME = operációs rendszer fájlnév [, SIZE = méret] [, MAXSIZE = { max.méret | UNLIMITED } ] [, FILEGROWTH = növekedés] ) [,.n] Az E nterprise M anager-rel l étrehozott ad atbázis m egbeszélése u tán a p arancs p araméterei m ár beszédesek. P éldaképpen hoz zunk l étre e gy adatbázist S ales né ven A f ájlok l egyenek a z

mssql7data kön yvtárban. Az elsődleges fájl kezdeti mérete legyen 10 MB, 5 MB-onként növekedjen, d e 50 M B-nál n e t ovább. U gyanezek a p araméterek a t ranzakciós n apló es etében legyenek 5 MB, 5 MB és 25 MB. A parancs ekkor a következő: 1. USE master GO CREATE DATABASE Sales ON ( NAME = Sales dat, FILENAME = c:mssql7datasaledat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales log, FILENAME = c:mssql7datasalelog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO Nézzünk még néhány példát! 2. USE master GO CREATE DATABASE Products ON ( NAME = prods dat, FILENAME = c:mssql7dataprods.mdf, SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1 ) GO Products néven hoz tunk l étre adatbázist. Mivel csak az elsődleges adatfájlt specifikáltuk, egy 1 MB-os t ranzakciós na pló j ön l étre a utomatikusan. A s ize pa raméternél ne m í rtunk M B-ot va gy KB-ot, a MB az alapértelmezett. A tranzakciós naplóra nincs méretkorlátozás 3. CREATE

DATABASE mytest Említettük, hogy az adatbázis létrehozása a model nevű rendszeradatbázis lemásolását jelenti. Mivel itt nem adtunk meg semmilyen paramétert, a mytest nevű adatbázis minden tekintetben meg fog egyezni a model adatbázissal. 4. USE master GO CREATE DATABASE Products2 ON ( NAME = prods2 dat, FILENAME = c:mssql7dataprods2.mdf ) GO Mivel csak a neveket határoztuk meg a létrejövő adatbázis mérete a modal adatbázis méretével fog megegyezni. A tranzakciós napló automatikusan létrejön, mérete ilyenkor mindig negyede az adatbázis m éretének, de m inimum 512 K B. M ivel M AXSIZE pa raméter ni ncs m egadva, a z állományok igény szerint növekedhetnek. Adatbázis módosítása és törlése Előfordul, hogy az adatmennyiség növekedése miatt szeretnénk megnövelni az adatbázis méretét. Ezt megtehetjük az E nterprise M anager-rel v agy az A LTER D ATABASE u tasítással. H árom dolgot tehetünk. Automatikus növekedést állítunk be, ahogy

az előző szakaszban leírtuk Megnöveljük az aktuális adatfájl vagy naplófájl méretét, vagy másodlagos fájlokat hozunk létre. Az ALTER DATABASE utasítás így néz ki: ALTER DATABASE database { ADD FILE fájl | ADD LOG FILE fájl | REMOVE FILE logikai fájlnév | MODIFY FILE fájl } ahol a fájl ::= (NAME = logikai fájlnév [, FILENAME = operációs rendszer fájlnév ] [, SIZE = méret] [, MAXSIZE = { max.méret | UNLIMITED } ] [, FILEGROWTH = növekedés] ) A következő példában létrehozunk egy adatbázist, aztán hozzáadunk még egy adatfájlt: USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = c:mssql7data 1dat1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = c:mssql7data 1dat2.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO Ha egy adatbázis nagyobb, mint amekkorára szükség van, méretét csökkenthetjük. Ahogy a méret növelésénél, itt is lehetőség

van k ézi és au tomatikus m éretváltoztatásra. Egy adatbázis m éretét maximum az er edeti m éretére csökkenthetjük vissza! A z E nterprise Manager-ben álljunk a z adatbázisunkra, m ajd j obb ka ttintás ut án vá lasszuk a z A ll T asks/Shrink D atabase. pa rancsot Állítsuk be a kí vánt m éretcsökkenést é s ké sz i s va gyunk. A z a utomatikus m éretcsökkenés beállításához az adatbázison a Properties-re majd az Options fülre kattintsunk. A megfelelő T-SQL utasítás a következő: DBCC SHRINKDATABASE ( database name [, target percent] ) Egyetlen fájl méretcsökkentésére is lehetőség van, ekkor a DBCC SHRINKFILE utasítást kell használnunk. Számtalan beállítási lehetőség van, amivel szabályozni tudjuk egy adatbázis működési tulajdonságait. E hhez a z E nterprise M anagerben a z a datbázis P roperty/Options r észébe ke ll ellátogatnunk. Ha e gy a datbázisra ni ncs t öbbé s zükség, ki ke ll t örölnünk. A z E nterprise M

anagerben j obb egérgombbal kattintva az adatbázisra válasszuk a Delete parancsot. Az ennek megfelelő T-SQL utasítás a következő: DROP DATABASE database name 5. Táblák Táblák tervezése Egy tábla létrehozása előtt célszerű terveinket papírra vetni. A következő döntéseket kell meghoznunk: • A tárolni kívánt adatok típusa. • Milyen oszlopokat szeretnénk létrehozni, azokat milyen jellemzőkkel (adattípus, hossz, stb.) • Mely oszlopokban engedünk meg Null értéket. • Hol fogunk default-okat megadni, és hol fogunk megszorításokkal (constraints) élni. • Milyen indexekre van szükségünk, mik lesznek elsődleges és idegen kulcsok. Minden táblában létrehozhatunk egy ún. a zonosító oszlopot, amibe az SQL Server által generált egyedi s orszámok f ognak ke rülni. E zen os zlop é rtékei ki válóan a lkalmasak a s orok e gyedi azonosítására, így gyakran használjuk őket elsődleges kulcsként. Azonosító oszlopot az

IDENTITY kulcsszóval hozhatunk létre. Táblák létrehozása Ahogy az a datbázisoknál m ár m egszoktuk, t áblát ké tféleképpen hoz hatunk l étre, E nterprise Managerrel és a C REATE TABLE utasítással. A táblák az adatfájlban tárolódnak, amíg ki nem töröljük őket, és a megfelelő jogosultsággal rendelkező felhasználók ho zzáférnek. A z á llandó táblák mellett lehetőségünk van ideiglenes táblák létrehozására is. Ezek teljesen megegyeznek az állandó t áblákkal, de a t empdb r endszer a datbázisban j önnek l étre, é s a utomatikusan megszűnnek, ha már nincs rájuk szükség. Kétféle átmeneti tábla van Egy lokális táblát hozunk létre, ha a tábla nevének első karaktereként #-ot a dunk m eg. E zt c sak a t áblát l étrehozó felhasználó látja, és a kapcsolat megszűntével a tábla is megszűnik. Globális táblát a nevében két #-tel hoz hatunk l étre. Ezt minden felhasználó látja, és akkor szűnik meg, ha már

senki nem hivatkozik rá többet. Egy t áblában m aximum 1024 os zlopot hoz hatunk l étre. A hogy korábban e mlítettük, a t ábla nevének egyedinek kell lennie, és az oszlopok neveinek is a táblán belül. Hozzunk létre egy táblát az Enterprise Managerrel: 1. Nyissuk ki azt az adatbázist, amelyikben a táblát létre akarjuk hozni 2. A T ables f eliraton ka ttintsunk j obb e gérgombbal, m ajd vá lasszuk a N ew T able parancsot. 3. Írjuk be a tábla nevét a dialógus ablakba 4. Az oszlopokat értelemszerűen egy táblázat kitöltésével határozzuk meg 5. Mentsük el a táblát és zárjuk be az ablakot Ugyanez a CREATE TABLE utasítással: CREATE TABLE táblanév ( { oszlopnév adattípus [ [ DEFAULT konstans kifejezés ] | [ IDENTITY [(kiindulási érték, növekmény )] ] } [,.n] ) Nézzünk egy-két példát 1. A publ ishers t áblában öt os zlopot s zeretnénk t árolni. E gy azonosítót, a ki adó ne vét, vá rosát, államát é s or szágát. H a az

or szágot ne m t öltjük ki a datbevitelkor, az a utomatikusan a z USA értéket veszi fel. CREATE TABLE publishers ( pub id char(4) NOT pub name varchar(40) city varchar(20) state char(2) country varchar(30) ) NULL, NULL, NULL, NULL, NULL DEFAULT(USA) 2. Legyen a z előző példában a pub id mező az elsődleges kulcs, és ne engedjünk meg bármilyen azonosítót, ha nem c sak 99-cel kezdődő négyjegyű számokat, vagy ezeken kívül még kettőt, az 1389-et és az 1622-t. CREATE TABLE publishers ( pub id char(4) NOT NULL CONSTRAINT UPKCL pubind PRIMARY KEY CLUSTERED CHECK (pub id IN (1389, 1622) OR pub id LIKE 99[0-9][0-9]), pub name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT(USA) ) Az elsődleges kulcs mezőre mindig képződik egy index. Kétféle index létezik, a fürtözött (Clustered) é s a ne m f ürtözött ( Nonclustered). H a ne m a dunk m eg s emmit, f ürtözött i ndex j ön létre. Az indexekről később

részletesen is lesz szó Az elsődleges kulcsot mindig NOT NULL-ra kell állítani. 3. CREATE TABLE mytable ( low int, high int, myavg AS (low + high)/2 ) 3 oszlopot hozunk létre és a harmadik oszlopban az első kettő átlagát tároljuk. Táblák módosítása és törlése A tábla létrehozása után annak majd minden jellemzőjét megváltoztathatjuk. • Hozzáadhatunk egy új oszlopot, megváltoztathatunk vagy kitörölhetünk egy régit. • Constraint-eket adhatunk a táblához vagy kitörölhetjük őket. (Primary key, Foreign key, Check, Default, Unique) • Azonosító oszlopot hozhatunk létre vagy törölhetünk ki (IDENTITY). • Megváltoztathatjuk a tábla nevét Az Enterprise Manager használatával a változtatások a szokásos könnyedséggel megtehetők. Az ennek megfelelő T-SQL utasítás az ALTER TABLE. ALTER TABLE táblanév { [ALTER COLUMN oszlopnév { új adattípus [ NULL | NOT NULL ] | {ADD | DROP} } ] | ADD { [ oszlop definíció ] }[,.n] |

[WITH CHECK | WITH NOCHECK] } 1. Létrehozunk egy táblát egyetlen oszloppal. Majd módosítjuk, és hozzáadunk még egy oszlopot, ahol engedélyezzük Null érték beírását is. CREATE TABLE doc exa ( column a INT) GO ALTER TABLE doc exa ADD column b VARCHAR(20) NULL GO 2. Létrehozunk egy táblát, majd kitöröljük belőle az egyik oszlopot. CREATE TABLE doc exb ( column a INT, column b VARCHAR(20) NULL) GO ALTER TABLE doc exb DROP COLUMN column b GO 3. Létrehozunk egy táblát, majd kibővítjük egy oszloppal, de ehhez az oszlophoz már egy constraint is tartozik. CREATE TABLE doc exc ( column a INT) GO ALTER TABLE doc exc ADD column b VARCHAR(20) NULL CONSTRAINT exb unique UNIQUE GO 4. Ebben a z ös szetett pé ldában l étrehozunk e gy t áblát, m ajd e gyszerre t öbb m ódosítást i s végrehajtunk rajta. Hozzáadunk egy azonosító oszlopot (IDENTITY), és ezt a mezőt tesszük meg elsődleges kulcsnak a Primary key constraint segítségével. Hozzáadunk

még egy oszlopot, amiben telefonszámokat szeretnénk tárolni. Check constraint segítségével előírjuk, hogy ha valaki ad atbevitelkor kitölti ezt a mezőt, az a megfelelő telefonszám formátumú legyen. Végül egy harmadik új oszlopot is adunk a táblához, aminek a Default constraint ad kezdeti értéket. CREATE TABLE doc exd ( column a INT CONSTRAINT column a un UNIQUE) GO ALTER TABLE doc exd ADD column b INT IDENTITY CONSTRAINT column b pk PRIMARY KEY, column d VARCHAR(16) NULL CONSTRAINT column d chk CHECK (column d IS NULL OR column d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR column d LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"), column e DECIMAL(3,3) CONSTRAINT column e default DEFAULT .081 GO Tábla törlése az adatbázis törléséhez hasonlóan nagyon egyszerű: DROP TABLE table name 6. A Transact SQL nyelv, SELECT A T-SQL elemei A Transact SQL vagy röviden T-SQL az SQL Server programozási nyelve, az SQL 92

szabvány entry l evel aj ánlásának f elel m eg. E z a fejezet a T -SQL S ELECT utasításáról, v agyis a lekérdezésről szól A T-SQL ut asítások funkciójuk s zerint há rom c soportba os zthatók. A z adatvezérlési ut asítások (Data C ontrol Language, D CL) a z adatbázis, t ábla, né zet obj ektumok jogosultsági t ulajdonságainak be állítására s zolgálnak. Ide t artozik a G RANT, a R EVOKE v agy DENY utasítás. Ha például Mary és John nevű felhasználóinknak adatbázis és tábla létrehozására akarunk jogot adni, a következőket kell írnunk: GRANT CREATE DATABASE, CREATE TABLE TO Mary, John Az a datdefiniálási ut asítások ( Data D efinition Language, D DL) a z a datbázis obj ektumok létrehozását végzik. Ilyen volt a CREATE TABLE vagy CREATE DATABASE is Végül az ad atkezelési u tasításokkal ( Data M anipulation Language, D ML) l ehet az ad atbázisok adatait m anipulálni. Ilyen az INSERT, az U PDATE és a S ELECT E zekkel az u

tasításokkal foglalkozik ez a fejezet, különös tekintettel a SELECT utasításra. Adatok lekérdezése táblából (SELECT) Lekérdezéseket az SQL S erver Q uery A nalyzerrel hozhatunk létre, amit akár a Start menüből, akár az Enterprise Managerből is indíthatunk. A lekérdezéseket az SQL Server végrehajtás előtt mindig optimalizálja, hogy aztán a lehető leghatékonyabban hajtsa végre. Az optimalizálótól tanácsot is kérhetünk, hogy hogyan helyezzük el hatékonyan az indexeinket. A S ELECT pa ranccsal gyakorlatilag s orokat é s os zlopokat vá logatunk ös sze e gy va gy t öbb különböző táblából, amit aztán kedvünk szerint rendezünk. Az utasítás így néz ki: SELECT select list [INTO új táblanév] FROM tábla lista [WHERE feltétel] [GROUP BY group by list] [HAVING feltétel] [ORDER BY order list [ASC | DESC] ] Röviden áttekintve az egyes sorokat (záradék): • select list: Az eredménylistában szereplő oszlopok neveit tartalmazza:

• INTO új táblanév: Az eredménylistát elmenthetjük egy új táblába. • FROM t ábla l ista: Azokat a t áblákat t artalmazza, am ikre t ámaszkodva az eredményhalmazt összeállítjuk. Ezek lehetnek táblák vagy VIEW-k, de lehetőségünk van arra is, hogy OLE DB-n keresztül egy külső táblára csatlakozzunk, és azt a SELECT-ben felhasználjuk. • WHERE feltétel: Ezzel a sorral szűrjük a táblák összekapcsolásával létrejött descartes-i szorzatot. A z e redményhalmazban c sak a zok a s orok l esznek be nne, a mik a W HERE kulcsszó utáni feltételnek eleget tesznek. • GROUP B Y gr oup by list: A z e redményhalmazt ke dvünk s zerint c soportosíthatjuk. A csoportokat itt kell megadnunk. • HAVING feltétel: Ezzel a kulcsszóval egy második szűrést adhatunk meg, ami a WHERE és a G ROUP BY k ulcsszavak ér vényesítése u tán h ajtódik v égre. Leginkább a GROUP BY kulcsszóval együtt fordul elő. • ORDER BY order list: A

végeredményt tetszés szerint sorrendezhetjük. Ha e gy tábla m inden os zlopát s zeretnénk l átni a l ekérdezésben, a S ELECT ut án í rjunk * -ot. A következő példában az SQL Server példa adatbázisából, a Northwind adatbázisból leválogatjuk a Shippers tábla összes rekordját, és a CompanyName mező szerint rendezzük. USE Northwind GO SELECT * FROM Shippers ORDER BY CompanyName GO Ha a SELECT után beírjuk az oszlopok neveit, azok a beírás sorrendjében fognak megjelenni az eredménylistában. A SELECT után konstansokat is felhasználhatunk a megfelelő listakép kialakításának érdekében, akár külön oszlopként is. A következő példában az alkalmazottak teljes nevét listázzuk ki, amit egy egyszerű sztring konkatenációval ké pzünk a ve zetéknév é s keresztnév mezőkből. Az eredménylistát név szerint sorbarendezzük úgy, hogy az ABC-ben előrébb állók lesznek a lista elején. Erre utal az ASC (ASCENDING) kulcsszó a sorrend

megadásánál. Az eredmény táblájában az oszlop neve EmployeeName lesz (AS kulcsszó) USE Northwind GO SELECT LastName + , + FirstName AS EmployeeName FROM Employees ORDER BY LastName, FirstName ASC Ahogy konstansokat, úgy matematikai kifejezéseket is használhatunk. A következő példában a termék táblából az 58-as azonosítóval rendelkező termékre kalkulálunk egy engedményes árat: SELECT ROUND( (UnitPrice * .9), 2) AS DiscountPrice FROM Products WHERE ProductID = 58 A DISTINCT kulcsszóval kihagyhatjuk az azonos sorokat az eredményhalmazból. A következő példában az SQL Server pubs nevű példaadatbázisának titleauthor nevű táblájából kilistázzuk a szerzők kódjait. USE pubs SELECT au id FROM titleauthor A táblában a művek szerzői vannak felsorolva. Mivel egy szerző több művet is írhat, így a táblában t öbbször is szerepelhet. A fenti lekérdezésben ezért ismétlődések lehetnek Hogy ezt elkerüljük, a DISTINCT kulcsszót kell

használnunk: USE pubs SELECT DISTINCT au id FROM titleauthor A FROM kulcsszó A F ROM kul csszó ut án ke ll f elsorolnunk, m ely táblákból é s nézetekből (View) áll össze a lekérdezésünk. A t ábláknak á lneveket i s a dhatunk ( AS) T ovábbá i tt ke ll m egadnunk a t áblák közti kapcsolatok típusát. (illesztés, join) Az illesztések teszik lehetővé, hogy több táblából hozzunk létre egyetlen eredményhalmazt. Az illesztések pontos szintaktikája: FROM first table join type second table [ON (join condition)] A j oin type adja m eg, h ogy m ilyen t ípusú i llesztést a karunk m egvalósítani. A z S QL S erver 3 illesztési típust k ínál: in ner jo in, o uter jo in, c ross jo in. A join condition, v agyis a z ille sztési feltétel határozza meg, hogy mely sorok kerülnek az eredményhalmazba. Lássunk egy példát: SELECT ProductID, Suppliers.SupplierID, CompanyName FROM Suppliers JOIN Products ON (Suppliers.SupplierID = ProductsSupplierID)

WHERE UnitPrice > $10 AND CompanyName LIKE NF% A N orthwind a datbázis S uppliers tá blája ta rtalmazza a c ég b eszállítóit, a P roducts tá bla a termékeket. A lekérdezés párosítja a termékeket a megfelelő beszállító cégekkel és azokat válogatja ki közülük, ahol a cégek neve F betűvel kezdődik, és a termék ára 10$-nál nagyobb. Ha több táblából válogatunk le, a SELECT utasításban használt oszlopneveknek egyértelműeknek kell lenniük. Mivel a Suppliers és a Products táblának is van SupplierID mezeje, a tábla nevének eléírásával jelezhetjük egyértelműen, hogy melyikre is gondolunk. Ha egy mezőnév egyébként is egyedi az ad ott l ekérdezésben, n em s zükséges eléírni a t ábla n evét. A zonban n agymértékben egyszerűsítheti a lekérdezés értelmezését, ha ezt mégis megtesszük. Hogy ne kelljen a tábla nevét mindig kiírni, használhatunk álneveket (alias), ahogy azt a következő példa mutatja: SELECT

P.ProductID, S.SupplierID, S.CompanyName FROM Suppliers AS S JOIN Products AS P ON (S.SupplierID = PSupplierID) WHERE P.UnitPrice > $10 AND S.CompanyName LIKE NF% A táblák illesztését nem csak a FROM kulcsszó után specifikálhatjuk, hanem a W HERE után is, egyszerűen az egyenlőségjel használatával: SELECT P.ProductID, S.SupplierID, S.CompanyName FROM Suppliers AS S, Products AS P WHERE S.SupplierID = PSupplierID AND P.UnitPrice > $10 AND S.CompanyName LIKE NF% A l ekérdezés o lvashatóságát j avítja, h a az i llesztési f eltételek a FROM záradékban, az e gyéb szűrőfeltételek a WHERE záradékban vannak. Inner Join, belső illesztés Belső illesztés esetén az illesztési feltételnek megfelelő sorok kerülnek az eredményhalmazba. Ez a leggyakrabban használt illesztési típus, és ez az egyetlen, amit a WHERE záradékba is írhatunk. Mivel ez az al apértelmezett i llesztés, INNER J OIN h elyett el ég, h a J OIN-t í runk. Nézzünk egy

példát: USE pubs SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = pcity ORDER BY a.au lname DESC Ismét a pubs példaadatbázist használjuk, az authors (szerzők) és a publishers (kiadók) t áblákat. Az eredmény a két tábla minden oszlopát tartalmazni fogja (*), és csak azokat a sorokat, ahol az illesztési oszlop értékek, vagyis a city oszlopok értékei megegyeznek. authors: au id 238-95-7766 267-41-2394 274-80-9391 409-56-7008 publishers: au lname Carson OLeary Straight Bennet au fname Cheryl Michael Dean Abraham pub id 0877 1389 9952 Eredményhalmaz pub name Binnet & Hardley Algodata Infosystems Scootney Books city Berkeley Cleveland Oakland Berkeley city Washington Berkeley New York au id au lname au fname city pub id pub name city 238-95-7766 Carson Cheryl Berkeley 1389 Algodata Infosystems Berkeley 409-56-7008 Bennet Abraham Berkeley 1389 Algodata Infosystems Berkeley Az er edményhalmaz k étszer t artalmazza a ci ty mezőt, és ez

természetes is, hiszen mindkét táblában be nne va n, é s minden os zlop f elsorolását ké rtük a l ekérdezésben. H a c sak e gyszer szeretnénk látni, a SELECT után meg kell adnunk az oszloplistát: SELECT p.pub id, ppub name, a* Outer Join Amíg a z in ner j oin c sak a kkor a d vi ssza e redményt, ha m indkét t áblában l egalább egy s or megfelel az illesztési feltételnek, az outer join le galább az e gyik tábla mi nden sorát visszaadja. (Természetesen a WHERE és HAVING kulcsszavakkal ezt lehet szűrni.) Minden sort visszakapunk a ba l ol dali t áblából L EFT O UTER JOIN e setén, m indent a j obb o ldali t áblából RIGHT O UTER J OIN esetén és m indent m indkét t áblából F ULL OUTER J OIN es etén. (Az OUTER kul csszó e lhagyható). A ko rábbi p éldához ha sonlóan ha sználjuk i tt i s a z a uthors és a publishers táblát. Az előző lekérdezésben, ahol inner joint használtunk, azokat a szerzőket válogattuk le, akik abban a városban

laktak, ahol a kiadójuk is működött. Ha szeretnénk látni az összes szerzőt, left outer joint kell használnunk: USE pubs SELECT a.au fname, aau lname, ppub name FROM authors a LEFT OUTER JOIN publishers p ON a.city = pcity ORDER BY p.pub name ASC, aau lname ASC, aau fname ASC A két tábla között itt is city mező egyezése az illesztési feltétel, de itt azok a szerzők is listázódnak, a kikhez ne m s ikerült ki adót i lleszteni: Ilyen esetben a m ásodik t ábla os zlopainak helyére NULL érték kerül. au fname Cheryl Michael au lname Carson OLeary pub name Algodata Infosystems NULL Dean Straight NULL Abraham Bennet Algodata Infosystems Hasonló a rigth outer join működése is. Az előbb a két táblát a város oszlop alapján kapcsoltuk össze és az összes szerzőt szerettük volna látni, most viszont az összes kiadót listázzuk, vagyis a második tábla minden elemét szerepeltetjük az eredményhalmazban: USE pubs SELECT a.au fname, aau lname, ppub

name FROM authors a RIGHT OUTER JOIN publishers p ON a.city = pcity ORDER BY p.pub name ASC, aau lname ASC, aau fname ASC Azok a kiadók, akikhez nem sikerült kiadót illeszteni (a city mező alapján), N ULL értékekkel párosulnak. au fname au lname pub name NULL NULL Binnet & Hardley Cheryl Carson Algodata Infosystems Abraham Bennet Algodata Infosystems NULL NULL Scootney Books Ha olyan listát szeretnénk, ahol az összes nem illeszkedő elem szerepel, akkor a full outer joint használjuk. Az eredményhalmazba mindkét tábla minden sora bekerül A nem illeszkedő sorokba a másik tábla mezőinek helyen NULL értékek fognak szerepelni. USE pubs SELECT a.au fname, aau lname, ppub name FROM authors a FULL OUTER JOIN publishers p ON a.city = pcity ORDER BY p.pub name ASC, aau lname ASC, aau fname ASC Egy t áblát s aját m agához i s i lleszthetem. Például k eressük az ö sszes O akland-ben lakó szerzőt, akik ugyanabban az irányítószám-körzetben laknak. A

megoldás: az authors táblát saját magához illesztem, az illeszkedés feltétele az azonos zip mező lesz. (Mivel it az authors tábla két szerepet is j átszik, kül ön á lnévvel ke ll hi vatkoznunk r á.) E zzel e gy ol yan t áblát ka pok, amelyben páronként s zerepelni f og az összes azonos irányítószámú szerző. Ezt már csak szűrni kell az Oakland-i szerzők szerint: USE pubs SELECT au1.au fname, au1au lname, au2au fname, au2au lname FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2zip WHERE au1.city = Oakland ORDER BY au1.au fname ASC, au1au lname ASC Adatok szűrése (WHERE és HAVING) A SELECT és a HAVING kulcsszavakkal szűrhetünk, vagyis kiválogathatjuk, hogy mely sorok kerüljenek az eredményhalmazba. A szűrés egy logikai kifejezés megadásával történik, az eredmény s orainak m eg ke ll f elelniük ennek a f eltételnek. Lássunk e gy példát a Northwind adatbázisból. Szeretnénk látni a Washington állambeli cégek azonosítóját

és nevét: USE Northwind SELECT CustomerID, CompanyName FROM Customers WHERE Region = WA A HAV ING kulcsszóval újabb szűrést definiálhatunk. Rendszerint a GROUP BY után használjuk, d e t ermészetesen e gyedül i s k iadható z áradék. P éldákat m ajd a G ROUP B Y tárgyalásánál láthatunk. A szűrőfeltétel lehet valamilyen összehasonlítás, tartomány vizsgálat, tartalmazás v izsgálat, mintaillesztés, NULL érték vizsgálat és egyéb speciális feltételek. Az összehasonlításokhoz a következő operátorokat használhatjuk: =, <, >, >=, <=, <> (nem egyenlő), !> (nem nagyobb, mint), !<(nem kisebb, mint) és != (nem egyenlő). Például az 50$-nál drágább termékek listája: SELECT ProductName FROM Products WHERE UnitPrice > $50.00 Tartományvizsgálatot a B ETWEEN ( vagy N OT B ETWEEN) kul csszóval t ehetünk. A zon termékek listája, amelyekből még 15 és 25 közötti mennyiség van a raktárban: SELECT UnitsInStock,

ProductID, ProductName FROM Products WHERE UnitsInStock BETWEEN 15 AND 25 ORDER BY UnitsInStock A ta rtalmazásvizsgálat kulcsszava a z IN. U tána me gadhatunk egy lis tát, z árójelek k özött, vesszővel elválasztva, ahogy a következő példa is mutatja: SELECT ProductID, ProductName FROM Products WHERE CategoryID IN (1, 4, 5) Az IN u tán á lló lis tát e gy má sik le kérdezés is e redményezheti, e kkor b eágyazott S ELECT-ről beszélünk: USE pubs SELECT au lname, au fname FROM authors WHERE au id IN (SELECT au id FROM titleauthor WHERE royaltyper < 50) Keressük azokat a szerzőket, akik szerzői jogdíja egyik könyvüknél sem érte el az 50 százalékot. Ez az információ a titleauthor tábla royaltyper oszlopában található. Először leválogatjuk a titleauthor táblából az összes megfelelő szerzőazonosítót, majd ezek alapján az author táblából kigyűjtjük a keresett neveket. A mintaillesztés kulcsszava a LIKE, dátum, idő vagy sztring

típusú adatok keresésére használhatjuk. A mintába a következő joker karaktereket tehetjük be: % Bármely (akár nulla hosszú) sztring helyettesítésére. Bármely karakter helyére. [] Bármilyen k arakter a zárójelek k özött me gadott ta rtományon ( például [ a-f]) v agy halmazon (például [abcdef]) belül. [^] Bármely karakter, ami nincs a megadott tartományban. (Például [^a - f]) Ezekkel a joker karakterekkel változatos és bonyolult feltételeket gyárthatunk: • LIKE ‘%en%’ Minden olyan szöveg, amiben előfordul az en. • LIKE ‘M[^c]%’ Minden olyan szó, ami M betűvel kezdődik és a második betűje nem c. Keressük az összes szerzőt, akik telefonszáma nem 415-tel kezdődik: SELECT phone FROM authors WHERE phone NOT LIKE 415% NULL érték keresése rendkívül egyszerű: WHERE oszlopnév IS [NOT] NULL Ügyes l ekérdezéseket al kothatunk az A LL és A NY k ulcsszavak felhasználásával. A következő példában

kilistázzuk azokat a termék és megrendelés azonosítókat az Order Details táblából, ahol a le szállított te rmék me nnyisége n agyobb v olt, min t b ármelyik le szállított egyes kategóriájú termék mennyisége. Ehhez először leválogatjuk a z e gyes k ategóriájú te rmékek k iszállított mennyiségeit. Ez lesz a beágyazott SELECT Majd listázzuk azokat a r ekordokat a megrendelés táblából, ahol a kiszállított termékmennyiség nagyobb volt, mint a belső SELECT-ben ké pzett tábla bármely rekordjában: USE Northwind GO SELECT OrdD1.OrderID, OrdD1ProductID FROM "Order Details" OrdD1 WHERE OrdD1.Quantity > ALL (SELECT OrdD2.Quantity FROM "Order Details" OrdD2 JOIN Products Prd ON OrdD2.ProductID = PrdProductID WHERE Prd.CategoryID = 1) GO Rendezés (ORDER BY) Az e redményhalmazt e gy v agy t öbb os zlopa szerint r endezhetjük a z O RDER B Y z áradék segítségével. A rendezés történhet növekvő (ASC, ascending) vagy

csökkenő (DESC, descending) sorrendben. Ezt nem kötelező megadni, a növekvő rendezés az alapértelmezett Olyan mező szerint is rendezhetünk, ami nem szerepel a SELECT utáni felsorolásban. A következő példában a könyvek címeit tartalmazó táblát rendezzük a pub id mező szerint: USE pubs SELECT pub id, type, title id FROM titles ORDER BY pub id Az eredmény ez lesz: pub id -----0736 0736 0736 0736 0736 0877 0877 0877 type -----------business psychology psychology psychology psychology mod cook mod cook UNDECIDED title id -------BU2075 PS2091 PS2106 PS3333 PS7777 MC2222 MC3021 MC3026 0877 0877 0877 0877 1389 1389 1389 1389 1389 psychology trad cook trad cook trad cook business business business popular comp popular comp PS1372 TC3218 TC4203 TC7777 BU1032 BU1111 BU7832 PC1035 PC8888 Nem csak egy mező szerint rendezhetünk, hanem többet is megadhatunk. A következő utasítás a pub id mező szerint rendez csökkenő sorrendben, azon belül a type, azon

belül a price mezők szerint. USE pubs SELECT pub id, type, title id, price FROM titles ORDER BY pub id DESC, type, price Rendezhetünk számított mezők értéke szerint is: SELECT type, sum (ytd sales) AS sales total FROM titles GROUP BY type ORDER BY sales total Oszlopfüggvények A S ELECT ut áni l istában os zlopfüggvényeket i s ha sználhatunk. E zek ne m a z e gyes e lemekre, hanem a vi zsgált os zlop e gészére von atkoznak. V agyis egy os zlop m inden e lemét f eldolgozva egyetlen értéket ad eredményül. Példaképpen tekintsük a következő utasítást: USE pubs SELECT avg(price * 2) FROM titles Vagyis kíváncsiak vagyunk, hogy mi lenne a könyvek átlagára, ha minden árat megdupláznánk. Az eredmény pedig egyetlen érték: 97446 Az SQL Server 7.0 a következő oszlopfüggvényeket ismeri: SUM() A numerikus elemek összege. AVG() A numerikus elemek átlaga COUNT() Az elemek száma MIN() Az elemek közül a legkisebb MAX() Az elemek közül a

legnagyobb A C OUNT k ivételével a z o szlopfüggvények n em v eszik f igyelembe a NULL ér téket. A S UM, AVG é s C OUNT f üggvényeknél a z árójelen belül m egadhatjuk a z opc ionális D ISTINCT kulcsszót, ha azt akarjuk, hogy a függvény a duplikált elemeket ne vegye figyelembe. Példák: Keressük meg az ABC-ben legelső helyen álló szerzőt az authors táblában: USE pubs SELECT MIN(au lname) FROM authors -------------------------Bennet A könyvek átlagárát szeretnénk tudni: USE pubs SELECT AVG(price) FROM titles WHERE type = business -------------------------13.73 Ha használjuk a DISTINCT kulcsszót, a különböző árú könyvek átlagárát tudhatjuk meg. USE pubs SELECT AVG(DISTINCT price) FROM titles WHERE type = business Here is the result set: -------------------------11.64 Csoportosítás (GROUP BY) Az os zlopfüggvények s egítségével e gy l eválogatott os zlophoz e gyetlen é rtéket r endelhetünk. A GROUP B Y z áradék s egítségével

a er edményhalmaz s orait cs oportosíthatjuk, m ajd az í gy kialakult csoportokra külön-külön végrehajthatjuk az oszlopfüggvényt. A példában lekérdezzük, hogy az egyes termékekből hány egységet (darab, doboz, akármi) adtak el, de csak a 2. termékcsoporton belül: SELECT o.ProductID AS ProdID, SUM(o.Quantity) AS AmountSold FROM [Order Details] AS o JOIN Products as p ON o.ProductID = pProductID AND p.CategoryID = 2 GROUP BY o.ProductID ProdID ----------3 4 5 6 8 15 44 61 63 65 66 77 AmountSold ----------328 453 298 301 372 122 601 603 445 745 239 791 A G ROUP BY kul csszó ut án a c soportosító m ező következik, ami lehet vesszővel elválasztva több mezőnév vagy akár kifejezés is. Az eredménylistában a csoportosító mezőnek megfelelő, különböző értékek jelennek meg minden sorban, mellettük az erre a csoportra kiszámított oszlopfüggvény é rtéke. A pé ldában a Product ID mező szerint csoportosítottunk Az első sor jelentése ezek

szerint: a hármas termékazonosítójú termékekből összesen 328 db-ot adtunk el. Típus és kiadó szerint csoportosítva az egyes könyveket kíváncsiak vagyunk az egyes csoportok átlagárára és az eladott könyvek után befolyt összegre. USE pubs SELECT type, pub id, avg = AVG(price), sum = sum(ytd sales) FROM titles GROUP BY type, pub id type -----------business business mod cook popular comp psychology psychology trad cook pub id -----0736 1389 0877 1389 0736 0877 0877 avg ---------------------11.96 17.31 11.49 21.48 45.93 21.59 15.96 sum ----------18722 12066 24278 12875 9564 375 19566 Ahogy egy SELECT leválogatást a WHERE kulcsszóval szűrhetünk, úgy egy GROUP BY csoportosítást a HAVING kul csszó s egítségével. A WHERE szűrés még a csoportképzés előtt megtörténik, a HAVING csak utána. A HAVING kulcsszót követő feltétel szintaktikája ugyanaz, mint a WHERE utáni feltételé, sőt HAVING után még az oszlopfüggvényeket is

használhatjuk. Kíváncsiak va gyunk p éldául a zokra a ki adókra (pub id), a kik $40000 -nál na gyobb be vétellel büszkélkedhetnek. Először a GROUP BY segítségével csoportosítunk kiadók szerint és a SUM oszlopfüggvénnyel kiszámítjuk a bevételt, majd a csoportosított eredményhalmazt megszűrjük a képződött összeg szerint: USE pubs SELECT pub id, total = SUM(ytd sales) FROM titles GROUP BY pub id HAVING SUM(ytd sales) > 40000 pub id total ------ ----------0877 44219 Azokat a könyvtípusokat szeretnénk látni, amely típusokba legalább két könyv tartozik: USE pubs SELECT type FROM titles GROUP BY type HAVING COUNT(*) > 1 type -----------business mod cook popular comp psychology trad cook Végül né zzünk e gy ol yan pé ldát, ahol ha sználjuk H AVING kul csszót, de n em ha sználunk oszlopfüggvényt. Azokat a típusokat listázzuk, amelyek p betűvel kezdődnek: USE pubs SELECT type FROM titles GROUP BY type HAVING type LIKE p% type

-----------popular comp psychology Adatok felvitele, törlése, módosítása Ha egy táblához új rekordot szeretnénk hozzáfűzni, az INSERT parancsot kell használnunk: INSERT [INTO] táblanév [(oszloplista)] értéklista Az értéklistában szereplő egy vagy több rekord hozzáadódik a táblanév nevű táblához. Hogy melyik érték melyik mezőbe íródjon, azt az oszloplista dönti el. Az értéklistát előállíthatjuk egy SELECT utasítással, de konkrét értékeket is írhatunk a VALUES kulcsszó segítségével. Például egy új szállítmányozó cég adatait adjuk hozzá a Shippers táblához. INSERT INTO Northwind.dboShippers (CompanyName, Phone) VALUES (Snowflake Shipping, (503)555-7233) A S hippers t áblának u gyan 3 os zlopa va n, d e a S hipperID os zlop IDENTITY t ípusú, va gyis értéket nem rendelhetünk hozzá, azt az SQL Server generálja. Ha egyszerre több rekordot akarunk egy táblához fűzni, a SELECT utasítást kell használnunk. Válogassuk

le a modern főzés (mod cooking) típusú könyveket a MyBooks nevű táblába: USE pubs INSERT INTO MyBooks SELECT title id, title, type FROM titles WHERE type = mod cook A S ELECT után specifikált oszloplista meg kell egyezzen az INSERT utasítás oszloplistájával. Ha ezt nem adjuk meg, mert nem kötelező, akkor a SELECT utáni oszloplista a céltábla oszlopaival kell megegyezzen. Létező adatokat az UPDATE utasítással módosíthatunk. Egyszerre csak egy táblára alkalmazhatjuk, d e an nak a kár e gy, akár t öbb, a kár m inden s orát m ódosíthatjuk. A z U PDATE utasításnak 3 fontos cikkelye van: SET: egy vesszővel elválasztott lista követi, amely tartalmazza a módosítandó mező nevét és az új értéket a következő formában: mezőnév = kifejezés A k ifejezésben szereplő értékek lehetnek állandók, egy tábla vagy nézet (view) bármely eleme, vagy akár egy összetett kifejezés értéke is. FROM: azokat a táblákat azonosítja, amik a SET

utáni kifejezésekben szerepelnek. WHERE: a megadott feltétel a módosítandó sorokat választja ki. Példaképpen emeljük meg a m ásodik k ategóriás t ermékek árát 1 0 százalékkal a t ermékek táblájában (products): UPDATE Northwind.dboProducts SET UnitPrice = UnitPrice * 1.1 WHERE CategoryID = 2 Néhány további példa: 1. A ki adók ( publishers) t áblában az ö sszes k iadó s zékhelyét a G eorgia ál lambeli A tlanta-ra javítjuk. (Ha például mindenki odaköltözik) UPDATE publishers SET city = Atlanta, state = Georgia 2. A United Shippers nevű szállítmányozó cég nevét átírjuk United Packages-re UPDATE Shippers SET CompanyName = United Shippers WHERE CompanyName = United Packages Adatok törlése a DELETE utasítással történik. DELETE táblanév FROM táblanév WHERE feltétel FROM: az okat a t áblákat az onosítja, am elyeket a W HERE u táni f eltétel h asznál. A W HERE utáni feltétel választja ki, hogy a táblából mely rekordok

törlődnek ki. Töröljük ki a McBadden nevű szerzőt a szerzők (authors) táblából. DELETE FROM authors WHERE au lname = McBadden Ha nem adunk meg WHERE feltételt, az összes rekord törlődik a táblából. USE pubs DELETE authors 7. Tranzakciók Tranzakciók vezérlése A tranzakció egymás utáni műveletek olyan logikai egysége, amelyben az egyes objektumokon végzett változtatásokat egyszerre érvényesítjük. Ez azt jelenti, hogy az egy tranzakcióba tartozó műveletek vagy egyszerre jutnak érvényre, v agy b ármilyen h iba es etén eg yszerre k erülnek visszautasításra. Klasszikus példa egy pénzügyi tranzakció programozása Tegyük fel, hogy egy ügyfél valamekkora összegű pénzt kíván átutalni az egyik számláról (A) a másikra (B). Ekkor a programnak az összeget le kell vonnia az A számláról, hozzá kell adnia a B számlához, továbbá a pénzügyi tranzakciót fel kell tüntetni a forgalmi naplóban és még ki tudja, hány másik

táblában. A program mondjuk először leveszi az összeget az A számláról, majd mielőtt hozzáírná a B számlához, meghibásodik. Ha a műveletek nem lennének egy tranzakcióban, az ügyfél számára elveszne a pénze. Így azonban a tranzakcióban szereplő minden művelet érvénytelen, az adatbázis eredeti állapotába áll vissza, vagyis az összeg az ügyfél A számláján lesz. A tranzakciót négy jellemző tulajdonsága minősíti. (ACID: Atomicity, Consistency, Isolation, Durability) A tr anzakciónak a mu nka e gy elemi ( atomic) s zeletét k ell d efiniálnia. A mikor e gy tranzakció l ezajlik, az ad atbázisnak k onzisztens ál lapotba ke ll ke rülnie. A konkur ens tranzakcióknak izoláltaknak kell lenniük, vagyis, ha egy A tranzakció módosít egy adatot, akkor egy másik, vele egyidejűleg futó B tranzakció ugyanannak az adatnak a módosítás előtti értékét látja egészen addig, amíg az A tranzakció be nem fejeződik. Az módosított értékek

csak ez után láthatók. E gy t ranzakció l ezajlása ut án a v égrehajtott m ódosítások á llandósulnak, m ég egy esetleges rendszerhiba után is megmaradnak. Az ad atbázis f izikai i ntegritásáért az S QL S erver f elel. A programozó feladata, ho gy az adatok logikai in tegritását b iztosítsa. E hhez me g k ell h atároznia, h ogy hol k ell tr anzakciókat alkalmaznia, definiálnia kell a tranzakciók kezdetét és végét, illetve azokat a műveleteket, amelyek e gy t ranzakcióba ke rülnek. M inden t ranzakció vé gén a z a datbázisnak konz isztens állapotban kell lennie! Az adatbázis fizikai integritásának megőrzéséhez az SQL Serverben a következő automatikus mechanizmusok szolgálnak: • Zárolási képesség a tranzakciók izolálásához. A zárolásról részletesebben lesz szó később • Naplózó k épesség a ma radandósági ( durability) k ritérium k ielégítésére. E gy e setleges rendszerleállás u tán az S QL s zerver ú

jraindulásakor a t ranzakciós n apló segítségével az összes nem befejezett tranzakciót visszagörgeti. • Tranzakció kezelő képesség. Egy tranzakció elindítása után annak sikeresen be kell fejeződnie. Ellenkező esetben az SQL Server visszaállítja a tranzakció előtti állapotot Tranzakció i ndítására a B EGIN T RANSACTION u tasítás s zolgál. Lezárása k étféleképpen történhet. A sikeres tranzakciót COMMIT utasítás zárja le A COMMIT végrehajtása garantálja, hogy a tranzakció során végrehajtott módosítások ezután az adatbázis állandó részét képezik. A tranzakció s orán ke letkezett zárolások i s e kkor s zabadulnak f el. H a hi ba m erül fel a t ranzakció során, akkor annak a ROLLBACK utasítás vet véget. Ekkor minden módosítás érvényét veszti, az adatbázis a tranzakció előtti állapotba kerül. ROLLBACK esetén a használt zárolások szintén felszabadulnak. Nézzünk egy példát. A pubs adatbázisban a

szerzői jogdíjakat a roysched nevű tábla tartalmazza Szeretnénk megemelni minden számítástechnikai könyv szerzői jogdíját 10 százalékkal. Az ilyen könyvek azonosítója (title id mező) Pc-vel kezdődik BEGIN TRANSACTION MyTransaction USE pubs UPDATE roysched SET royalty = royalty * 1.10 WHERE title id LIKE Pc% COMMIT TRANSACTION MyTransaction A t ranzakciónak lehet neve, de ez nem kötelező. Beágyazott tranzakciók esetében lehet rá szükség, ho gy a B EGIN C OMMIT p árokat meg l ehessen kül önböztetni e gymástól. E z a megkülönböztetés cs ak a p rogramozónak s zól, az S QL S erver n em v eszi f igyelembe ez eket a neveket. A B EGIN TRANSACTION kiadása után azok az erőforrások, amik részt vesznek az adatbázis-műveletekben, zárolás alá kerülhetnek. Zárolt állapotuk mindaddig megmarad, amíg a tranzakciót e gy C OMMIT, R OLLBACK, v agy egy hi ba l e ne m z árja. Sokáig n yitva ha gyott tranzakciók t ehát m egakadályozhatják, ho gy m

ás f elhasználók i s hoz záférjenek az é ppen z árolt adatokhoz. B ár a B EGIN T RANSACTION v égrehajtásával a t ranzakció k ezdetét v eszi, a tranzakciós naplóba ekkor még nem kerül bejegyzés. Erre csak akkor kerül sor, ha a tranzakció során végrehajtjuk az INSERT, UPDATE vagy DELETE utasítások valamelyikét. A @@TRANCOUNT nevű globális változó tárolja a rendszer aktív tranzakcióinak számát. Ezt a BEGIN T RANSACTION e ggyel növe li, a C OMMIT T RANSACTION e ggyel c sökkenti. H a a COMMIT után a változó értéke nem nulla, például egy beágyazott tranzakció esetében, akkor a változtatások még nem kerülnek véglegesítésre, nem történik meg az erőforrások felszabadítása a zárolás alól. A R OLLBACK u tasítás v isszagörgeti a k iadott u tasításokat eg észen a t ranzakció el ejéig. Beágyazott tranzakció esetében nem veszi figyelembe a belső tranzakciókat, az ér vénytelenítés egészen a külső tranzakció elejéig

történik. A @@TRANCOUNT változó értéke ilyenkor 0 lesz Egy tranzakció sohasem görgethető már vissza COMMIT után! Előfordulhat, hogy egy tranzakció során ROLLBACK utasítást kell alkalmaznunk, de nem akarjuk az összes műveletet érvényteleníteni. Ilyenkor a SAVE TRANSACTION utasítással jelölhetjük k i a v isszagörgetés h atárát. A z u tasításban a h elyet e gy n évvel j elöljük m eg, és a COMMIT kiadásakor erre a névre kell hivatkoznunk. A következő példában ismét a pubs adatbázist használjuk. A The Gourmet Microwave című könyv két szerzője 75%-25% arányban részesülnek a szerzői jogdíjból. Ezt az arányt szeretnénk 65%-35%-ra m ódosítani T ovábbá kíváncsiak vagyunk arra is, hogy ha 10%-kal emelnénk a k önyv ár át, az hogyan befolyásolná a szerzők bevételét. Először tehát a két szerző rekordját kell módosítani. Mivel a két UPDATE között az adatbázis inkonzisztens állapotban van, a műveleteket

tranzakcióban fogjuk össze: BEGIN TRANSACTION royaltychange UPDATE titleauthor SET royaltyper = 65 FROM titleauthor, titles WHERE royaltyper = 75 AND titleauthor.title id = titlestitle id AND title = The Gourmet Microwave UPDATE titleauthor SET royaltyper = 35 FROM titleauthor, titles WHERE royaltyper = 25 AND titleauthor.title id = titlestitle id AND title = The Gourmet Microwave SAVE TRANSACTION percentchanged Itt m egjelöljük a t ranzakciót, é s m ost m egvizsgáljuk, hog y m i l enne, ha a kön yv á rát megemelnénk. UPDATE titles SET price = price * 1.1 WHERE title = The Gourmet Microwave SELECT (price * royalty ytd sales) royaltyper FROM titles, titleauthor WHERE title = The Gourmet Microwave AND titles.title id = titleauthortitle id Miután a SELECT megmutatta a kért adatokat, a változásokat visszagörgetjük a megjelölt pontig. ROLLBACK TRANSACTION percentchanged COMMIT TRANSACTION Ha a RO LLBACK-kel cs ak s avepoint-ig g örgetünk vi ssza, a @ @TRANCOUNT

é rtéke ne m változik. Úgyszintén a zárolt erőforrások sem szabadulnak fel a tranzakció végéig Autocommit tranzakciók Minden TSQL utasítás önmagában tranzakcionálisan hajtódik végre, vagyis commit-álódik vagy érvényét v eszti b efejeztekor. H a s ikeresen v égrehajtódott, co mmit-álódik, ha hi ba m erül f el, automatikusan R OLLBACK h ajtódik v égre. A z S QL S ervernek ez az al apértelmezett üzemmódja, és egészen egy BEGIN TRAN utasításig így működik. (A T RANSACTION-t TRAN-nak l ehet rövidíteni.) Amikor a m egkezdett t ranzakciónak vé ge v an, akár C OMMIT-tal, akár ROLLBACK-kel, ismét autocommit üzemmódba kerül. Implicit tranzakciók Ezt az üzemmódot ki és be lehet kapcsolni a SET IMPLICIT TRANSACTIONS OFF és a SET IMPLICIT TRANSACTIONS O N pa rancsokkal. E bben a z ü zemmódban a z S QL S erver bizonyos utasítások után automatikusan tranzakciót indít, nem kell tehát külön beírnunk a BEGIN TRAN ut asítást, de a t

ranzakció l ezárásáról n ekünk ke ll gondoskodnunk a R OLLBACK vagy COMMIT utasításokkal. Ilyen tranzakciót generáló utasítások a következők: ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN REVOKE SELECT TRUNCATE TABLE UPDATE Példa: USE pubs CREATE TABLE ImplicitTran ( Cola int PRIMARY KEY, Colb char(3) NOT NULL) SET IMPLICIT TRANSACTIONS ON Az első tranzakció automatikusan indul a következő INSERT utasítás hatására, amit majd a COMMIT zár. INSERT INTO ImplicitTran VALUES (1, aaa) INSERT INTO ImplicitTran VALUES (2, bbb) COMMIT TRANSACTION A második implicit tranzakciót a SELECT indítja. SELECT COUNT(*) FROM ImplicitTran INSERT INTO ImplicitTran VALUES (3, ccc) SELECT * FROM ImplicitTran COMMIT TRANSACTION SET IMPLICIT TRANSACTIONS OFF Elosztott tranzakciók Ha e gy t ranzakció e gyszerre t öbb a datbázist i s érint, m elyek különböző erőforrás-szervereken vannak, a kkor elosztott tranzakcióról be szélünk. E zeket a t

ranzakciókat a z S QL S erverben a Microsoft D istributed T ransaction C oordinator ( MS D TC) m enedzseli. Ha a t ranzakció c sak egyetlen szerveren fut, akkor is érinthet egyszerre két adatbázist. Ez is elosztott tranzakció, de az SQL Server ezt saját maga vezényli le. Az elosztott tranzakciók érvényesítése két fázisban történik. Az első fázisban a tranzakcióban érintett a datbázisok kül ön-külön é rvényesítik a vá ltoztatásokat. Ha ez m indenkinél s ikeresen lezajlott, csak akkor válik az egész tranzakció is érvényessé. Ez az ellenőrzés képezi a második fázist. Ha az első fázis során az egyik adatbázis nem tud érvényesíteni, minden adatbázisban érvényteleníteni kell a változtatásokat. Ez a metódus a kétfázisú commit, vagy angolul two-phase commit, 2pc. A két fázis neve: prepare phase és commit phase. Az első fázisban tehát a tranzakció kezelő megkapja a commit kérelmet, és ekkor egy prepare parancsot küld az

összes erőforrás szervernek, akik érintettek ebben a tranzakcióban. Az egyes erőforrás szerverek ekkor mindent megtesznek, hogy a tranzakciót lezárhassák, a vonatkozó naplóbejegyzéseket lemezre mentik, majd jelentik a tranzakció-kezelőnek, hogy ezt a fázist sikeresen végrehajtották vagy sem. Ha a tranzakció kezelő mindenkitől sikeres visszajelzést kapott, mindenkinek kiküldi a commit parancsot. Minden erőforrás szerver véglegesíti a változtatásokat, és erről is jelentést küld a tranzakció-kezelőnek. Ha mindenki sikert jelent, a tranzakció-kezelő csak ekkor küld üzenetet annak az alkalmazásnak, amelyik meghívta ezt a tranzakciót. Ha bármelyik erőforrás szerver az első menet végén sikertelenségről üzen, a tranzakció-kezelő mindenkinek kiküldi a rollback utasítást és erről értesíti az alkalmazást. Az e losztott t ranzakciókkal ka pcsolatban a pr ogramozónak ne m s ok t ennivalója a kad, a z S QL Server automatikusan elrendez

m indent. A s zokásos m ódon elindítjuk a t ranzakciót, m eghívjuk azokat az utasításokat, amiket szeretnénk és a szokásos módon, COMMIT-tal vagy ROLLBACKkel zárjuk a tranzakciót. Az SQL Server ekkor végrehajtja a műveleteket, majd az MS DTC segítségével elrendezi a COMMIT-ot vagy éppen a ROLLBACK-et. Izolációs szintek A f ejezet e lején e mlítettük ( ACID), h ogy a z iz oláció f ontos ismérve a t ranzakcióknak. A z izoláció az t j elenti, h ogy egy t ranzakció v égrehajtása s orán a n em ér vényesített ad atokat e gy másik futó tranzakció nem láthat. Ez azt jelenti, hogy a módosított mezőket tartalmazó sorokat az SQL szerver zárolja. Hogy ez az izoláció mennyire szigorú, az állítható A szigorúság mértéke az izolációs szint, vagy isolation level. Az SQL Server 4 különböző szintet valósít meg READ U NCOMMITTED. O lvasás ér vényesítés n élkül E z az l egalacsonyabb s zint, ah ol a tranzakciók cs ak an nyira

vannak egymástól e lválasztva, hog y f izikailag hi bás adatot ne olvassanak. Lehetőség van tehát beleolvasni egy tranzakcióban részt vevő táblába annak érvényesítése előtt. Így előfordulhat, hogy olyan adatot olvasunk, amit már megváltoztattak, vagy kitöröltek. Előnye, hogy rendkívül gyors, ám ugyanannyira veszélyes is Például egy tranzakció megváltoztat egy sort. Eközben egy másik tranzakció elolvassa a megváltoztatott sort Ha most az első tranzakció ROLLBACK-kel vi sszagörgeti a vá ltoztatásokat, a m ásodik tranzakció o lyan adatok birtokában van, amely logikailag sohasem létezett. (dirty read) READ C OMMITTED. Más t ranzakciók csak már ér vényesített v áltoztatásokat l áthatnak A változtatás a latt á lló s orokhoz c sak a s aját t ranzakció f érhet hoz zá, de más s orokba bele l ehet módosítani a tranzakció lezárása előtt. Így az előbb említett diry read probléma már nem áll fent, előfordulhatnak azonban

további érdekes esetek. Ha egy tranzakció egy sort két alkalommal is elolvas, és a két olvasás között azt egy m ásik tranzakció módosítja, a két érték különböző lesz. (nonrepeatable r ead) E gy tranzakció el olvashat eg y s ort, am i n em i s l étezik, h a az t eg y m ásik tranzakció beszúrja (INSERT), mielőtt az első tranzakció véget érne. Ez az úgynevezett fantom probléma (phantom). Ez az izolációs szint az alapértelmezett. REPEATABLE READ. Az érintett sorokra zár kerül a tranzakció végéig, tehát más tranzakciók abba ne m m ódosíthatnak be le, de f antom s orok í gy i s ke letkezhetnek, m ert a t áblába adatot beszúrni továbbra is lehet. Az a fajta izoláció a párhuzamos végrehatást már erősen korlátozhatja, ezért csak indokolt esetben használjuk! SERIALIZABLE. Az egész adathalmaz zár alá kerül, amíg a tranzakció tart, abba sem beszúrni, sem módosítani nem lehet. Ez a legerősebb megszorítás, és a leglassabb is

egyben Az izolációs szint b eállítása a S ET T RANSACTION ISOLATION LEVEL p aranccsal t örténik. Egy példa: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM publishers SELECT * FROM authors . COMMIT TRANSACTION Néhány jó tanács A l egfontosabb t anács: l egyenek a t ranzakcióink ol yan r övidek, a milyenek c sak t udnak l enni. Vagyis é rvényesítsük a zokat ol yan ha mar, amilyen ha mar c sak l ehet. Amikor e gy t ranzakció elindul, az adatbázis-kezelő rendszer lefoglal valamennyi erőforrást, hiszen temérdek dolga van: a korábban már említett ACID jellemzőket ki kell elégíteni. Ha adatot módosítunk, a sort zárolnunk ke ll, ho gy a t ranzakció v égéig m ás t ranzakciók ne é rhessék e l. A z i zolációs szinttől függően a SELECT utasítások is maguk után vonhatják sorok zárolását. Minél több felhasználó van a rendszerben, annál inkább fontos a tranzakciókat rövid ideig futtatni, hogy a lehető

legkevesebbet kelljen várakozni a zárolt adatok miatt. Ne programozzunk felhasználói bevitelt tranzakció közben. Ezt tegyük meg mindig a tranzakció előtt. Ha tranzakció közben derül ki, hogy mégis szükség van még adatra a felhasználótól, mindig R OLLBACK-eljünk, rendezzük l e a b evitelt, és i ndítsunk e gy új tranzakciót. Ellenkező esetben előfordulhat, hogy a felhasználó csak sokára válaszol, és közben zárolva tartjuk a táblákat akár órákig. Egy tranzakció során mindig a lehető legkevesebb adattal dolgozzunk. Feladatok 1. A S AVE T RANSACTION-t b emutató r észben s zerepel eg y p élda Írja át a p éldában szereplő UPDATE utasításokat úgy, hogy explicit join legyen bennük. 2. Hányféleképpen érhet véget egy tranzakció? 3. Találjon ki egy szituációt, ahol elosztott tranzakciót kell megvalósítani! 4. Mely izolációs szinteknél fordulhat elő dirty read, nonr epeatable r ead é s pha ntom probléma? 8. Indexelés Az

indexek lehetővé teszik, hogy megtaláljunk egy adatot a táblában anélkül, hogy az egészet elolvasnánk. Indexet ú gy képzünk, ho gy a t áblából ki másoljuk a zokat a kul csokat, a melyek értékeinek ismeretében gyakran akarunk az egyes rekordokhoz hozzáférni, és minden egyes kulcs mellé odaírjuk, hogy az ahhoz tartozó rekord hol található meg a táblában. Készíthetünk indexet egyetlen os zlopra, va gy ké szíthetünk t öbb os zlop kom binációjából ös szetett i ndexet is. A z indexállomány a kulcsokat kiegyensúlyozott B-fában tárolja. A B-fa a kulcs szerint rendezett, így nagyon h atékonyan l ehet be nne ke resni. E gy t áblára t öbb i ndexet i s de finiálhatunk A z S QL Server au tomatikusan el őállít indexet a PRIMARY KEY és UNIQUE oszlopokra. Az indexek használatának t ermészetesen ár a v an. A z i ndex ál lományok h elyet f oglalnak a h áttértáron, az INSERT, UPDATE és DELETE u tasítások v égrehajtásakor a t áblák m

anipulálása m ellett az indexállományokat is fel kell frissíteni, így ezek a műveletek tovább tartanak; mégpedig annál tovább, m inél t öbb i ndexet de finiáltunk a t áblára. E zért a z i ndexek ha sználatakor ü gyesen ke ll egyensúlyozni a lekérdezési idők csökkentése és a karbantartási idők növekedése között. Amikor az S QL S erver végrehajt e gy l ekérdezést, a l ekérdezés o ptimalizáló ( query o ptimizer) megbecsüli a r endelkezésre álló me tódusok k öltségeit, és a le goptimálisabbat v álasztja a lekérdezéshez. Ez lehet a tábla soronkénti végigolvasása (table scan), vagy lehet index használata is, ha va n. H ogy eldönthessük, m ilyen i ndexeket definiáljunk e gy t áblához, t udnunk ke ll, hog y miben is segítenek minket konkrétan az indexek. • Ha ke resünk e gy r ekordot, é s pont osan t udjuk a hoz zá t artozó kul csot. ( exact m atch query) • Ha t öbb r ekordot i s ke resünk, és t udjuk, hog y a hoz

zájuk t artozó kul csok m ilyen tartományba esnek. (range query) • A T1 táblában keressük azokat a rekordokat, amelyek egy join-on keresztül megfelelnek bizonyos rekordoknak a T2 táblában. (index nested loops join) • Az i ndexek s egítik a P RIMARY K EY és a U NIQUE co nstraint-ek ér vényre j utását INSERT v agy U PDATE ut asításnál, hi szen a z e setleges dupl ikációk r ögtön lelepleződnek. • Két, F OREIGN KE Y-jel ö sszekapcsolt t ábla s orainak m egfeleltetése i s s okkal g yorsabb indexek segítségével. Ha e ldöntöttük, hogy ho vá t eszünk i ndexet, m ár c sak l étre ke ll hoz ni a zokat. A z SQL S erver kétféle indexet kínál: fürtözött (clustered) és nem fürtözött (non-clustered) indexeket. Fürtözött indexek A f ürtözött i ndex a t ábla s orait a kul csértékeknek megfelelően fizikailag rendezi. Mivel egy táblának egyszerre csak egyféle fizikai sorrendje lehet, minden táblához csak egy fürtözött index

tartozhat. A fürtözött index a leghatékonyabb módszer, hogy az indexkulcs alapján megtaláljunk egy s ort, va gy egy sortartományt. Sortartomány esetén például elég a tartomány legelső sorát megkeresni, a s zomszédos i ndex é rtékek garantáltan a s zomszédos r ekordokat f ogják ki jelölni. Például ha e gy t áblából m indig ké t dá tum köz ti é rtéket ke ll l eválogatnunk, a kkor ha a dátum mezőre fürtözött indexet definiálunk, igen gyors lekérdezéseink lesznek. Hiszen csak első dátumot kell megkeresni, utána pedig addig olvasni a sorokat sorban, amíg a végső dátumhoz nem é rünk. H a e zen kí vül m ég gyakran k ell d átum s zerint s orba r endezni a z a datokat, s okat spórolhatunk azon, hogy ez a tábla rögtön ebben a sorrendben produkálja a leválogatásokat. Mikor jó tehát a fürtözött index? • Ha az oszlop nem túl sok különböző értéket tartalmaz. Például megyéket, amiből összesen 19 féle van. • Ha

olyan lekérdezéseket írunk, amelyek egy tartományt eredményeznek. (BETWEEN, >, >=, <, <=) • Nagy eredményhalmazok lekérdezésekor. • Ha a l ekérdezésben J OIN v agy G ROUP B Y v an, ál talában az ad ott o szlopnév eg yben FOREIGN KEY is. Ha erre az oszlopra fürtözött indexet definiálunk, megspórolunk egy sorba rendezést az SQL Servernek, mivel az adatok már úgyis sorban vannak. Nem fürtözött indexek A nem fürtözött indexek a sorok logikai sorrendjére utalnak. A lekérdezéseket gyorsíthatjuk fel a segítségükkel. A ne m f ürtözött i ndexek ugy anúgy B -fában va lósulnak meg, m int a f ürtözött indexek, csak ebben az esetben nem rendezzük a fát. A nem fürtözött indexek a kulcsok mellett nem a rekord fizikai helyét tartalmazzák, hanem a fürtözött index megfelelő kulcsát, ezért fontos, hogy a fürtözött index oszlopmérete minél kisebb legyen. Egy nagy oszlopméretű fürtözött index rengeteg helyet fog

elfoglalni azzal, hogy szerepel az összes nem fürtözött indexben is. A nem fürtözött indexeket mindig a fürtözött indexek után célszerű létrehozni, mert a fü rtözött index m egváltoztatja a t ábla s orainak f izikai s orrendjét. E gy f ürtözött i ndex l étrehozása va gy törlése után az SQL Server újragenerálja a az összes nem fürtözött indexet. Mikor jó egy nem fürtözött index? • Ha az oszlop nagy számú különböző értéket t artalmaz. H a cs ak k eveset, akkor f ürtözött indexet ke ll i nkább ha sználnunk, ha c sak 1 -2 különböző értéket, akkor nincs értelme indexet használni. • Ha fürtözött indexet kéne használnunk, de már van egy. • Kis eredményhalmazok lekérdezésekor. • Olyan oszlopokra, amelyek gyakran szerepelnek WHERE utáni feltételben. Index létrehozása Az S QL S erver a utomatikusan e gyedi i ndexeket hoz l étre, hog y ki kényszerítse a P RIMARY KEY és a UNIQUE constraint-ek esetén

előírt egyediségek. Az egyedi index biztosítja, hogy egy indexelt os zlop m inden adata egyedi l egyen, és ne t artalmazzon dupl ikált értéket. Ha m ég nem létezik fürtözött index a táblán vagy nem kérünk kifejezetten nem fürtözött indexet, egy egyedi, fürtözött i ndex j ön l étre ha de finiálunk e gy P RIMARY KEY c onstraint-et. H asonlóan ha csak nem fürtözött indexet kérünk, egy egyedi, nem fürtözött index jön létre ha UNIQUE constraint-et definiálunk. Ha a constraint-ektől függetlenül szeretnénk indexet létrehozni, a CREATE INDEX utasítást kell használnunk. Alapértelmezés szerint nem fürtözött index jön létre A CREATE INDEX utasítás szintaxisa a következő: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index name ON table (column [,.n]) [WITH [IGNORE DUP KEY] [[,] DROP EXISTING] ] A UN IQUE k ulcsszó d efiniálja az eg yediséget, a C LUSTERED a f ürtözött index, a NONCLUSTERED a nem fürtözött index

megfelelője. Az indexet el kell neveznünk (index name). A t able é s a c olumn ha tározzák m eg, ho gy m elyik tábla m elyik os zlopán értelmezzük az indexet. Az IGNORE DUP KEY kulcsszó határozza meg, hogy mi történik akkor, amikor egy már létező kulcsot akarunk beszúrni a táblába, és az adott oszlopra az egyedi jellemző érvényes. Ha az index definíciójában szerepel az IGNORE DUP KEY kulcsszó, az SQL Server figyelmeztetést küld és a b eszúrást n em h ajtja v égre. H a a k ulcsszót n em s zerepeltettük, h iba g enerálódik, és az eg ész tranzakció visszagörög (rollback). Ha m egadjuk a DROP EXISTING kul csszót, a z i ndex létrejöttekor t örli a z ug yanilyen né ven esetleg már létező korábbi indexet. Ha emiatt egy fürtözött indexet kell törölni, a nem fürtözött indexek is újra generálódnak. Ha kí váncsiak va gyunk, hog y az i ndexeink m ennyi h elyet f oglalnak, a z s p spaceused t árolt eljárást kell meghívnunk. USE

pubs EXEC sp spaceused titles Ha fü rtözött indexet hozunk létre, a művelethez körülbelül a tábla méretének 1,2-szerese szükséges ideiglenes tárolás céljából. Ez azért kell, mert a táblázatot előszőr le kell másolni az új sorrendnek megfelelően. A másolás végén az eredeti táblázat törlődik Indexeket ideiglenes táblákra is feltehetünk. A tábla megszűntével az index is automatikusan megszűnik. Ha kí váncsiak va gyunk, hog y egy t áblán m ilyen i ndexek é rtelmezettek, az s p helpindex tárolt eljárást kell használnunk. Az SQL Server az indexeket a sysindexes nevű rendszertáblában tárolja. Példák 1. A s ysindexes t áblában m egnézzük, hog y l étezik-e m ár i ndex a p ubs a datbázis a uthors táblájában az au id mezőre. Ha már létezik, töröljük Ezután létrehozzuk ezt az indexet Az SQL Server a műveletek végrehajtása után e gy DONE IN PROC üz enetben t udatja a z ü gyfél alkalmazással, hogy az adott művelet

hány sort érintett. Ezt egyébként a @@ROWCOUNT golbális változóban tárolja. Ha ez a szám minket nem érdekel, az üzenetküldést letilthatjuk a SET NOCOUNT ON u tasítással, illetve bekapcsolhatjuk a S ET NOCOUNT OFF utasítással. Erre is látunk is itt példát. SET NOCOUNT OFF USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = au id ind) GO DROP INDEX authors.au id ind USE pubs CREATE INDEX au id ind ON authors (au id) GO 2. Újra létrehozzuk az alkalmazottak fizetését tartalmazó táblát, feltöltjük adatokkal, majd egyedi, fürtözött indexet definiálunk rá, és használjuk az IGNORE DUP KEY. SET NOCOUNT ON USE pubs IF EXISTS (SELECT * FROM INFORMATION SCHEMA.TABLES WHERE TABLE NAME = emp pay) DROP TABLE emp pay GO USE pubs IF EXISTS (SELECT name FROM sysindexes WHERE name = employeeID ind) DROP INDEX emp pay.employeeID ind GO USE pubs GO CREATE TABLE emp pay ( employeeID int NOT NULL, base pay money NOT NULL, commission decimal(2, 2) NOT NULL )

INSERT emp pay VALUES (1, 500, .10) INSERT emp pay VALUES (2, 1000, .05) INSERT emp pay VALUES (3, 800, .07) INSERT emp pay VALUES (5, 1500, .03) INSERT emp pay VALUES (9, 750, .06) GO SET NOCOUNT OFF GO CREATE UNIQUE CLUSTERED INDEX employeeID ind ON emp pay(employeeID) WITH IGNORE DUP KEY Index törlése Ha az indexre már nincs többé szükségünk, a DROP INDEX utasítás használatával törölhetjük. DROP INDEX table.index Az utasítás használatára a előző szakaszban láthatunk példát. Fürtözött index törlése időigényes művelet, hiszen az összes nem fürtözött indexet újra létre kell hozni. PRIMARY KE Y és U NIQUE c onstraint-ekkel létrehozott indexek nem törölhetők, ezek megszűntetéséhez magát a constraint-et kell a tábláról levennünk. A tábla minden indexe a tábla megszűntével automatikusan megszűnik. Statisztika Minden index tartalmaz egy eloszlás statisztikát az indexben lévő adatértékekről. Ezeket az információkat az S QL

S erver k öltség alapú le kérdezés o ptimalizálója ( query o ptimizer) használja, h ogy el döntse, m ilyen s tratégiát alkalmazzon az eg yes l ekérdezések v égrehajtásakor. Ha létrehozunk egy indexet, az oszlop értékeiből automatikusan generálódik a statisztika is. A későbbi műveletek a statisztikát nem befolyásolják, ezért előbb vagy utóbb elavulttá válik, és a query opt imizer m ár ne m a l egoptimálisabb dönt éseket f ogja m eghozni. B ár egy bi zonyos mintavételezési algoritmus szerint maga az S QL S erver is frissíti néha a statisztikákat, m ódunk van ezt a programunkból kikényszeríteni az UPDATE STATISTICS utasítással. UPDATE STATISTICS table [ index | (statistics name[,.n]) ] [ WITH [ [FULLSCAN] | SAMPLE number {PERCENT | ROWS}] ] [[,] [ALL | COLUMNS | INDEX] [[,] NORECOMPUTE] ] 1. Az authors tábla minden indexére újrageneráljuk a statisztikákat UPDATE STATISTICS authors 2. Csak egyetlen indexre kérjük a frissítést

UPDATE STATISTICS authors au id ind 3. T öbb t ábla s tatisztikáját ug yanazzal a né vvel e lnevezve e gy s tatisztikacsoportot hoz hatunk létre, amit egyszerre frissíthetünk. Itt az anames nevű csoport statisztikáit akarjuk újraszámolni A WITH FULLSCAN kapcsoló előírja, hogy a statisztika k iszámításához az e gész t áblát v égig kell ol vasni. E nnek hi ányában a z S QL S erver e ldönti, hogy ho gyan m intavételez a t áblából a statisztika be csléséhez. A S AMPLE ka pcsolóval e zt i s pont osan s zabályozni l ehet A NORECOMPUTE kapcsoló azt írja elő, hogy most előálló statisztikát az SQL Server később automatikusan soha ne frissítse. UPDATE STATISTICS authors(anames) WITH FULLSCAN, NORECOMPUTE Statisztikát mi is létrehozhatunk a CREATE STATISTICS utasítással. CREATE STATISTICS names ON Customers (CompanyName, ContactName) WITH SAMPLE 5 PERCENT A customer tábla két oszlopára kérünk statisztikát, amiket a names nevű csoporttá

fogjuk össze. A statisztikát a tábla 5 százalékának elolvasásából kérjük becsülni. Ha kí váncsiak v agyunk egy konk rét s tatisztikára, pé ldául ho gy m ikor f rissült ut oljára a D BCC SHOW STATISTICS utasítást kell használnunk. USE pubs DBCC SHOW STATISTICS (authors, UPKCL auidind) A szükségtelen statisztikákat a DROP STATISTICS utasítással törölhetjük. Feladatok 1. Miért gyorsabb megtalálni egy rekordot indexszel, mint index nélkül? 2. A fürtözött vagy a nem fürtözött index törlése tart tovább? Miért? 3. Milyen u tasítással tö rölhetjük a P RIMARY K EY é s a U NIQUE constraint-ekkel létrehozott indexeket? 4. Hozzunk l étre ké t t áblát, a hol e z e gyik t ábla F OREIGN KE Y-jel ka pcsolódik a m ásik táblához. (Találjunk ki egy életszerű szituációt, ahol szükség lehet erre a két táblára, például egy kisebb üzlet nyilvántartja a v ásárlókat és megrendeléseiket.) Legyen mindkét táblában P RIMARY K EY. H

ozzunk l étre i ndex(ek)et é s i ndokoljuk m eg, ho gy m iért hoztuk létre. UPDATE-eljük az indexek statisztikáit 9. Zárolás Az SQL Server a tranzakciók integritásának és az adatbázis konzisztenciájának megőrzése érdekében z árakat alkalmaz. A z árak m egakadályozzák, h ogy egy felhasználó el olvassa az t az adatot, a mit e gy m ásik f elhasználó é ppen m ódosít, va gy ho gy ké t f elhasználó eg yszerre módosítsa ugy anazt a z a datot. A z árakat a z S QL S erver a utomatikusan ke zeli, m égsem á rt, ha programozás közben értjük, hogy mikor, miért és mire kerülnek zárak. Ha az adatokat nem védenénk zárolással, a konkurens hozzáférések miatt többféle problémánk is adódhat. Elveszett frissítés (lost update) Ez a pr obléma a kkor j elentkezik, a mikor ké t va gy t öbb t ranzakció u gyanazt a s ort ol vassa e l, majd e gyszerre a zt mó dosítják. M ivel az e gyes tranzakciók e gymásra n incsenek t ekintettel, a

legkésőbb megszólaló tranzakció felülírja a többiek módosítását, így adatot vesztünk. Például két szerkesztő egyszerre dolgozik ugyanazon a cikken. Mindkettő módosításokat hajt végre, majd elmentik. Aki később ment, az felülírja az előző mentést, vagyis az egyik szerkesztő munkája elvész. Dirty read Az előző fejezetben már volt róla szó, annyit jelent, hogy miközben az egyik tranzakció módosít egy rekordot, a m ásik t ranzakció köz ben e zt e lolvashatja. Például egy szerkesztő megkapja a cikket, és belejavít. Javában dolgozik még, amikor egy másik szerkesztő lemásolja a dokumentumot, és szétküldi az érdekelteknek, vagy esetleg leküldi a nyomdába. Az első szerkesztő tovább folytatja a munkát, és további javításokat e szközöl, e setleg s aját kor ábbi kiegészítéseiből töröl, stb. A munka végeztével elmenti a dokumentumot A közzétett cikk így fals információkat tartalmazhat. Jobb lett volna, ha a

második folyamat (közzététel) megvárja az első (szerkesztés) végét. Nonrepeatable read Szintén szerepelt már az előző fejezetben. Példa: Egy szerkesztő kétszer is elolvassa a cikket, a két olvasás közben azonban a cikk írója módosít rajta. A második olvasáskor a szerkesztő már egy teljesen átszabott cikket lát, ami félreértésekhez vezet. Phantom A szerkesztő lemásolja a cikket, és elviszi, hogy belejavítson. Eközben valaki módosítja a cikket, mondjuk az szerzője hozzáír még pár sort. A szerkesztő később visszatér, hogy átvezesse a módosításait, és a cikkben talál néhány új sort, ami összezavarhatja. Ha most elmegy, hogy majd később foglalkozik a problémával, az írónak van lehetősége visszagörgetni az előző műveletét, és a szerkesztő számára tényleg fantom lesz a feltűnő majd ismét eltűnő néhány sor. Az S QL S erver kül önböző egységeket képes zárolni. Hogy a zárolás minél kisebb költségű

legyen, az SQL Server automatikusan eldönti, hogy melyik művelethez milyen egységet kell zárolni. Minél kisebb egységeket zárolunk (például sorokat), annál nagyobb lehetőség van konkurens feldolgozásra, viszont a sok zár kezelése erőforrásokat foglal le. Ha egy egész táblát zárolunk, az adminisztráció gyors és egyszerű, hiszen egyetlen zárat kell kezelni, de a táblát egy másik tranzakció már nem tudja elérni. Az SQL Server a következő egységeket tudja zárolni. • RID Sor azonosító, vagyis az SQL Server sor szintű zárolást képes megvalósítani. A tranzakció alatt kizárólag az érintett sor kerül zár alá. A tábla összes többi sora elérhető a többi tranzakció számára. • Kulcs (Key) Sor szintű zárolás az index állományokban. • Lap (Page) • Extent 8 lap egyszerre zárolódik. • Tábla Zárolhatunk egyszerre e gy egész t áblát i s. E kkor a t áblához t artozó i ndexek i s zárolódnak. • Adatbázis

Egy 8 KB-os egység zárolódik. Az egész adatbázis zár alá helyezhető. A zárolás módja az egyes esetekben más és más lehet. A következő módok vannak: • Shared(S) Azon műveletek esetében kerül shared lock az adatokra, amelyek csak olvassák a zokat. ( SELECT) A míg az e gyik t ranzakció ol vas e gy ilyen s ort, m ás tranzakciók nem módosíthatják, nem törölhetik, de ők is elolvashatják. A zár az olvasás befejeztével azonnal l ekerül az adatról, h acsak a t ranzakció i zolációs s zintje n em repeatable read vagy magasabb szintű. Ekkor a zár a tranzakció végéig megmarad • Update (U) Azokra az eg ységekre k erül u pdate lock, amiket valószínűleg módosítani fogunk. Ezzel elkerülhető, hogy egyidejű módosításkor holtpont (deadlock) alakuljon ki Egy tipikus módosítás a következő minta szerint zajlik. A tranzakció elolvassa a rekordot, az ol vasáskor s hared ( S) l ock ke rül a z e gységre (sorra va gy l apra), e zután m

ódosítja a rekordot, e hhez vi szont a s hared l ockot e xclusive ( X) l ockra ke ll konv ertálni. H a k ét tranzakció tesz shared lockot az egységre, majd mindkét tranzakció módosítani szeretne, az először módosítani szándékozó t ranzakció m egpróbál m ajd e xclusive lockot f eltenni. De a l ock konve rzióval várnia ke ll, m ert a z exclusive l ock ne m kom patibilis e gy m ásik tranzakció shared lockjával. A első tranzakció várakozni kényszerül, amíg a második tranzakció l e n em v eszi a s hared l ockját ( lock w ait). M ost a m ásodik t ranzakció i s módosítani szeretne, és exclusive lockkal próbálkozik. A konverzióval neki is várakoznia kell, é s m áris ki alakul a hol tpont, köl csönös e gymásra vá rakozás va n. A z i lyen t ípusú holtpont elkerülésére szolgál az update lock. Update lockot egyszerre csak egy tranzakció tehet az eg ységre. H a aztán s or k erül a t ényleges m ódosításra, az u pdate l ock ex clusive

lockká konvertálódik. Ha mégsem módosítunk, shared lock lesz belőle • Exclusive (X) Az U PDATE, D ELETE és INSERT u tasítások h asználják. H asználatával biztosítható, hogy több tranzakció nem tud egy erőforrást egyszerre módosítani. Amíg egy egységen exclusive lock van, más tranzakciók sem módosítani, sem olvasni nem tudják. • Intent Az intent lock jelzi, hogy az SQL Server a későbbiek folyamán shared vagy exclusive l ockot s zeretne el helyezni v alahol a hierarchia al sóbb s zintjén. P éldául e gy shared intent lock a táblán azt jelenti, hogy a tranzakció szeretne shared lockot használni a tábla sorain és lapjain. A tábla szintű intent lock más tranzakciókat megakadályozhat abban, hogy exclusive lockot tegyenek a tábla valamelyik lapjára. Így nő az SQL Server teljesítménye, hiszen cs ak m eg k ell n ézni a t áblán az i ntent l ockot, é s m áris el l ehet dönteni, hogy zárolható-e az a tábla. Ha ez a jelzés nem

lenne rajta, a táblát zárolni kívánó tranzakciónak végig kellene nézni a tábla minden egyes sorát, hogy meggyőződhessen róla, vajon zárolható-e az egész tábla. Háromféle intent lock van, intent shared (IS), intent exclusive (IX) és shared with intent exclusive (SIX). • Schema M típusú schema lock (Sch-M) akkor kerül egy táblára, ha azon DDL (data definition language) műveletet hajtunk végre, például új oszlopokat adunk hozzá. S típusú schema l ockot ( Sch-S) akkor ha sznál a z S QL S erver, ha éppen l ekérdezést f ordít. E z a többi l ockot ne m be folyásolja, é ppen e zért a mikor a z S QL S erver l ekérdezést f ordít, minden tranzakció tetszés szerint futhat, ugyanakkor az érintett táblákon DDL műveletek nem végezhetők. Ha egy egységen már van valamilyen zár, akkor csak egy azzal kompatibilis másik zár kerülhet rá. Például ha egy soron exclusive lock van, semmilyen más zárolás nem alkalmazható erre sorra, mert

a z e xclusive lo ck nem k ompatibilis a tö bbi lo ck típ ussal. V agy egy másik példa: h a e gy soron s hared l ock va n, e gy m ásik t ranzakció ké rhet r á s hared va gy a kár upda te l ockot i s a ttól függetlenül, hogy az első tranzakció még nem zajlott le. Exclusive lock a zonban c sak a kkor kérhető, ha az adott egységen már nincs több shared lock. Az egyes zárolási típusok kompatibilitását a következő táblázat foglalja össze. Existing granted mode Requested mode IS S U IX SIX X Intent shared (IS) Yes Yes Yes Yes Yes No Shared (S) Yes Yes Yes No No No Update (U) Yes Yes No No No No Intent exclusive (IX) Yes No No Yes No No Shared with intent exclusive (SIX) Yes No No No No No Exclusive (X) No No No No No No Az S ch-S min denkivel k ompatibilis, k ivétel az S ch-M, a z S ch-M pe dig s enkivel ne m kompatibilis. Ha kíváncsiak vagyunk a rendszerben levő aktív zárolásokra, az sp lock nevű rendszer szintű tárolt eljárást kell használnunk.

Persze ha a szerverünk éppen terhelés alatt van, nem célravezető éppen így monitorozni, mert a zárak gyorsabban fel- és lekerülnek, mint az sp lock megjeleníti azt a képernyőn. Ekkor célszerű az SQL Server Profilert, vagy az NT Performance Monitorát használni. Feladatok 1. Milyen pr oblémák m erülhetnek f el, ha n em ha sználunk s emmilyen z árolást? M inden problémáról írjunk egy vagy két mondatot! 2. Mi a DDL? 3. Melyik zárolási típus teszi lehetővé, hogy a konkurens módosításkor ne alakuljon ki holtpont? 4. Mik azok az izolációs szintek? 5. Milyen i zolációs s zintet ke ll be állítanunk, ha a zt s zeretnénk, hog y az a datbázison végrehajtott módosításaink semmilyen körülmények között ne vesszenek el?