Het Fundament van SQL (Logisch Proces)

Ik heb regelmatig kennisoverdracht sessies naar collega's en vind het zonde om de kennis niet verder te delen. Hieronder een beschrijving van een kennissessie mbt de SQL fundamenten, die kennis is vooral handig voor mensen die proberen de SQL taal onder de knie te krijgen. Ik heb bij het opstellen van het document met name geput uit de verschillende SQL inside boeken die microsoft op de markt heeft gebracht. Deze boeken zijn een aanrader voor mensen die dieper in het SQL Server wereldje willen duiken.


Om goede SQL code te kunnen schrijven, is het handig om te weten hoe SQL server onder water een query oplost. Er kan onderscheid gemaakt worden tussen het logische proces en een fysiek query proces. Het logische proces kan gezien worden als een soort stappen plan dat gebruikt wordt om een query uit voeren. Het daad werkelijke fysieke proces kan hiervan afwijken doordat er meer factoren om de hoek kijken, zoals indexen meerdere processoren op een server, waardoor sql server kan besluiten om bepaalde processen doormiddel van een kortere route efficiënter op te lossen.

Logisch query proces

SQL lost een query in vergelijking met andere talen niet op in de volgorde waarin je je code schrijft. Daarintegen wordt de SQL query in z’n geheel geanalyseerd en doormiddel van het volgende stappen plan uitgevoerd:

 






Elk boven genoemde stap levert onder water een virtuele tabel op. Deze virtuele tabellen zijn niet benaderbaar voor de uitvoerende, enkel de virtuele tabel die in de laatste stap gegeneerd is, wordt geretourneerd. Als een bepaalde clause niet is opgenomen in de query, bijvoorbeeld een group by dan wordt deze stap gewoon overgeslagen in het logische proces.

STAP (1) Cartetisch product
In de eerste stap wordt er een cartetisch product gemaakt tussen de eerste 2 tabellen voorkomend in de from clause. Dit cartetisch product (= cross join of onbeperkte join) wordt weggeschreven naar een virtuele tabel en bevat het aantal rijen van tabel 1 maal het aantal rijen van tabel 2

STAP (2) ON Filter
In de volgende stap wordt de ON filter toegepast, hier worden alle rijen gefilterd waarvoor geldt dat de op gegeven join conditie waar is. De ON filter is de eerst toegepaste filter van de 3 filters condities die SQL kent (ON, WHERE en HAVING). De output van de filtering wordt weer weggeschreven naar een virtuele tabel zoals bij elke stap.

STAP (3) Outer Rows
Bij de derde stap worden alle rijen, die gemarkeerd zijn als ‘te behouden’ door een outer join weer toegevoegd. Een Left Outer Join makeert alle rijen van de linker tabel als te behouden en een Right Outer Join alle rijen van de rechter tabel. Als er geen outer join (Left, Right, Full) is gespecificeerd zal deze stap worden overgeslagen.

NB: Als er meer dan 2 tabellen zijn gejoined wordt de stappen 1 t/m 3 toegepast op de derde tabel in de from clause met de output van virtuele tabel van stap 3 van de eerste 2 tabellen etc. Totdat alle tabellen verwerkt zijn. Met de uiteindelijke virtuele tabel worden de volgende beschreven stappen (4 t/m 11) afgehandeld.

STAP (4) WHERE Filter
De where conditie filter alle rijen, waarvoor de opgegeven conditie waar is. De gefilterde conditie zorgt weer voor een nieuwe virtuele tabel.

NB: Specificeer geen where filter op een tabel de ge-outer joined is, aangezien daardoor de outer join weer ongedaan wordt gemaakt. De rijen die zijn toegevoegd in stap 3 worden namelijk weer door stap 4 weggefilterd. Er kunnen geen filters worden toegepast op geaggregeerde data en aliassen van de select aangezien deze voor SQL Server nog niet bekend zijn in pas in latere stappen worden verwerkt.

Enkele code samples om het te verduidelijken:
DECLARE @tPersoon TABLE (Sorteernaam nvarchar(150), PersoonID int)

