In SQL2005 zijn een aantal tabel operatoren toegevoegd, die bewerkingen kunnen doen met tabel expressies. Een tabel expressie kan voor veel verschillende dingen staan zoals een echte tabel, tijdelijke tabel, derived table, een view etc etc.
De reeds bekende operator is de JOIN constructie met de reeds bekende syntax:
Naast de JOIN zijn er drie nieuwe constructies bij gekomen te noemen:
• APPLY
• PIVOT
• UNPIVOT
APPLY
De APPLY syntax maakt het mogelijk de rechter tabel expressie uit te laten voeren voor elke rij van de linker tabel. De rechter tabel mag de kolommen van de linker tabel gebruiken om een verhouding van onderliggende afhankelijkheid tot stand te brengen (correlatie). Dit betekend dan ook dat in tegenstelling tot een JOIN, dat de volgorde van verwerking vast staat: de linker tabel wordt eerst geëvalueerd alvorens de rechter tabel wordt benadert. De CROSS syntax maakt de rechter tabel output ten opzichte van de linker verplicht en kan dus vergeleken worden met een INNER statement. De OUTER maakt de rechter tabel output optioneel en zal dus NULL retourneren als er niet aan voldaan wordt.
NB: Doordat APPLY functionaliteit is toegevoegd aan de SQL Server 2005 engine is ook een andere mooie eigenschap beschikbaar geworden, namelijk het feit dat een functie die een tabel retourneert nu ook kolommen accepteert als parameter.
Enkele code voorbeelden:
DECLARE @tPersoon TABLE (Sorteernaam nvarchar(150), PersoonID int, ManagerID int)
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID) SELECT 1 , 'Jelluh, Snelle', NULL
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 2 , 'Arjan, Zuurtje', 1
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 3 , 'Roosje, Cohen', 1
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 4 , 'Twan, Super', 2
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 5 , 'Claudia, Breekmans', 4
DECLARE @tNotities TABLE (NotitieID int IDENTITY(1,1), OwnerID int, Notitie nvarchar(250), Datum smalldatetime)
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'Is it Friday Yet?', '2010-02-01'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'Talk to the Palm !', '2010-01-01'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'More Coffee!', '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'As long as the answer is right, who cares if the question is wrong?','2010-04-05'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Beauty is only skin deep, ugly goes clear to the bone.', '2010-03-04'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Bureaucracy: a method for transforming energy into solid waste.', '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Cocaine is a nature way of telling you you have too much money.', '2010-04-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 3 , 'Complex problems have simple, easy to understand, wrong answers.', '2010-06-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 3 , 'Do someone a favor and it becomes your job.', '2010-05-03'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'Each problem solved introduces a new unsolved problem.' , '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'People tend to make rules for others and exceptions for themselves.', '2010-01-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'The best way to realise your dreams is to wake up.', '2010-06-02'
---===================================---
-- SQL 2000
-- Meest 2 recente notities van de personen
---===================================---
SELECT tP.Sorteernaam
,tN.Notitie
,tN.Datum
FROM @tPersoon tP
LEFT OUTER JOIN @tNotities tN ON tN.OwnerID = tP.PersoonID
WHERE tN.NotitieID IS NULL
OR tN.NotitieID IN (SELECT TOP 2 NotitieID
FROM @tNotities tN2
WHERE tN2.OwnerID = tP.PersoonID
ORDER BY Datum DESC)
ORDER BY tP.Sorteernaam
---===================================---
-- SQL 2005
-- Meest 2 recente notities van de personen
---===================================---
SELECT tP.Sorteernaam
,tRN.Notitie
,tRN.Datum
FROM @tPersoon tP
OUTER APPLY (SELECT TOP 3 tN2.NotitieID
,tN2.Notitie
,tN2.Datum
FROM @tNotities tN2
WHERE tN2.OwnerID = tP.PersoonID
ORDER BY Datum DESC) tRN
ORDER BY tP.Sorteernaam
PIVOT
Met het gebruik maken van de PIVOT techniek is het mogelijk om rijen te roteren naar kolommen van een select statement en tijdens het roteren kan de data ook meteen geaggregeerd worden. Het PIVOT statement is opgebouwd uit drie elementen:
1. De aggegratie die zal plaatsvinden op de waarden in de groep.
2. De FOR statement geeft de kolom aan waar uit de rijen geselecteerd gaan worden die geroteerd worden.
3. Het IN statement geeft de uiteindelijke rijen aan die als kolommen getoond gaan worden.
NB: Omdat er wel een aggegratie plaatsvindt maar geen group by clause is gespecificeerd, wordt er onder water gegroupeerd bij alle kolommen van alle tabellen die niet benoemd zijn in de PIVOT. Dit betekent dat je eigenlijk altijd met derived tabels moet werken om de output zo klein mogelijk te houden en zo onnodige groeppeer acties te verkomen.
Enkele code voorbeelden:
---==============================---
-- Output klein houden, om onnodige groepeer acties te verkomen
---==============================---
SELECT Sorteernaam,[6],[5],[4]
FROM (SELECT tN.OwnerID
,Month(Datum) AS Maand
FROM @tNotities tN
) tDerived
INNER JOIN @tPersoon tP ON tP.PersoonID = tDerived.OwnerID
PIVOT (SUM(OwnerID)
FOR Maand IN ([6],[5],[4])
) AS tPivot
Tabel expressies
In SQL 2005 is een nieuw type tabel expressie geintroduceerd, namelijk de Common Table Expressie (CTE) . Een CTE lijkt op vele opzichten op een derived table en alle validaties die van toepassing op een derived table zijn dan ook van toepassing op een CTE, te noemen dat de query een tabel moet retourneren en dus alle kolommen namen moeten hebben en geen order by clause is toegestaan. Omdat CTE’s het WITH statement gebruiken kan dit botsen met andere syntax die ook WITH gebruiken. Het statement die voor de CTE wordt uitgevoerd wordt moet dan ook afgesloten worden met een semicolon (;).
NB: Het gebruik van Semicolon (oftewel puntkomma) om een statement af te sluiten is in SQL2005 geintroduceerd. Het wordt in deze versie nog niet afgedwongen op een paar cases na zoals CTE’s. Toch wordt verwacht dat in toekomstige versies de semicolon verplicht gaat worden, dus om code compatible te houden en jezelf het gebruik alvast aan te leren is het wel wijs om jezelf dit aan te leren.
Van een Common Table Expressie is de syntax als volgt opgebouwd:
Er zijn echter ook verschillen tussen een derived table en een CTE. Een CTE kan bijvoorbeeld niet direct genestd worden. Daarintegen kan je meerdere CTE’s onder elkaar defineren gebruik makend van het zelfde with statement.
CTE’s hebben nog meer voordelen, zo kan je meerdere keren in je outer query verwijzen naar de CTE tabel zonder de definitie van de CTE te herhalen, dit in tegenstelling tot een derived table. De CTE is niet toegankelijk voor andere query’s in de batch, enkel voor de query rechtstreeks volgend na de CTE definitie. CTE’s maken het ook mogelijk om complexe recursieve vraagstukken op te lossen, zodat hierachieen, bomen etc verwerkt kunnen worden.
Een recursieve CTE is opgebouwd uit tenminste 2 queries. Eerst wordt een zogenaamde anchor query gedefineerd. Dit is de tabel die eigenlijk gewoon een resultset terug retourneert en is de basis tabel waarover recursie gaat plaatsvinden De tweede tabel is de recursieve tabel waar een relatie wordt gelegd met de CTE naam, dit is niet te verwarren met dezelfde relatie die de outer query legt met de CTE naam. De outer query krijgt namelijk het uiteindelijk resultaat terug geretourneerd. De referentie die de recursieve query legt naar de CTE naam (door verwijzing naar de CTE wordt de query een recursieve query), verwijst naar een resultaat wat nog in verwerking is.
NB: Hoe diep een recursie kan gaan, mag worden ingesteld met het statement OPTION (MAXRECURSION
NB: Kolommen die met betrekking hebben op elkaar door de UNION moet het zelfde datatype hebben.
Enkele code voorbeelden:
---===================================---
-- Meerdere Common Table Expressie's (CTE)
-- Geef Maanden waarvan aantal actieve
-- notitiegebruikers grotergelijk is aan 2
---===================================---
;WITH C1 AS
(
SELECT Month(tN.Datum) AS Maand
,tN.OwnerID AS EigenaarID
FROM @tNotities tN
),
C2 AS
(
SELECT Maand
,COUNT(DISTINCT EigenaarID) AS AantalNotities
FROM C1
GROUP BY Maand
)
SELECT Maand, AantalNotities
FROM C2
WHERE AantalNotities >= 2
---===================================---
-- Common Table Expressie (CTE) meerdere keren naar verwijzen
-- Bepaal de toename van het aantal actieve gebruikers
-- die de notitie functionaliteit gebruiken per maand.
---===================================---
;WITH cteNotitiesPerMaand AS
(
SELECT Month(tN.Datum) AS Maand
,COUNT(DISTINCT OwnerID) AS AantalNotities
FROM @tNotities tN
GROUP BY Month(tN.Datum)
)
SELECT tNPM.Maand AS Maand
,tNPM.AantalNotities AS AantalNotities
,(tNPM.AantalNotities - tNPM2.AantalNotities) AS ToenameAantalNotities
FROM cteNotitiesPerMaand tNPM
LEFT OUTER JOIN cteNotitiesPerMaand tNPM2 ON tNPM.Maand = (tNPM2.Maand + 1)
ORDER BY Maand
---===================================---
-- Recursieve Common Table Expressie (CTE)
---===================================---
;WITH ctePersonenMetManager AS
(
--Anchor
SELECT PersoonID
,Sorteernaam
,ManagerID
,0 AS TreeLevel
FROM @tPersoon
WHERE ManagerID IS NULL -- Root
UNION ALL
--Recursive Member
SELECT tP.PersoonID
,tP.Sorteernaam
,tP.ManagerID
,tRecursief.TreeLevel + 1 AS TreeLevel
FROM ctePersonenMetManager AS tRecursief
INNER JOIN @tPersoon tP ON tP.ManagerID = tRecursief.PersoonID
)
SELECT * FROM ctePersonenMetManager
Set operatoren
Joins kunnen gezien worden als horizontale operaties tussen tabellen. Een join genereert dan ook een viruele tabel, die uit kolommen bestaat van meerdere tabellen. Een Set operator kan gezien worden als een verticale operatie tussen tabellen. Het verbreedt de data niet in de zin van meerdere kolommen, maar wel in de zin van meer of minder data te tonen dan 1 tabel. Een reeds bekende set operator is de UNION (ALL). In SQL 2005 zijn er twee nieuwe bij gekomen namelijk de EXCEPT en INTERSECT.
EXCEPT
Met het statement EXCEPT kan je rijen indentificeren welke in het eerste SQL statement voorkomen en die niet aanwezig zijn in het tweede SQL statement.
NB: In SQL 2000 wordt vaak NOT EXISTS gebruikt om vershillen tussen tabellen te herleiden. Een groot nadeel van NOT EXISTS statement is dat NULL=NULL als verschil wordt gezien.
INTERSECT
Met het statement INTERSECT kan je rijen indentificeren die in beide SQL statement aanwezig zijn.
NB: Voor alle SET operatoren geldt dat het aantal kolommen moet overeenkomen in de verschillende SQL statements. Ook moet het datatype per corresponderende kolom overeenkomen.
Enkele code voorbeelden
---===================================---
-- SQL 2000 versus SQL 2005
-- NOT EXISTS versus EXCEPT
---===================================---
CREATE TABLE #tDummyTabel1 (Straat VARCHAR(50), Postcode VARCHAR(10), Plaats VARCHAR(50))
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Kipstraat',NULL,'Rotterdam')
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Koestraat','2014GH','Rotterdam')
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Varkenstraat','2045JG','Rotterdam')
CREATE TABLE #tDummyTabel2 (Straat VARCHAR(50), Postcode VARCHAR(10), Plaats VARCHAR(50))
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Kipstraat',NULL,'Rotterdam')
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Koestraat','2014GH','Rotterdam')
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Visstraat','2045JG','Rotterdam')
--SQL 2000 , ziet NULL als verschil
SELECT t1.Straat,t1.Postcode,t1.Plaats
FROM #tDummyTabel1 t1
WHERE NOT EXISTS(SELECT 1
FROM #tDummyTabel2 t2
WHERE t1.Straat = t2.Straat
AND t1.Postcode = t2.Postcode
AND t1.Plaats = t2.Plaats)
--SQL 2005 , except geeft werkelijk verschil
SELECT Straat,Postcode,Plaats FROM #tDummyTabel1
EXCEPT
SELECT Straat,Postcode,Plaats FROM #tDummyTabel2
---===================================---
-- SQL 2005
-- INTERSECT
---===================================---
SELECT Straat,Postcode,Plaats FROM #tDummyTabel1
INTERSECT
SELECT Straat,Postcode,Plaats FROM #tDummyTabel2
Aggregatie
In SQL 2005 zijn vele nieuwe opties om data te aggregeren, eerder is al besproken PIVOT en UNPIVOT methodiek. Er zijn echter nog andere mooie nieuwe features in 2005 zoals het OVER statement en de CUBE optie.
OVER
De OVER clause is een zeer eenvoudige en krachtig statement, dat het mogelijk maakt om data te aggregeren zonder een group by clause. Hierdoor wordt het dus mogelijk om geaggreerde data te combineren met data die niet geaggreerd is. Tevens is het ook toegestaan om meerdere aggregaties op verschillende kolommen te laten plaatsvinden. De OVER clause is ook nog eens zeer efficient omdat de data maar 1 keer gescand wordt en alle aggregaties hierop worden uitgerekend.
CUBE
Met behulp van het CUBE statement maak je vraagstukken met betrekking tot geaggreerde data flexibel. Een cube statement verkomt dat je voor elke gewenste vraagstuk een nieuwe groepeer actie moet laten uitvoeren. Om gegroepeerde data toegangelijk te maken voor meerdere vraagstukken kan je het WITH CUBE Statement toevoegen onder de Group by clause.
Goed werk Jelle!
BeantwoordenVerwijderen