Rollback

Sok fejlesztő használ naming conventiont (nevezési szokást) az adatbázis objektumainak elnevezésénél. Ez kedves tőlük, mivel kiszámíthatóbbá teszik a karbantartók (beleértve saját magukat) életét. Ámde vannak kevésbé szerencsés naming conventionök, és itt nem csak az akkor jó ötletnek tűnő Systems Hungarian notationre gondolok.

Többször találkoztam az sp_ prefixszel, mint tárolt eljárás jelöléssel, ami jó ötletnek tűnik, a Microsoft is ezt favorizálja, úgyhogy biztos jó. De nem az. Az sp_ ugyanis a Microsoft sajátja, amit befoglaltak kódból: minden sp_ kezdetű tárolt eljárást a master adatbázisban keres először az SQL Server, és ha ott nem találja, akkor nézi meg az aktuális adatbázist. Benne is van a BOL-ban, hogy ne használd, mert ráfázol. És íme, a példa:

use tempdb
go
create procedure sp_password
AS
print 'De rossz nev ez...'
GO

És próbáljuk meg futtatni a mi kis sp_password SP-nket. Ez sosem fog sikerülni, még ha két- vagy háromtagú nevet adunk meg, akkor se.

EXEC sp_password
-- fail
EXEC tempdb.dbo.sp_password
-- ez is fail
GO

Az egyetlen elfogadható kiút, ha nem a dbo schema alá rakjuk, és legalább kéttagú névvel hivatkozunk rá:

create schema test
GO
create procedure test.sp_password
AS
print 'De rossz nev ez...'
GO
EXEC test.sp_password

Azért valljuk be, ez így gáz. Használjunk valami jobb nevezéket…

Ha esetleg valakiben felmerülne a kérdés, hogy ez miért jó dolog, akkor gondolja végig a fenti példa esetleges működésének következményeit: Gonosz Géza olyan szép trójai sp_password-öt produkálhat, hogy mindenki csak nézni fog. A security fontos dolog…

Alcím: Hogyan nem tud valaki belépni egy SQL Serverbe? A belépés egy két részből álló folyamat, mint minden rendszer esetében: autentikáció és authorizáció. Az autentikáció mondja meg, hogy kik vagyunk: felhasználónév+jelszó például. Az authorizáció pedig megmondja, hogy mi, akik azok vagyunk, akik, mit tehetünk, mire vagyunk feljogosítva.

Ebból következik, hogy a legegyszerűbb nem-belépés az, ha elbukjuk az autentikációt. De az túl egyszerű. Inkább nézzük meg, hogyan lehet elbukni az authorizációt olyan mértékben, hogy be sem jutunk.

A legegyszerűbb a letiltás:

CREATE LOGIN [SQL01\ddisable] FROM WINDOWS;
ALTER LOGIN [SQL01\ddisable] DISABLE;

Csináltunk egy SQL logint egy domain usernek, majd letiltottuk a logint. Könnyű és nyilvánvaló, pipa.

Egy kicsit régebbi történet a DENY LOGIN:

EXEC sp_denylogin 'SQL01\ddeny';

Ez létrehozza az SQL logint a domain userhez, és annak rögtön meg is tiltja, hogy belogoljon. Erről a tárolt eljárásról egyébként azt írja a BOL, hogy elavult:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER LOGIN instead.

Oké, és hogy használjuk az ALTER LOGIN-t? Hát, lehet DISABLE-t mondani, mint az előbb. De az nem egészen ugyanaz: a disable a sys.server_principals catalog is_disabled mezőjét állítja. A deny login pedig nem. Ő a sys. server_permissions catalog view-t bővíti egy sorral, amiben a CONNECT SQL jogot tiltja. Vagyis a fenti deny login script igazából így néz ki modernül:

CREATE LOGIN [SQL01\ddeny] FROM WINDOWS;
DENY CONNECT SQL TO [SQL01\ddeny];

És hab a tortán: ha ez nem elég, akár még ki is lockolhatunk egy accountot. Mi kell ehhez? egy olyan local account (vagy domain) policy, amiben van account lockout N próbálkozás után, plusz be kell állítanunk, hogy az SQL login ellenőrizze a policyt.

