MS Sql 70-461: Chapter 3

Les 1

Predicates, three-values logic en zoek argumenten

In Sql kent drie clauses om te filteren: WHERE, ON en HAVING.

Een predicate is een logische expressie. Als NULL niet mogelijk is (kolom is NOT NULL) kan een predicate true of false opleveren. Dit noemt men two-valued logic. Als een kolom wel NULL toestaat, dan spreekt men van three-valued logic. Als een WHERE cluase wordt toegepast worden enkel de records geretourneert waarvoor de predicate true is. Als een WHERE geen rekening houdt met NULL dan worden deze rijen ook nooit geretourneert. NULL betekent immers 'geen informatie'.

Op basis van query filters kan Sql bepalen om indexes te gebruiken om de data efficient op te halen zonder een volledige scan van een tabel uit te voeren. Belangrijk is wel dat de predicate in de vorm van een search argument (SARG) moet zijn om efficient indexes toe te kunnen passen.

Een predicate in de vorm kolom operator waarde of waarde operator kolom kan een SARG zijn. Voorbeeld: col1 = 10 of 10 > col1 zijn search arguments. Als manipulaties worden toegepast op de gefilterde kolom dan voorkomt dit in de meeste gevallen dat de predicate een SARG is. Voorbeelden: MAX(col1) > 90 of col - 1 <= @n. Het laatste geval kan beter worden geschreven als col <= @n +1. Dit omdat er dan op de kolom (col) geen manipulatie wordt toegepast.

Combining predicates

Sql kent een bepaalde volgorde voor logische operatoren: NOT gaat voor AND en OR en AND gaat voor OR. Haakjes hebben gaan voor alles.

Neem deze filter predicate: 

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10

Een veelgemaakt denkfout is dat hier van links naar rechts gewerkt wordt. Dit is echter niet het geval omdat elke predicate conceptueel gelijktijdig wordt uitgevoerd. De kans bestaat dus dat er een waarde is voor propertyval die niet gecast kan worden naar int en dan ontstaat er een fout. Een oplossing vor dit probleem is om gebruik te maken van TRY_CAST, welke een NULL teruggeeft als de conversie faalt:

WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10

Filtering Character Data

Bij het filteren op strings moet gelet worden op Unicode characters. Als de gefilterde kolom (col1) bijv. van het type NVARCHAR is en de expressie is col1 = 'Fred', dan vind er door Sql impliciete conversie plaats, wat de performance niet ten goede komt. Het is daarom altijd het beste om de vergelijking te doen met dezelfde datatypes. In voorgaand beter is het dus beter om col1 = N'Fred' te gebruiken.

Met de LIKE predicate kan gezocht worden op een patroon. Wildcards die de LIKE ondersteund zijn:

- %. Voorbeeld:  'D%' - string moet starten met een D

- _. Voorbeed: '_D' - string waar tweede karakter een D is

- []. Voorbeeld: [AC]% - string die begint met A of C, [0-9]% - string die begint met numerieke waarde ,[^0-9]% - string die niet begint met een numerieke waarde.

Als de LIKE begint met een bekende prefix, zoals LIKE 'ABC%' dan zou Sql een index kunnen gebruiken. Als LIKE bgint met een wildcard, dan kan dit niet.

Filtering Date and time data

Als een datum wordt opgegeven als een string in een predicate, wordt deze door Sql geconverteerd naar een datum. Hoe Sql de formatting van de string interpreteert is afhankelijk van de logon. Bij een logon staat namelijk o.a. de default taal opgegeven. Dit is de DATEFORMAT.

Het formaat '20160324' wordt beschouwd als taal onafhankelijk en betekent altijd 24 maart 2016. Het formaat is dus ymd. Een andere manier om tot een generieke datum te komen is door gebruik te maken van CONVERT of PARSE.

Gebruik bij het filteren van datums zoveel mogelijk de onafhankelijke format, omdat op deze manier Sql indexing kan toepassen. Voorbeeld:

Gebruik bij voorkeur 

WHERE orderdate >= '20070201' AND orderdate < '20070301' 

in plaats van 

WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) =2. 

