Op 25 april 2024 hadden we de TRES studiedag. Ik had mezelf voorbereid om deze dag met de DP-900 aan de slag te gaan, mocht ik "gezakt" zijn. Maar gelukkig, bij het examen op 23 april had ik 857 punten, ruim boven het minimum van 700!
Een tijdje geleden met de voortgangsgesprekken eens gekeken wat er allemaal qua certificeringen bij Microsoft te doen is. Ik heb al een aantal certificaten gehaald, maar je hebt ook de "schema's/leerplannen" die je kunt volgen. Ik heb gekozen om eerst het "simpele pad" te bewandelen is dat is te zorgen dat ik alle Fundamental-certificeringen "in the pocket heb". De AZ-900 heb ik al, de DP-900 nu dus ook. De AI-900 wordt dus het volgende examen. Daar heb ik al voorbereidingen voor gedaan, mijn collega Jeroen Smink kwam een tijd geleden met de AI-challenge van Microsoft, waarbij je met het doorlezen van de stof en het beantwoorden van de vragen die challenge kon halen. Dus met de kortingscode kan ik binnenkort een examen in gaan plannen.
Maar goed, dat is iets voor een volgend kwartaal, daarom deze dag bekeken of ik mijn punten en niveau binnen Microsoft Learn kan opkrikken.
Bij de start zit ik op niveau 12. "Ergens" heb ik gelezen dat er nooit een eindpunt is, dus geen maximaal niveau, geen maximale punten.
Bij mijn voorbereidingen voor de DP-900 kwam ik de Learn-module "Get Started Querying with Transact-SQL" tegen.
Naast het feit dat ik veel zit te query-en en dacht "dit kan wel eens een easy win zijn", ben ik eerder ook wel bepaalde statements tegengekomen waarvan ik dacht "die ken ik niet" en "dit is misschien wel eens handig om te gebruiken". Dus eens kijken of ik er mijn voordeel mee kan doen! De duur staat op 5 uur en 40 minuten.
Spoiler: inderdaad nogal wat statements die ik niet kende, leerzaam dus!
Samenvatting (zaken die voor mij nieuw of bekend (maar wel interessant) waren):
- De statements TRY_CAST en TRY_CONVERT zijn handig als de kolom ook "ongeldige" data kan bevatten.
- In plaats van een uitgebreid CASE WHEN.. THEN statement, kun je ook COALESCE gebruiken.
- Met WITH TIES kun je zorgen dat records die gelijk zijn aan wat nu in je TOP xx teruggegeven wordt er ook bij komen.
- Wil je valideren met een INNER SELECT of records bestaan, gebruik dan EXISTS en geen COUNT(*) voor betere performance.
- Met RANK, PARTITION, OVER kun je gedailleerd je data sorteren.
- Als een tabel met DELETE geleegd moet worden en je wilt je IDENTITY weer op 1 laten starten, gebruik DBCC CHECKIDENT.
- En heb je een "tellertje" nodig, misbruik niet IDENTITY, maar gebruik SEQUENCE.
Module 1 - introductie
SQL staat voor Structured Query Language en is een standaard in beheer bij de American National Standard Institute (ANSI). Elke leverancier heeft eigen variaties en extensies.
Als je zelf wel eens met Microsoft SQL Server en met mySQL gewerkt hebt zal je dat niet onbekend voorkomen (bijvoorbeeld de ISNULL en IFNULL en de LIMIT ...).
Vaak hoor je ook TSQL, dat is het dialect wat Microsoft gebruikt waarbij je dus ook stored procedures, functies en andere zaken kunt gebruiken.
De verschillende soorten statements kwamen ook bij de DP-900 naar voren:
- Data Manipulation Language (DML), voor query-en en aanpassen van data, dus INSERT, UPDATE, DELETE.
- Data Definition Language (DDL), voor definitie en levensduur van database-objecte, dus CREATE, ALTER, DROP.
- Data Control Language (DCL), voor rechten van gebruikers en objecten, dus GRANT, REVOKE, DENY.
Soms wordt ook TCL genoemd (Transaction Control Language) voor BEGIN TRANSACTION, COMMIT.
DML wordt ook wel Data Modification Language genoemd waardoor SELECT niet meer in het rijtje hoort (die komt dan bij DQL als Data Query Language).
SQL is flexibel, je kunt alles in 1 regel zetten, hoofdletter-"ongevoelig".
Tips zijn:
- maak de statements UPPERCASE, dat maakt inzichtelijk wat er gebeurt.
- bij elk statement, start die op een nieuwe regel.
- als je subquery's e.d. hebt, laat de code inspringen zodat je ziet wat waar bij hoort.
Je hebt veel verschillende datatypes. Je gebruikt een subset, want ik heb ze nog nooit allemaal gebruikt. Tekst, getallen, datums. En in stored procedures de cursor, een tijdelijke table.
Meer uitleg over de verschillende types kun je hier nalezen.
Bij het omzetten/combineren van data gebruik je nog wel eens SELECT CAST(veldnaam AS int) .... zodat je bijvoorbeeld een goede sortering krijgt.
Maar als dat veld ook niet numerieke data bevat, dan gaat de boel kapot. Ik loste dat wel eens op met in de WHERE een ISNUMERIC(veldnaam) = 1 toe te voegen.
Maar mocht je die data wel willen behouden, dan moet het anders.
Ik zie in de documentatie dat je ook een TRY_CAST(veldnaam as type) kunt uitvoeren. De records komen wel in het resultaat, de waarde zal dan NULL zijn.
Zo heb ik ook wel CONVERT(..) gebruikt en nu blijkt dat je dus ook een TRY_CONVERT hebt. Handig!
Eerste opsteker van deze module, want die beide statements kende ik nog niet.
CAST is een ANSI-statement, CONVERT is de TSQL variant (je kunt dus beide gebruiken).
Je kunt PARSE('01/01/2021' AS date) gebruiken om numerieke waardes of datum om te zetten.
Ook hier heb je een TRY_PARSE.
Met STR(..) kun je een numerieke waarde omzetten naar een varchar-waarde.
ISNULL is niet een ANSI standaard (had ik boven ook al genoemd met de verschillen met mySQL, waar je IFNULL hebt).
In de documentatie wordt COALESCE genoemd. De functie is iets flexibeler, omdat je meerdere argumenten mee kunt geven.
Dit zie ik niet zozeer als een vervanging voor ISNULL, maar meer om een keuze uit een veld te maken als je 3 velden aangeeft, waarbij er 2 leeg zijn en 1 gevuld.
Dat klinkt alsmeer als een CASE WHEN ... THEN en ziet deze COALESCE er een stuk leesbaarder uit.
Ook de functie NULLIF kende ik nog niet. Hiermee kun je een NULL waarde terug geven als een veld aan een bepaalde voorwaarde voldoet.
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Aan het einde van de eerste module krijg je nog een aanbieding voor het gratis proberen van Azure SQL Database:
Module 2 - sorteren en filteren
Als je een deel wilt opvragen gebruik je TOP xx, die kende ik al.
Maar je hebt ook nog een SELECT TOP xx WITH TIES ... FROM ... ORDER BY price DESC
Die WITH TIES is handig voor situaties waarbij je een aantal records hebt die dezelfde waardes (in dit geval qua prijs) hebben die binnen de top 10 vallen.
En leverancier A wel in je "normale TOP xx" in de lijst kwam, maar leverancier B "net niet" omdat SQL zelf nog even op naam ging sorteren.
Het andere kan natuurlijk ook, leverancier B kwam wel in de lijst, maar leverancier A niet.
WITH TIES gebruik je dus om niet expliciet dat aantal op te vragen, maar wel om een lijst op te vragen die minimaal dat aantal krijgt, als er zoveel records zijn.
En nog 1 die ik niet kende, SELECT TOP 10 PERCENT ... FROM ... ORDER BY price DESC
Als je niet exact 10 records, maar bijvoorbeeld 10% van je resultaten terug wilt krijgen.
Voor als je paging gebruikt kun je gebruik maken van OFFSET-FETCH.
Dus dat wordt een SELECT .. FROM .. ORDER BY price DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Misschien nog wel eens de moeite waard om te kijken hoe dit in Entity Framework gaat, of iets (meer) mee doen.
Want alle data wat niet over de lijn hoeft te gaan zorgt ervoor dat je app sneller wordt.
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Module 3 - combineer tabellen met JOINs
Dit lijkt niet zo moeilijk. Maar... ik gebruik eigenlijk alleen maar de JOIN (en dat is een impliciete INNER JOIN).
Je hebt meer mogelijkheden.
In de ANSI-specs (SQL-89) werd een JOIN benoemd door de tabellen in de FROM-clause op te nemen, komma-gescheiden.
Ik heb dat zelf wel gezien in statements die werden gemaakt voor Access-databases.
Maar dat is niet de meest "slimme" methode. Mocht je bijvoorbeeld vergeten om een WHERE-clause toe te voegen (kan gebeuren...), dan heb je in eens een resultaatset die ongelovelijk groot kan zijn.
Als in je ene tabel 4.000 records zitten en in de andere 2.500, dan is je resultaatset 10 miljoen records :D
In de ANSI-specs (SQL-92) is JOIN en ON geïntroduceerd.
De uitleg van de INNER JOIN volgt, waarbij de tabellen een match hebben, die kende ik al.
Dan heb je de OUTER JOIN, waarbij je ook waarbij er geen match is in je resultset komt (omdat je alle records van tabel A in je resultaat wilt hebben).
Je kunt OUTER wel weglaten, want je geeft aan "welke tabel" er mee moet, dat doe je met LEFT JOIN, RIGHT JOIN of FULL JOIN.
We gaven al het voorbeeld van het combineren van 2 tabellen, waarbij je 10 miljoen records krijgt.
Soms is dat onbedoeld en met SQL-92 wordt dat redelijk afgeschermd. Maar in sommige gevallen wil je juist WEL de combinatie van alle mogelijkheden.
Daarvoor gebruik je een CROSS JOIN: SELECT ... FROM ... AS t1 CROSS JOIN ... AS t2
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Module 4 - schrijf subquery's in TSQL
Wat hier besproken wordt is redelijk bekende kost. Als je 1 resultaat krijgt kun je een ... WHERE x=(SELECT ...) doen.
En anders een WHERE x IN (SELECT ...).
Ik wist al dat het statement EXISTS(... bestond, de uitleg is wel goed. Mocht je namelijk niets met het resultaat hoeven doen, dan kan in je subquery een SELECT * ... uitgevoerd worden, met EXISTS check je of er uberhaupt een record bestaat. Als je een WHERE (SELECT COUNT(*)...) > 0 doet, moet er al een berekening e.d. op de complete resultset uitgevoerd worden.
Dit kan dus een hele goede performance-fix zijn!
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Module 5 - gebruik ingebouwde functies en GROUP BY
We gaan de verschillende functies langs:
- scalar, het resultaat is een enkele waarde, dus GETDATE(), ROUND(..)
- logisch, IIF(...)
- ranking, rowset, over
- aggregatie (SUM, COUNT, ..)
- group by, het samenvatten van je data
- having, het filteren van je gegroepeerde resultaten
Bij de logische functies wordt CHOOSE genoemd. Hiermee kun je op basis van een nummer een tekstwaarde teruggeven.
Voorbeeld is:
SELECT orderid, status, CHOOSE(status, 'Ordered', 'Shipped', 'Delivered') as orderstatus FROM salesOrder
Bij ranking, rowset wordt het voorbeeld getoond hoe je iets een positie kunt geven op basis van de prijs;
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Met "OVER" kun je data groeperen. Zo zien we het voorbeeld van hoe je zaken kunt sorteren op categorie en daarbinnen weer kunt sorteren op prijs;
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
De opmerking hierbij is dat sommige records dezelfde RANK-waarde hebben en sommige waardes overgeslagen worden.
Hierbij wordt verwezen naar het gebruik van andere functies zoals DENSE_RANK, NTILE en ROW_NUMBER.
Uitleg daarvan kun je op deze pagina vinden.
En met OPENROWET kun je data opvragen uit een andere database. De vraag is of je dat wilt :)
Voorbeeld wat gegeven wordt luidt:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Ook wordt genoemd dat je OPENXML en OPENJSON kunt gebruiken om dat type data te gebruiken.
Voor meer "verdieping" kun je hier de documentatie lezen.
De meeste aggregaties ken ik wel. Wel zie ik dat er ook een COUNT_BIG is om een "big integer" terug te geven. Handig bij hele grote datasets.
NULL wordt nog even benoemd, de SUM werkt omdat NULL waardes genegeerd worden.
Maar bij AVG (gemiddelde) gebeurt dat ook, waarbij die rijen dus ook niet meegeteld worden. En dat kan dus wel eens afwijkende resultaten geven.
Goed om in de gaten te houden, mogelijk wil je er dan toch een ISNULL(..,0) omheen zetten.
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Module 6 - aanpassen van data met T-SQL
Het INSERT statement kende ik al. Maar het voorbeeld toont naast VALUES (waarde uit tabel of "vaste waarde" zoals NULL) ook DEFAULT.
Hiermee zorg je dat de standaard ingestelde waarde op die kolom ingevoerd wordt. Als je de kolomnaam niet mee neemt in de INSERT wordt dat al standaard uitgevoerd.
Dus waarschijnlijk zul je alleen de DEFAULT gebruiken als je expliciet kolommen in de INSERT wilt plaatsen of als je mogelijk zelf CASE WHEN... gaat plaatsen in de SELECT.
Met een SELECT ... INTO kun je zorgen dat de waardes in een nieuwe tabel aangemaakt worden. Dit statement kwam ik ook tegen bij de DP-900 studie.
IDENTITY is voor gegenereerde waardes. Met SCOPE_IDENTITY() vraag je de meest recente ID aan in je huidige scope en sessie.
Als je niet zeker weet of dat de juiste waarde is (omdat je met triggers misschien ook acties uitvoert) kun je met IDENT_CURRENT('tabel') de laatste ID waarde van een tabel opvragen (let op, als iemand anders een record toevoegt, krijg je de ID terug van dat record)!
En soms wil je zelf records met waardes invoeren. Daarvoor gebruik je de:
SET IDENTITY_INSERT tabelnaam ON;
INSERT INTO tabelnaam (...)SET IDENTITY_INSERT tabelnaam OFF;
En als je de teller voor de automatische waardes terug wilt zetten of bepaalde ID's wilt overslaan, daarvoor gebruik je de functie DBCC CHECKIDENT.
Handig als je niet een TRUNCATE op een tabel kunt uitvoeren omdat er foreign keys aan gekoppeld zijn en je genoodzaakt bent om een DELETE uit te voeren, het is dan wel netjes om je auto-incrementveld weer op 1 te laten beginnen. In de documentatie kun je hier meer over lezen.
En hier kom ik nog iets tegen wat ik niet kende. Je werkt eigenlijk alleen met die IDENTITY.
Maar soms heb je een ander "oplopend getal" nodig, daarvoor kun je een SEQUENCE gebruiken.
Het voorbeeld wat gegeven wordt is:
CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);
SEQUENCE gebruik je om een veld te delen over meerdere kolommen en/of meerdere tabellen.
Met SEQUENCE kun je sorteren op een andere kolom. Voorbeeld wat gegeven wordt:
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
ProductID,
Name
FROM Production.Product;
Als je meerdere nummers nodig hebt kun je met IDENTITY "gaten" krijgen.
Het voorbeeld wordt gegeven om de stored procedure sp_sequence_get_range te gebruiken.
Het MERGE statement heb ik volgens mij wel eens in BULK-statements gebruikt.
Je gebruikt vaak een INSERT of UPDATE, maar met MERGE INTO ... kun je zorgen dat:
- bestaande data bijgewerkt wordt
- nieuwe records aangemaakt worden
- niet matchende records verwijderd worden
Voorbeeld wat hierbij gegeven wordt:
MERGE INTO schema_name.table_name AS TargetTbl
USING (SELECT <select_list>) AS SourceTbl
ON (TargetTbl.col1 = SourceTbl.col1)
WHEN MATCHED THEN
UPDATE SET TargetTbl.col2 = SourceTbl.col2
WHEN NOT MATCHED [BY TARGET] THEN
INSERT (<column_list>)
VALUES (<value_list>)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Mocht je zelf met de oefening aan de slag willen gaan:
Setup instructies
Uitleg oefening
Een groot deel van de statements en het gebruik kende ik al, maar ik kwam hier (zie ook de samenvatting bovenaan) toch zaken tegen die ik niet kende. En dus ook niet gebruik(te). En misschien later toch nog wel eens nodig ga hebben. Handig dat ik dan op mijn eigen blog dit makkelijk terug kan zoeken ;)
In ieder geval, een goed bestede en nuttige studiedag!