CREATE LOGIN slocked WITH PASSWORD='Password2',  CHECK_POLICY=ON;

Természetesen ez csak SQL loginra igaz, mivel a windows accountok lockoutját a Windows maga végzi. Rontsuk el párszor a jelszavát, és azt fogja mondani a jó jelszóra, hogy ki vagyunk lockolva. Erre két lehetőség van: vagy a jelszó ismeretében az ALTER LOGIN UNLOCK, vagy anélkül az ALTER LOGIN CHECK_POLICY=OFF, majd ON:

ALTER LOGIN slocked WITH PASSWORD='Password2' UNLOCK;
ALTER LOGIN slocked CHECK_POLICY=OFF;
ALTER LOGIN slocked CHECK_POLICY=ON;

Már csak egy kérdés maradt: mi a különbség a disable meg a deny között? Hát, a disable kb. arra jó, mint a Windows account disable: fel lehet vele függeszteni valaki-valami hozzáférését egy időre. A deny viszont azt a problémát tudja orvosolni, amikor egy csoportnak adtunk jogot, de azon belül egy kisebb csoportnak, vagy egyes személyeknek nem akarunk hozzáférést adni mégsem. A deny segítségével “kitakarhatjuk” őket a jogosultak köréből.

Az unlocknak pedig az a varázsa, hogy ha beállítjuk egy alkalmazásusernek, akkor simán ki tudja zárni az alkalmazást bárki az adatbázisból… :)

A count() függvénnyel kapcsolatosan van pár dolog, ami itt ugrál a fejemben már egy ideje, most kiborítom őket.

Először is: van egy komoly vallási kérdés a témában, miszerint vajon a count(1) vagy a count(*) a jobb. Ezt mind Oracle, mind MSSQL platformon keményen nyomják emberek. Legyünk egyszerűek, nézzünk meg egy végrehajtási tervet, és lássuk a valót: pontosan ugyanazt csinálja a szerver mindkét esetben. Lelke mélyén ő tudja, hogy pontosan ugyanaz a kérdés. Na de honnan van a válasz? Mivel azt kérdezzük ilyenkor, hogy hány sor is van a táblában, ez egy index scan lesz. És mivel az SQL Server okos, ezért a legkisebb lapszámú indexet fogja felolvasni. Nézzétek meg sok indexet hordozó táblákon, hogy milyen lehetetlen indexet bök ki. Általában azokat, amiknek nagy a NULL aránya, mivel azok kicsik. Persze filtered indexre nem ugrik.

A másik egy személyes élmény. Azt vettem észre egy szép napon, hogy a szerverek nagyon sok időt töltenek azzal, hogy select count(*) from sysfiles vagy select count(*) from sysobjects lekérdezéseket futtatnak. Hamar rájöttem, hogy ez egy marék Java alkalmazás műve, melyek sok kicsi lekérdezést indítottak, melyek előtt a JDBC driver futtatott egy health check kverit, hogy tudja, hogy jó az adatbáziskapcsolat, amit a poolból vett ki. Történetesen a health check drágább volt, mint sokszor a lekérdezés maga. A masteren a resourcedb miatt még join is van az execution planben. Mi egyszerűen átálltunk az alulmúlhatatlan select 1-re, de ha valakinek van hasonló gondja, inkább azt ajánlom, hogy válasszon egy táblát, és abból kérje le az első sort. Nekem ez sajnos a sok kis adatbázis miatt nem menő, mert senki nem fog mindegyikben táblát keresni, de másnak bejöhet. Nálam ez hatszoros telejsítménykülönbséget jelentett: 0.003 vs 0.018 total subtree costok jelentek meg.

Előzőleg elmondtam ,hogy milyen fontos a DBCC CHECKDB futtatása, és erre gondolom mindenki kedvet kapott arra, hogy megnézze, mikor futott ez le az ő kis adatbázisain utoljára sikeresen. Felütötte mindenki a BOL-t, ééés… nem volt benne. Ja, ez is egyike azon undocumented apróságoknak:

DBCC DBINFO ('master') WITH TABLERESULTS;