Dit omdat er anders een expressie wordt toegepast om de kolom waarop gefilterd wordt en dit belemmerd weer het toepassen van indexen.

Les 2

Van een query die geen ORDER BY clause heeft kan nooit vastgesteld worden in welke volgorde de rijen worden opgehaald. Dit heeft te maken omdat het een set is en een set is nooit gesorteerd. Om zeker te weten dat een query gesorteerd wordt opgehaald is door de ORDER BY cluase toe te passen. 

Opvallende zaken bij ORDER BY

- ORDER BY kan gebruik maken van een alias welke is opgegeven bij de SELECT clause;

- Als DISTINCT wordt toegepast kan de ORDERBY enkel kolommen bevatten die in de SELECT clause staan. Als DISTINCT niet gebruikt wordt kan gesorteerd worden op kolommen die niet voorkomen in de SELECT clause.

- Bij ORDERBY kan ook gebruik gemaakt worden van nummers. Deze nummers refereren naar de locaties binnen de SELECT list. Bijv. ORDERBY 1, 2. Dit betekent dat op de eerste en tweede kolom wordt gesorteerd welke gedefinieerd is in de SELECT clause.

- Als er gesorteerd wordt op een kolom welke NULL kan bevatten, dan staan de rijen met NULL boven de rijen die wel een waarde hebben. 

Als het niet nodig is om de rijen gesorteerd te tonen, laat dan de ORDER BY achterwege vanwege performance redenen.

Les 3

Filteren met TOP

Met de TOP functie kan een gegeven aantal rijen of percentage van rijen worden opgevraagd. TOP wordt gebruikt in de SELECT clause: SELECT TOP(3) username FROM users. Sql ondersteund ook TOP zonder haakjes, maar dat is vanwege backwords-compatibility ondersteuning. De juiste syntax is met haakjes.
Het is ook mogelijk om een percentage op te geven ipv een nummer: SELECT TOP(1) PERCENT username FROM users. Het getal tussen haakjes is een FLOAT en moet tussen de 0 en 100 liggen. De PERCENT option rond het aantal rijen naar boven af. Stel een een aantal van 830 rijen voor en er wordt gefilterd op 1 procent, het resultaat is dan 8,3. Er worden door Sql 9 rijen geroutourneerd, want er wordt naar boven afgerond.
Door ORDER BY (SELECT NULL) te gebruiken kan aangegeven worden dat de rijen bewust niet worden gesorteerd.
Stel dat een TOP(3) wordt toegepast, maar er zijn nog 2 andere records die hetzelfde zijn als de laatste van de TOP(3). Stel dat deze 2 rijen ook nodig zijn, dan kan dat met de WITH TIES option: SELECT TOP(3) WITH TIES username. In dit geval worden er dan 5 rijen teruggegeven.
Een andere manier om gelijke records te filteren is dmv een tie breaker, bijv degene met het hoogste id wint: SELECT TOP(3) username FROM users ORDER BY username, id DESC.

Filteren met OFFSET-FETCH

OFFSET-FETCH kan net als TOP gebruikt worden om een vast aantal records terug te laten geven en is, in tegenstelling tot TOP, standaard Sql. Tevens heeft OFFSET-FETCH een skip functionaliteit. De OFFSET en FETCH clauses worden na de ORDERBY geschreven. 

ORDER BY date OFFSET 15 FETCH NEXT 25 ROWS ONLY. Deze query slaat de eerste 15 rijen over en geeft de volgende 25 rijen terug. Om geen rijen over te slaan, moet 0 worden ingevuld ipv 15. FETCH FIRST en FETCH NEXT bieden beide dezelfde uitkomst. Ditzelfde geldt voor ROW en ROWS, beide zijn geldig en zijn hetzelfde. 

In T-Sql kan OFFSET-FETCH allee gebruikt worden na een ORDERBY, terwijl dit in standard Sql niet hoeft. In T-Sql heeft FETCH altijd OFFSET nodig, maar OFFSET heeft niet altijd een FETCH nodig:

ORDERBY date OFFSET 15

Deze query geeft alle rijen terug, behalve de eerste 15.