INSERT INTO @tPersoon (PersoonID, Sorteernaam) SELECT 1 , 'Jelluh, Snelle'
INSERT INTO @tPersoon (PersoonID, Sorteernaam) SELECT 2 , 'Arjan, Zuurtje'
INSERT INTO @tPersoon (PersoonID, Sorteernaam) SELECT 3 , 'Roosje, Cohen'
INSERT INTO @tPersoon (PersoonID, Sorteernaam) SELECT 4 , 'Twan, Super'

DECLARE @tTelefoon TABLE (OwnerID int, Nummer nvarchar(20), IndMobiel bit)
INSERT INTO @tTelefoon (OwnerID, Nummer, IndMobiel) SELECT 1 , '0612345678', 1
INSERT INTO @tTelefoon (OwnerID, Nummer, IndMobiel) SELECT 2 , '0687654321', 1
INSERT INTO @tTelefoon (OwnerID, Nummer, IndMobiel) SELECT 2 , '01087654321',0
INSERT INTO @tTelefoon (OwnerID, Nummer, IndMobiel) SELECT 4 , '01012345678',0

/*Dit voorbeeld geeft weer hoe een een filter opennemen in de where clause de left outer join ongedaan maakt */
SELECT tP.Sorteernaam, tT.Nummer
FROM @tPersoon tP
LEFT OUTER JOIN @tTelefoon tT ON tT.OwnerID = tP.PersoonID
WHERE tT.IndMobiel = 1

/*Om toch te kunnen filteren, kan je de filter opnemen bij de left outer join, als volgt, vergelijk het resultaat maar met bovenstaande query */
SELECT tP.Sorteernaam, tT.Nummer
FROM @tPersoon tP
LEFT OUTER JOIN @tTelefoon tT ON tT.OwnerID = tP.PersoonID
                                 AND tT.IndMobiel = 1

/*Een filter op een Alias geeft levert een foutmelding op, Aliassen zijn immers pas bekent in stap 8. Je kan dus alleen filter op originele kolom namen, zoals sorteernaam. */
SELECT tP.PersoonID
      ,tP.Sorteernaam AS Name
FROM @tPersoon tP
WHERE Name Like 'Jel%'
Msg 207, Level 16, State 1, Line 29
Invalid column name 'Name'.
 
/*Filtering op geaggregeerde waarden kan niet met behulp van een where clause, de groepeer actie vindt namelijk plaats in stap 5, daarom zal je de having clause moeten gebruiken die in stap 7 verwerkt wordt om te kunnen filteren op geaggregeerde data. */
SELECT tP.Sorteernaam
      ,COUNT(tT.Nummer) AS Telefoonnrs
FROM @tPersoon tP
INNER JOIN @tTelefoon tT ON tT.OwnerID = tP.PersoonID
WHERE COUNT(tT.Nummer) > 1
GROUP BY tP.Sorteernaam
Msg 147, Level 15, State 1, Line 40
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
 
STAP (5) Groeperen
Gekomen bij stap 5 worden de kolommen gespecificeerd in group by clause gegroepeerd. Elke uniek combinatie resulteert in een groep. Elke rij voortgekomen uit de virtuele tabel van stap 4 kan maar 1 keer gekoppeld worden aan een specifieke groep. Als er een groepeer actie is gedefinieerd binnen een query dan kunnen er alleen expressies worden gedefinieerd die een ‘scalar’ waarde (enkelvoudig) opleveren per groep. Er kunnen dus alleen waarden voor de vervolgstappen worden gebruikt als ze of een in de group by clause of een geaggregeerde functie bevatten.

STAP (6) CUBE/ROLLUP Optie -- Nieuw SQL 2005
Als er gebruikt gemaakt van cube of rollup binnen de query worden super groepen gecreëerd in een nieuwe virtuele tabel.

STAP (7) HAVING Filter
In stap 7 wordt de having filter uit gevoerd op de groepen geretourneerd uit de eerdere stap. Alle waarden waarvoor de filter waarde waar is resulteert in een nieuwe virtuele tabel. The Having filter is de enige filter die gebruikt kan worden voor gegroepeerde data.

STAP (8) SELECT Clause
De select clause wordt in stap 8 verwerkt. In stap 8 wordt de tabel samengesteld die uiteindelijk geretourneerd wordt naar de uitvoerende. De select clause kan kolommen retourneren gebaseerd uit de virtuele tabellen die eerder gecreëerd zijn. Vergeet niet dat als een query een groepeer actie heeft kan enkel geselecteerd worden uit de kolommen die gespecificeerd zijn in de group by clause of een doormiddel van een functie geaggregeerd zijn. De aliassen meegegeven aan de geselecteerde kolommen mogen in de vervolgstappen gebruikt worden.