Ez visszaad egy recordsetet, és abban select * where field = ‘dbi_dbccLastKnownGood’. Hát ennyi.

Általános jelenség scriptelés közben, hogy valahol felbukkan egy subquery, pl. a “minden számla, ahol a vevő budapesti” típusú kérdésre. Ez lehet join vagy subquery, általában mindegy, a query optimizer ugyanazt a végrehajtási tervet produkálja belőlük.

-- a subquery
SELECT * FROM Szamlak
WHERE
   vevoid in
   (SELECT vevoid FROM Vevo Where varos = 'Budapest')
-- a join
SELECT s.* FROM Szamlak s
   JOIN Vevo v
   ON v.vevoid = s.vevoid
   WHERE v.varos = 'Budapest'

És vannak esetek, amikor még ez sem elég, azokra a nehéz napokra ott van a CTE, vagyis Common Table Expression. Mielőtt belemennék, hogy mi is ez, nézzük meg a fenti példát CTE-vel, ami teljesen értelmetlen, de legalább a szintaktikát felismerjük:

WITH BpVevok (vevoid)
AS (
   SELECT vevoid FROM Vevo Where varos = 'Budapest'
)
SELECT s.* FROM Szamlak s
   JOIN BpVevok
   ON BpVevok.vevoid = s.vevoid

Semmivel nem tűnik egyszerűbbnek, nem is gyorsabb, akkor meg mire jó? A CTE nem más, mint tuljadonképpen egy ideiglenes nézet, egy olyan view, amit a query elején a WITH clause-zal definiálunk, aztán a queryben meg felhasználjuk, akár többször is. Ez az egyik előnye, hogy újrahasznosítható, és akkor viszont már egyszerűség meg gyorsaság is felbukkan. Viszont amiért én pénteken hozzányúltam, az valami egészen más tulajdonsága: szereti a rekurziót is, és lehet “rekurzív nézeteket” definiálni benne. Ez nagyon jól jön például hierarchikus adatoknál, amikor van egy rekordazonosító meg egy szülőazonosító oszlop. De nézzük inkább, hogy én mire használtam, az talán jobb, mintha értekeznék.

Adott egy tábla, amiben fájlszerverről szóló adatok vannak, többek között: objektumazonosító; fájl, illetve könyvtárnév; projekt; lejárati idő; plusz a szülő könyvtár azonosítója. minta:
id|fajlnev|projektid|lejarat|szuloid
12234|feladatok.doc|4334|2011-12-31|3654
3654|output|4334|2012-01-31|1111
1111|teszt|4334|2020-01-20|NULL

A teljes feladathoz pedig az egyik részfeladat az, hogy minden fájlnak kell a teljes elérési útvonala is, amit subquerykben/joinokban kell majd használni, tehát pl.
12234|feladatok.doc|\teszt\output\feladatok.doc|4334|2011-12-31|3654

Akár a gyökérelem azonosítóját is hozzáadhatnánk, nem bonyolítjuk a helyzetet. Transact-SQL-ben ez egy érdekes feladat lenne CTE nélkül, írhatnék rekurzív függvényt például, de a CTE sokkal cukibb:

WITH FileCTE (id, fajlnev,eleresiut,projektid,lejarat,szuloid)
AS (
  SELECT id, fajlnev, '\' + fajlnev AS eleresiut, projektid, lejarat, szuloid
  FROM filetabla
   WHERE szuloid IS NULL
   UNION ALL
   SELECT f.id, f.fajlnev, c.eleresiut + '\' + f.fajlnev AS eleresiut, f.projektid, f.lejarat, f.szuloid
   FROM filetabla f
     JOIN FileCTE c
     ON c.id = f.szuloid
)
-- itt jön az igazi kelérdezés
SELECT f.id, f.projektid
FROM filetabla f
JOIN FileCTE c
ON f.id = c.id
-- join meg egy csomo minden...
WHERE c.eleresiut LIKE '%penzugyi terv%xls'

Hát erre jó a CTE. (Meg arra, hogy maga a kveri már átlátható, mert a szörnyű subquery-k ki vannak emelve az elejére… :)