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 objektumok scriptelése Powershellben

Azt már mindenki tudja, h PS-ben lehet kiválóan scriptet generálni a SQL entitásokról a Script() metódus segítségével. Felvetődött egy kérdés vkiben, hogy hogyan lehet csak DB sémát backupolni, és felvetődött erre a Powershell, meg a get-contentitem|%{$_.Script()}, mint alapgondolat. Ja, ez ezt adja vissza egy új táblámra:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Szóval jól néz ki, nem? Nem. addig néz ki jól, amíg nem jut eszembe, hogy van primary key is a táblán. Meg még további indexek. Amiket persze megint ki lehet scriptelni: ˘

%{$_.Indexes}|%{$_.script()}

Meg még ki tudja, mi marad ki. Na mindegy, tuti megoldást nem tudok, de ez olyan lámának tűnt, hogy elkezdtem nézegetni a metódusokat, és találtam egy olyat, hogy EnumScript():

PS > gci |%{$_.EnumScript()}
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_KeynoteDaily] PRIMARY KEY CLUSTERED
(
        [site] ASC,
        [day] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[KeynoteDaily]
(
        [failures] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ix_test2] ON [dbo].[KeynoteDaily]
(
        [measurements] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Vagyis ő megcsinálja nekünk a rekurziót. Szerintem aranyos kényelmi szolgáltatás.
Ja, a fentiekhez a teljes path a következő volt: PS SQLSERVER:\SQL\MYCOMPUTER\DENALI\Databases\MyDatabase\Tables> . Csak a tisztánlátás kedvéért.

Denali I. – SEQUENCE vs IDENTITY

Az egyik újdonság a Denali-ban a szekvencia. Eddig ilyen volt Oracle-ben, PostgreSQL-ben, most már van MSSQL-ben is. Eddig MSSQL-ben volt IDENTITY, de az egy kicsit más. Az IDENTITY egy adott tábla sémájához kötött autoincrementes oszlop, megadott kezdőértékkel és növekedési értékkel. Értéket neki nem adunk, hanem ő magától kitölti, kivéve ha azt mondjuk, hogy SET IDENTITY_INSERT tabla ON. Az általunk utoljára felhasznált értéket a @@IDENTITY változó illetve SCOPE_IDENTITY() függvény megmondja, ha szeretnénk. Ez jó. Viszont olyan mértékben a sémához van kötve, hogy az identity tulajdonság nem változtatható. Nem lehet se rátenni egy meglévő oszlopra, se levenni róla. Ez igen fájdalmas tud lenni időnként, és én még nem tudtam feldolgozni, hogy ez miért ilyen béna.
Continue reading ‘Denali I. – SEQUENCE vs IDENTITY’ »

Jogosultság lekérése T-SQL-ben

Keresgéltem a BOL-ban, és belebotlottam egy függvénybe: fn_my_permissions(). Ez igen kedvesen megmondja, hogy milyen jogaim vannak. Néhány példa:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
SELECT * FROM fn_my_permissions ('dbo.Mytable', 'OBJECT');

És mivel van kiváló impersonation is, sysadminként más jogait is lekérhetem:

EXECUTE AS LOGIN = 'GipszJakab'
SELECT * FROM fn_my_permissions ('dbo.Mytable', 'OBJECT');
REVERT

ROW_NUMBER, RANK – T-SQL ‘újdonságok’

Időnként meglepve tapasztalom, hogy emberek ignorálják az SQL szerverbe bekerült dolgokat, és nekik az SQL Server pont olyan, mint mikor megismerték. Oké, az Enterprise Manager már nincs meg, de azért más is változott. Múltkor egy fejlesztő az Oracle ROWNUM okosságát hiányolta, mert ablakozó lekérdezést akart csinálni, a fenti virtuális oszlop pedig sorszámozza a visszatérő recordsetet, és lehet azt mondani, hogy where rownum < 10. Mondtam, hogy van ilyen. Gyártsunk játszós táblát:

use tempdb

create table rankdemo (
ugyfel int,
uzletkoto int,
datum smalldatetime,
osszeg float
)

insert into rankdemo values
(1001,1,’2010-01-01′,1234),
(1002,2,’2010-01-02′,1245),
(1003,2,’2010-01-04′,6578),
(1004,3,’2010-01-03′,765),
(1005,3,’2010-01-10′,44236),
(1001,3,’2010-01-11′,75),
(1001,1,’2010-01-10′,1434),
(1002,4,’2010-01-01′,94848),
(1001,1,’2010-01-02′,44236),
(1006,1,’2010-01-31′,4876),
(1001,4,’2010-01-09′,987)

Ebben álságos szerződéskötési adatok vannak, csak a minta kedvéért, valós üzleti vállalkozáshoz a sémát nem ajánlom. És most nézzük meg az úgynevezett ranking függvényeket gyorsan:

Először is, a már emlegetett ROW_NUMBER():

select ugyfel, osszeg, ROW_NUMBER() OVER (order by osszeg desc) as sorszam
FROM rankdemo

Egyszerű, mint a szög. A hozzá hasonló RANK() a rendezés alapjául szolgáló érték “helyezését” mondja meg, azaz itt van holtverseny.
Continue reading ‘ROW_NUMBER, RANK – T-SQL ‘újdonságok’’ »