CTE = Common Table Expression

Á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… :)

SQL 2005 SP3 install és elmozgatott rendszeradatbázisok

Épp eszembe jutott, és gondoltam megosztom mindenkivel: pár hónapja SP3-at installáltunk a SQL-re, és azt találtuk, hogy elfrakkol az install. Újrakezdve is elfrakkolt. Meg kellett nézni a logot (programkönyvtár\Setup Bootstrap könyvtárban laknak ezek az izék), és kiderült a hiba: a distmodel.mdf fájl hiányzott a drágámnak, mert ott kereste, ahol a master.mdf volt, de mi átmozgattuk máshova időközben. Úgyhogy baráti jótanács: ha elmozgatjátok a rendszeradatbázisokat, mozgassátok az összes adatfájlt együtt :) Kicsit deja vu érzésem van – lehet, hogy ezt már megírtam egyszer?

Deprecation warningok

(mostanság nem volt időm írni, mivel dolgozom :), ha nem a munkahelyemen, akkor pedig egy menedzsment adatbázist reszelek, ami reményeim szerint tök kényelmessé teszi a DBA életét)

Az SQL 2005 profilerben megjelent egy érdekes eseménycsalád, a Deprecation, azaz elavulás. Ez mutatja meg, hogy milyen dolgokat használunk az SQL Serverben azokból, amik meg lesznek szüntetve. Két esemény van benne: a Deprecation Announcement, ami azt mondja, hogy vigyázz, mert ezt rátettük a halállistára, és a Deprecation Final Support, ami azt mondja, hogy ez a lista tetején van, és a következő major release-ben már kiveszik. A következő major release SQL 2005-nél a 2008, 2008-nál pedig a 2008 R2.

Könnyű kipróbálni: indítsunk el egy profilert a Deprecation eseményekre (ha bepipáljuk a show all events boxot, akkor biztos meglesz), és nyissunk egy query window-t a profilerezett SQL szerveren! (Amennyiben a szervert használjuk másra is, mint játékra, akkor szűrjük le a profilert a query window spidjére, amit a status baron megtalálunk a felhasználónevünk után zárójelben.) Abba pedig írjuk bele a következőt:

SELECT name FROM msdb.dbo.sysjobs (NOLOCK)

Majd nézzük meg a profilert!

Specifying table hints without using a WITH keyword is a deprecated feature and will be removed in a future version.

Tehát működik. A haszna nyilvánvaló: migráció előtt látja az ember, hogy mivel lehet probléma, de én egy kicsit továbbmegyek ennél: éppen most készülök bedobni azt, hogy nem veszünk át olyan adatbázist vagy módosítást, ahol deprecation warning van. Javítsák ki most, ne legyen még egy csontváz a szekrényben, az már így is tele van…

Szerintem érdemes időnként futtatni egy kicsit, és megnézni, hogy miket kap el. Nálam a master, msdb és AdventureWorks adatbázisokat :) Szóval érdemes szűrni felhasználói adatbázisokra (databaseid > 4) éles üzemben, a Microsoft meg majd rendet tesz a rendszeradatbázisokban.

SQL Server Standard Edition és Lock Pages in memory 64 biten

Ha az ember egy erősebb vason SQL Server Standard Editiont futtat, akkor találkozhat a kedves hibaüzenettel az errorlogban:
A significant part of sql server process memory has been paged out. This may result in performance degradation
Ez kellemetlen lehet, a oprendszer kitalálja, hogy neki másra (is) kell memória, és kiülteti az SQL-t a kispadra, aminek a teljesítménye tényleg leromolhat ilyenkor, nem viccel az errorlog.

Enterprise Edition esetén ez nem probléma, mivel az rendelkezik a mágikus “Lock pages in memory” képességgel (feltéve, hogy a futtató user megkapta a szükséges OS-szintű jogot), de a standardon bizony sokat szívtak ezzel emberek, beleértve engemet is. Éppen ezért örülök nagyon annak, hogy végre kivették ezt a bosszantó korlátozást, pontosabban kiveszik hamarosan. Még pontosabban egy trace flag segítségével be lehet majd kapcsolni azt, hogy lehessen lockolni a memóriába a lapokat. A várható szállítási idők:

  • SQL 2008 SP1 CU 2 – 2009 május
  • SQL 2005 SP3 CU 4 – 2009 június

Én már felírtam a naptáramba :) A PSS blogon az eredeti post itt olvasható.

Az légy, aki lenni akarsz

A cím nem a személyiségfejlődéshez, inkább a más accountok megszemélyesítéséhez kapcsolódik. Az SQL Server ugyanis lehetőséget ad arra, hogy egy megfelelő jogosultságokkal rendelkező login alól egy másik loginra (vagy userre) váltsunk át, és annak a nevében futtassunk lekérdezéseket. Ez különösen hasznos lehet például hibakeresésnél vagy ha vissza akarunk élni a hatalmunkkal.

SQL 2000 alatt már megvolt a SETUSER nevű parancs, ami átváltott egy megadott user alá, és viszonylag lehetett használni, de az igazi boldog élet a 2005-tel köszöntött be, ő hozta el az EXECUTE AS utasítást. Itt már megmondhatjuk, hogy database user vagy server login szinten akarunk imperszonálni, lehet ezt a privilégiumot bárkinek megadni (SETUSER csak sysadmin vagy dbo joggal megy), illetve lehet több szint mélyre lemászni, és onnan visszajönni.
Continue reading ‘Az légy, aki lenni akarsz’ »