NB: SQL wordt uitgevoerd met het All-In-Once principe. Dit betekent aliassen gedefinieerd in de select clause zijn niet beschikbaar (niet bekend) voor verwerkingen in ander gedeelte van dezelfde select. Volgende statement is bijvoorbeeld niet toegestaan:
SELECT kolom1 + 1 AS k1 ,k1 +1 AS K2

Enkele code samples om het te verduidelijken:
/* Met de volgende SQL code wordt duidelijk dat SQL Server batch gewijs werkt */
CREATE TABLE #tColumnSwapping (Column1 int, Column2 int)
DECLARE @int int
SET @int = 0

WHILE @int < 1000
BEGIN
  SET @int = @int + 1

  INSERT INTO #tColumnSwapping (Column1, Column2)
  VALUES (@int, 1001 - @int)
END

SELECT * FROM #tColumnSwapping
--===
UPDATE #tColumnSwapping
SET Column1 = Column2
   ,Column2 = Column1

SELECT * FROM #tColumnSwapping
--===
UPDATE #tColumnSwapping
SET Column2 = Column2 + (SELECT MAX(Column2) FROM #tColumnSwapping)

SELECT * FROM #tColumnSwapping
DROP TABLE #tColumnSwapping


STAP (9) DISTINCT Clause
Als er gebruikt gemaakt wordt van Distinct binnen een query worden allen gedupliceerd rijen verwijderd uit de virtuele tabel.

STAP (10) ORDER BY Clause
In de tiende stap worden de kolommen gesorteerd in de virtuele tabel in de volgorde dat ze gespecificeerd zijn. Dit is de eerste en meteen de laatste stap waar een verwijzing naar de alias mag worden gebruikt, die benoemd is in de Select clause. De Order By Clause is in meerdere opzichten bijzonder. Het is ten eerste de enige clause (sinds ANSI SQL 1999) die niet enkel toegang heeft tot laatste geretourneerde virtuele tabel van stap 9, maar ook toegang heeft tot de virtuele tabellen van stap 7 en 8. Hierdoor wordt het mogelijk om sorteringen te laten plaats vinden op kolomen, die je niet in select clause hebt opgenomen. Daarnaast is het belangrijk om te weten als er een Order By clause is toegevoegd aan een query, dat er geen tabel geretourneerd wordt maar een cursor. Doordat er ‘cursor’ wordt geretourneerd treden vele beperkingen op en kan de query niet verder meer gebruikt worden als een tabel expressie, zoals in een view, derived table, sub-query of CTE (nieuw SQL 2005).
NB: Een sortering is voor SQL een duur proces, aangezien alle rijen doorlopen moeten worden doormiddel van een cursor. SQL zal waar het kan indexen gebruiken om de data al gesorteerd op te halen. Gebruik echter nooit een sortering als deze niet nodig is.

Enkele code samples om het te verduidelijken:
-- OrderBy retourneert cursor, geen tabel
SELECT tDerivedTable.*
FROM (SELECT PersoonID, Sorteernaam
      FROM @tPersoon
      ORDER BY PersoonID) AS tDerivedTable
Msg 1033, Level 15, State 1, Line 23
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

STAP (11) TOP Conditie
Als laatste stap wordt de Top optie uitgevoerd. De Top clause hoeft in tegenstelling tot SQL 2000 niet meer een constante te zijn.

NB: De Top clause heeft als bijkomend eigenschap dat de output een tabel retourneert. Hier wordt dankbaar gebruik gemaakt van programmeurs om zo de cursor geretourneerd uit stap 10 weer om te converteren naar een tabel. Deze optie wordt eigenlijk niet ondersteunt en het is dan ook de vraag of code zo toegepast in de toekomstige versies nog zal werken.

Enkele code samples om het te verduidelijken:
--Top optie hoeft geen constante meer te zijn
DECLARE @intTopNr int
SET @intTopNr = 2
SELECT TOP(@intTopNr) *
FROM @tPersoon tP