Het "learning path" van Explore SQL Server 2022 capabilities kun je hier vinden: link.
Op 25 april was de studiedag, we zijn inmiddels 2 juni (ten tijde van typen van dit bericht). Dus een dikke maand na die dag, tijd om zaken af te ronden.
In mijn post van 22 mei had ik module 1 al besproken, ik moet er nu nog 5 die ongeveer een uur per stuk zijn. Als ik dat in mijn "woensdagavond studie-uurtje" ga doen ben ik een dikke maand bezig, daarom maar deze zondagmiddag/-avond "opofferen".
Module 2 - Begrijp hoe het hybride data platform van SQL Server 2022 werkt
In de introductie wordt in het kort uitgelegd wat de hybride mogelijkheden zijn van SQL Server 2022. Het zorgt ervoor dat je ook deels gebruik kunt maken van Azure, dus je hoeft niet "helemaal" over te gaan, mocht je nog twijfelen.
De verschillende termen die daarbij van belang zijn, zijn:
- Azure SQL Managed Instance Link
- Azure Arc-enabled SQL Server
- Azure Synapse Link for SQL Server
- Microsoft Entra authentication
- Microsoft Purview
Bij de uitbreidingen qua security wordt Microsoft Entra genoemd (vroegere Azure Active Directory). Hiermee kun je multi-factor authenticatie afdwingen, accesstokens gebruiken.
Met Purview kun je toegangs-policies beheren. Dit bevat Data en DevOps types, waarmee Microsoft Entra accounts in kunnen loggen in SQL Server, data kunnen lezen of specifieke taken kunnen uitvoeren.
Filmpje met uitleg (duurt 15 minuten).
Het eerste item is disaster recovery. De boel gaat down en je gaat bijna "instant" over naar een online failover omgeving, via de Managed Instance link.
Het volgende item is Synapse Link om data bijna real-time te monitoren.
Een mogelijke reden om een versie in de cloud te plaatsen is:
- een read-only omgeving voor rapportage-doeleinden. Geen extra belasting op je productie database.
- bepaalde workloads online laten uitvoeren om je productie database te ontlasten.
- geautomatiseerde back-ups, minder administratieve handelingen nodig.
- continuiteit van je bedrijf. als je bedrijf zonder stroom komt te zitten en je database-server ook, kan de handel "gewoon" door gaan.
Je kunt zaken activeren via SSMS of scripts. Met scripts kun je zaken automatiseren en dat heeft (dus) de voorkeur.
Alle mogelijkheden (en beperkingen) kun je hier nalezen.
Azure Arc-enabled SQL Server is een versie waarbij ook de online variant in de cloud ondersteund wordt.
Met pay-as-you-go heb je direct inzicht in je verbruik. Hier na te lezen.
Met Best Practise Assessment kun je tips en trucks krijgen om je SQL Server instanties beter in te richten.
Zo'n assessment kan een paar minuten duren, maar ook een uur en heeft 5% tot 10% van de CPU nodig.
Je kunt nu Azure AD gebruiken (Microsoft Entra).
Voor security heb je Microsoft Defender for Cloud.
Informatie over hoe je zaken in moet richten kun je hier nalezen.
Om Azure Arc SQL Server te gebruiken moet je deze configureren, de eerste keer heb je scripts nodig om de Azure extensie op de host-machine te installeren. Ook moet je een netwerkverbinding hebben zodat er verbinding gemaakt kan worden met Azure en je doel SQL Server machine.
Voordat je start moet je eerst wat zaken regelen:
- virtuele of fysieke machine met SQL Server 2022
- een gebruiker of service principal die de volgende rechten nodig heeft:
- Azure Connected Machine Onboarding role
- Microsoft.AzureArcData/register/action
- Microsoft.HybridCompute/machines/extensions/read
- Microsoft.HybridCompute/machines/extensions/write
- je hebt PowerShell nodig
- en je moet Microsoft.AzureArcData and Microsoft.HybridCompute resource providers registreren
Voor de handmatige versie ga je in de Azure portal naar Azure Arc - SQL Server. Klik daar op +Add. Selecteer Connect Servers onder Connect SQL Server to Azure Arc.
Doorloop die stappen, kies Run Script en download het scriptbestand. Voer dat script vervolgens uit op de host-machine.
En voor meer informatie kun je de documentatie nalezen.
Dan zijn er nog opties voor deployment. Ook daar zijn weer keuzes, interactief of schaalbare methodes. Je hebt daarvoor de Azure Connected Machine agent nodig samen met de Azure extensie voor SQL Server.
Interactief:
- verbind op een server, nog geen Azure Arc geïnstalleerd: uitleg
- verbind op een server, bevat reeds Azure Arc: uitleg
- verbind via een installer: uitleg
Schaalbaar:
- gebruik de automatische Arc-geactiveerde SQL Server registratiemethode: uitleg
- gebruik een script: uitleg
- gebruik de configurationmanager custom task sequence: uitleg
Als je alle huidige features en mogelijkheden wilt weten, bekijk dan de online documentatie.
Synapse Link, een cloud-gebaseerde oplossing voor Enterprise Data Warehousing (EDW).
Hiermee kun je data repliceren naar dedicated SQL pools.
Je hebt de mogelijkheid om data-replicatie te pauzeren en later te hervatten.
Als de link gestopt wordt, wordt auotmatisch de landingzone verwijderd.
Tevens staat op deze pagina nog een oefening die je zou kunnen doen.
Microsoft Entra
Er bestaat de mogelijkheid om dit te gebruiken. Wel lees ik iets over de Azure extensie die data ophaalt uit Azure en het in het Windows register opslaat.
Het lijkt erop dat dit alleen nodig is om aan te geven hoe er verbinding gemaakt moet worden en worden hier geen andere zaken opgeslagen.
Op deze pagina staat nog een oefening die je met Microsoft Entra kunt doen.
Purview maakt het leven een stuk makkelijker. Hiermee hoef je gebruikers niet meer stuk-voor-stuk rechten op een inviduele databaseserver te geven, maar kan dat via data-access policies.
Die access policies bevatten 2 verschillende types:
Data - een Microsoft Entra account kan inloggen en kan data lezen uit tabellen in elke database van die instantie, public preview.
DevOps - een Microsoft Entra account kan inloggen en specifieke acties doen, zoals Performance Monitoring, Security Auditing.
In de online documentatie kun je meer over Purview lezen.
De vragen die gesteld worden, daar moet ik toch even over nadenken.
Want wat is een beperking van Azure SQL Managed Instance link feature?
Als ik de antwoorden lees lijkt me het de "in memory OLTP objecten".
Hoeveel databases kunnen in een single availability group voor een instance link gezet worden?
Ik verwacht dat dit maar 1 is.
En dan de vraag over hoe Purview toegang tot SQL Server kan geven.
Login maken en koppelen aan policy lijkt me onmogelijk, een rol die gekoppeld zit aan een policy lijkt me het juiste antwoord.
Alles goed, behalve die van Purview. Dat is een policy in Purview maken gebaseerd op een Microsoft Entra account.
Module 3 - Begrijp de ingebouwde query-intelligentie uibreidingen in SQL Server 2022
Met Intelligent Query Processing zijn nieuwe uitbreidingen toegevoegd aan SQL Server 2022.
In 2017 en 2019 zijn er zal zaken bij gekomen.
Het eerste item wat besproken wordt is memory grant feedback.
Voor 2022 was dit gebaseerd op de meest recente uitvoeren van de query.
Voor 2022 werd memory grant feedback opgeslagen in cache in geheugen. Als dat op 1 of andere manier vrijgegeven werd, moest het later opnieuw opgebouwd worden.
Als je in SQL Server 2022 de Query Store actief maakt, dan wordt de data daarin opgeslagen. Via de sys.query_store_plan_feedback kun je zien wat er gebeurt.
Het toewijzen van geheugen is belangrijk. Bij het toewijzen van teveel geheugen, hou je minder geheugen beschikbaar voor andere zaken. En bij het toewijzen van te weinig geheugen moet er "on the fly" geheugen bij gepakt worden.
We zien een voorbeeld, bij de eerste actie duurde het 88 seconden.
DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;
Als de memory grant feedback actief is, wordt de volgende keer deze actie binnen 1 seconde uitgevoerd.
Als je meer dan 2x beschikbaar hebt gekregen van wat nodig is, wordt het cache-plan van het memory grant plan opnieuw doorgerekend.
Als je memory grant minder dan 1 MB is, dan wordt dat niet gedaan.
Om feeling te krijgen met deze nieuwe mogelijkheid kun je de oefening doen. Via deze Github-repo kun je de code/oefening ook bekijken.
Parameter Sensitive Plan optimalisatie
In de oude versie had je maar 1 plan voor een query en dat kon soms wel eens niet het juiste plan zijn.
Met een dispatcher expression en dispatcher plan wordt het nu wat slimmer aangepakt.
Ik lees iets over een "top 3" die wordt berekend, ook om te zorgen dat de Query Store niet volloopt met de verschillende alternatieven.
Via de online documentatie kun je hier meer over lezen.
En ook hier kun je een oefening doen. Via deze Github-repo kun je de code/oefening ook bekijken.
Bij deze oefening wordt ook gebruik gemaakt van een tool, Replay Markup Language (RML) Utilities for SQL Server.
Degree of Parallelism
Sommige query's worden parallel uitgevoerd. Elk op een eigen thread. De hoeveelheid threads, dat word de Degree of Parallelism (DP) genoemd.
In SQL Server 2022 heb je DOP feedback. Hiermee wordt gekeken of qua eindresultaat er meer of minder zaken parallel uitgevoerd moeten worden.
Om DOP feedback te gebruiken moet de Query Store actief zijn, database compatibiliteit level 160 en DOP_FEEDBACK moet actief zijn.
Als je meer informatie over DOP feedback wilt, dan kun je dat hier online nalezen.
Je kunt hier een oefening doen met DOP. Ook weer via Github op te vragen.
Module 4 - Ontdek nieuwe mogelijkheden in beveiliging, schaalbaarheid en beschikbaarheid in SQL Server 2022
De nieuwe features in SQL Server 2022.
Security:
- Ledger voor SQL Server 2022 (??)
- Always encrypted uitbreidingen
- Stringente connectie encryptie (TDS 8.0 en TLS 1.3)
- PFX certificaat ondersteuning
- Nieuwe vaste server-rollen
- Uitbreidingen voor het dynamisch maskeren van data (persoonsgegevens e.d.)
Als ik naar translate ga, zegt ie dat ledger staat voor "grootboek". Ik zag de connectie niet helemaal, maar toen zag ik bij de omschrijving dat het ging om het feit "dat er niet met je data geknoeid is". Dus daar komt een bepaalde administratie om heen die daarvoor zorgt. En ja, dan zie ik de connectie met grootboeken wel, de belasting is ook blij dat je administratie klopt en alle posten in je grootboek kloppen.
Schaalbaarheid:
- verbeteringen met betrekking tot de performance van buffer pool scan operaties op machines met veel geheugen
- uitbreidingen op systeempagina's, waarmee concurrent acties uitgevoerd kunnen worden, de tempdb met zware workloads heeft daar veel voordeel van
Beschikbaarheid:
- contained availability groepen en de mogelijkheid om server-level objecten tussen replica's te synchroniseren
- ingebouwde ondersteuning voor snapshot backups, ALTER DATABASE SUSPEND_FOR_SNAPSHOT_BACKUP
- met Intel QuickAssist Technology de compressie voor backups en integrale offloading verbeteren
Er volgt nog meer informatie over Ledger. Het gaat er inderdaad om dat je kunt aantonen dat de data ongewijzigd is. Ledger is een soort blockchain.
Er zijn 2 soorten ledgers, 1 die een append-only variant is, dus je kunt alleen toevoegen en ook nog een updatable versie, daar kun je wel rijen aanpassen en verwijderen.
Bij die updatable versie blijft natuurlijk wel de oude versie bewaard, er is een historie-tabel waar deze regels in komen.
Hier kun je meer over updatable ledgers lezen.
Over de insert-only ledger kun je hier meer nalezen.
De database digest, dit is de hash van het laatste blok. Het is een weergave van de staat van alle tabellen in de database.
Die hash moet wel "buiten" de database op een veilige plek opgeslagen worden.
Meer over het beheer kun je hier nalezen.
Er wordt aangegeven dat ik zaken kan aanpassen op een database. En dit niet onmiddels geconstateerd hoeft te worden.
Pas na een verificatie zal geconstateerd worden dat de data niet meer klopt.
Je kunt hier meer lezen over database verificatie.
Vervolgens kun je een oefening doen om te kijken hoe het werkt.
Je ziet bijvoorbeeld dit:
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
...
)
WITH
(
SYSTEM_VERSIONING = ON,
LEDGER = ON
);
GO
De historie wordt bekeken via:
SELECT * FROM dbo.Employees_Ledger;
GO
Om een digest te genereren wordt dit uitgevoerd:
EXEC sp_generate_database_ledger_digest;
GO
En blokken kun je zo bekijken:
SELECT * FROM sys.database_ledger_blocks;
GO
Nieuwe rollen
Zo zijn er nieuwe rollen, bijvoorbeeld ##MS_ServerPerformanceStateReader##
Deze rol kan de systeem-metrieken zien, maar meer niet.
Als je meer over de nieuwe rollen wilt weten, dan kun je dat hier nalezen.
Stricte encryptie
Door in je connectiestring Encrypt=strict op te nemen maak je dit actief. TLS 1.3, TDS login is volledig beveiligd.
Ook moet je zelf een certificaat aanleveren voor encryptie in plaats van te vertrouwen op de optieTrustServerCertificate
Hiermee voorkom je man-in-the-middle aanvallen.
Je kunt hier meer over TDS 8.0 en TLS 1.3 lezen.
Maskeren van data
Dit zat vroeger op het niveau van kolommen.
In 2022 kan dat anders, je kunt op basis van database-level, schema-level, table-level of column-level rechten toekennen op een UNMASK (dus toestaan of weigeren).
Voor de volledige lijst met security-zaken kun je hier kijken.
Verbeteringen met betrekking tot schaalbaarheid
De buffer pool is waar data staat die nodig is voor query's. In 2019 (en daarvoor) ging dat serieel. Zaken zijn onderverdeeld in 8-KB pages.
In het voorbeeld wordt genoemd dat voor 1TB bufferspace je 130 miljoen buffers moet scannen.
Nu dat parallel kan, heb je daar een stuk performance-verbetering.
Ook zijn er verbeteringen met betrekking tot de tempdb doorgevoerd.
- Concurrent global allocation maps (GAM) updates
- Concurrent shared global allocation maps (SGAM) updates
tempdb bevat veel zaken die dynamisch zijn (tijdelijke tabellen, alle sessies), maar ook als er niet voldoende geheugen is, worden zaken in tempdb gezet.
En als de machines groter worden, er meer processen draaien loop je tegen concurrency-zaken aan.
Object allocation contention
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention
Metadata contention
https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database#memory-optimized-tempdb-metadata
Temporary table cache contention
https://techcommunity.microsoft.com/t5/sql-server-blog/tempdb-files-and-trace-flags-and-updates-oh-my/ba-p/385937
Als er problemen optreden, controleer dan de PAGELATCH wachttijden.
We zien vervolgens een video met een performance demo.
Als je er meer over wilt weten, hier staat de online documentatie.
Uitbreidingen voor beschikbaarheid
Meer informatie is hier na te lezen.
Contained availability groups
In het verleden kon je alleen een failover van user databases doen.
Hierbij neem je ook de systeemdatabases mee.
Een contained availability group is een always on availability group welke het volgende ondersteunt:
- beheer van meta-data (gebruikers, logins, SQL agent jobs
- systeemdatabases
Module 5 - Introductie in data virtualisatie in SQL Server 2022
Deze module gaat over data virtualisatie. Dat houdt in dat data geïntegreerd wordt tijdens het query-en, zonder de originele data te kopiëren of te verplaatsen.
We zien een afbeelding waarbij in het blok "Polybase Services" de blokken van SQL Server, Oracle, Teradata, Mongo DB, SAP HANA en ODBC staan.
En via een HTTPS REST API tussenlaag kun je gebruik maken van Azure Data Lake Storage en Azure Blob Storage en een blok S3 compatible storage, zoals Dell, HP, AWS, Ceph, Pure Storage, MinIO, Cloudian en Hitachi Vantara.
Polybase op gestart in SQL Server 2016 en met elke editie uitgebreid. In 2019 heb je al een flink aantal mogelijkheden, op deze pagina kun je meer over de (on)mogelijkheden lezen.
Dankzij het kunnen gebruiken van REST API's zijn de mogelijkheden voor SQL Server uitgebreid.
SQL Server 2022 ondersteunt nu ook CSV, Parquet en Delta bestanden in Azure Blob Storage, Azure Data Lake Storage of andere S3 service.
Met nieuwe T-SQL statements kan deze versie van SQL Server nog meer (krachtige) dingen doen: CREATE EXTERNAL TABLE AS SELECT (CETAS), OPENROWSET, CREATE EXTERNAL TABLE (CET) en meer.
Om S3 object storage te gebruiken en er meer over te lezen kun je de online documentatie doorlezen.
Je denkt al gauw aan Amazon zelf bij AWS, maar ook andere S3 diensten zijn compatibel. Hier kun je de lijst met leveranciers zien die voldoen aan die standaard.
En een paar rechtstreekse linkjes naar providers:
- Cloudian HyperStore
- Dell Isildon OneFS
- Dell ECS Community Edition
- Hitachi Hitachi Content Platform (HCP)
- MinIO Multi-Cloud Object Storage
- Pure Storage Pure FlashBlade. De Pure Storage SQL Server Test Drive portal heeft virtuele SQL Server 2022 labs met een volledige ledger data virtualization guided oefening en meer.
Als je polybase gaat gebruiken, heb je te maken met security e.d.
We krijgen een overzicht te zien van begrippen die daarmee te maken hebben.
Master key: een symmetrische sleutel die private keys, certificaten in de database beveiligen. Meer informatie.
Database scoped credential: maak met SQL een credential om met andere databases verbinding te maken. Meer informatie.
Create External Datasource: met dit commando maak je verbinding met een externe databron. Meer informatie.
Create External Table: met dit commando maak je een virtuele tabel die naar een externe tabel verwijst. Meer informatie.
External File Format: met dit commando geef je aan wat de structuur van het externe bestand is. Meer informatie.
Hierna volgt een oefening waarmee je met Polybase en een Parquet file kunt testen.
Voorbeeld van het aanmaken van een database master key:
DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' + QUOTENAME(@randomWord, '''')
EXECUTE sp_executesql @createMasterKey;SELECT * FROM sys.symmetric_keys;
Voorbeeld van het aanmaken van een database scoped credential:
IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = N'PublicCredential')
DROP DATABASE SCOPED CREDENTIAL PublicCredential;
CREATE DATABASE SCOPED CREDENTIAL PublicCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<KEY>'; -- This example doesn't need the SECRET because the data source is public
En het voorbeeld om een externe datasource aan te maken:
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = N'Public_Covid') DROP EXTERNAL DATA SOURCE Public_Covid;
CREATE EXTERNAL DATA SOURCE Public_Covid
WITH (
LOCATION = 'abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest',
CREDENTIAL = [PublicCredential]
);
De volgende oefening is het maken van een externe tabel van een database.
Create External Table as Select (CETAS)
Hiermee kun je onder andere op een redelijk simpele manier data exporteren naar een Parquet bestand wat vervolgens voor rapportage gebruikt kan worden.
Data tiering or offloading
Je kunt hiermee bepaalde "read-only" of archief-data uit je database naar een externe bron zetten, maar toch de data kunnen blijven opvragen.
Om CETAS te gebruiken moet je met sp_configure de waarde ALLOW POLYBASE EXPORT instellen.
Hierna volgt een voorbeeld om zelf een CETAS uit te voeren.
Module 6 - Introductie in T-SQL uitbreidingen in SQL Server 2022
En we gaan voor de laatste module, uitbreidingen op T-SQL in SQL Server 2022, ik ben benieuwd!
In het intro lees ik al over mogelijkheden met JSON, BIT manipulatiefuncties en Approx percentile functies voor statistische berekeningen.
ALTER TABLE ADD CONSTRAINT, bijvoorbeeld het toevoegen van een primary key kan nu onderbroken en daarna hervat worden.
Waarom zou je dat willen? Nou, er zijn soms hele grote tabellen en daarbij kan het aanmaken van zo'n constraint de boel langere tijd "blokkeren".
We krijgen een voorbeeld waarbij de maximum duur 120 minuten is (2 uur).
-- Example 1: Add a PRIMARY KEY constraint with resumable operation and a MAX_DURATION of 120 minutes
ALTER TABLE [AdventureWorks2022].[Sales].[SalesOrderDetail]
ADD CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 120);
Stel, dit proces is nog steeds bezig, maar omdat de werkdag ten einde is en je niet wilt dat het proces back-ups in de avonduren gaat blokkeren, zet je deze op pauze:
-- You might want to check if the actual constraint is already paused or still running and what percentage of the operation is complete
SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations;-- To pause the constraint run the following command
ALTER INDEX ALL ON [AdventureWorks2012].[Sales].[SalesOrderDetail] PAUSE;
En de volgende werkdag zet je 'm weer aan:
ALTER INDEX ALL ON [AdventureWorks2012].[Sales].[SalesOrderDetail] RESUME;
En als je indexen wilt toevoegen, maar dat doe je op een "drukke tabel" en je wilt geen verkopen via de webshop missen, kun je een WAIT_AT_LOW_PRIORITY toevoegen:
-- Example 1: Terminate the index creation after waiting 10 minutes for low priority locks
CREATE NONCLUSTERED INDEX idx_CustomerID_OrderDate ON [AdventureWorks2012].[Sales].[SalesOrderHeader] (CustomerID, OrderDate)
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)));-- Example 2: Terminate blocking transactions after waiting 10 minutes for low priority locks
CREATE NONCLUSTERED INDEX idx_CustomerID_OrderDate ON [AdventureWorks2022].[Sales].[SalesOrderHeader] (CustomerID, OrderDate)
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
Met de WINDOW functie kun je aggregaties versimpelen.
We zien een voorbeeld van producten die op een bepaalde datum verkocht zijn en daar moet de volgende info bij:
- The total sales per product.
- A moving average for the last three sales.
- The sum of sales for each product over the last three orders.
- A running total for both products on that date.
Dat wordt hier als volgt gedaan:
SELECT
SOH.SalesOrderID,
P.Name,
SOD.OrderQty,
SOD.UnitPrice,
SOD.LineTotal,
SUM(SOD.LineTotal) OVER product_sales AS TotalSalesPerProduct,
AVG(SOD.LineTotal) OVER last_three_sales AS LastThreeSalesAverageByProduct,
SUM(SOD.LineTotal) OVER last_three_sales AS LastThreeSalesSumByProduct,
SUM(SOD.LineTotal) OVER running_total AS RunningTotal
FROM [AdventureWorks2012].[Sales].[SalesOrderDetail] SOD
JOIN [AdventureWorks2012].[Sales].[SalesOrderHeader] SOH on SOD.SalesOrderID = SOH.SalesOrderID
JOIN [AdventureWorks2012].[Production].[Product] P ON P.ProductID = SOD.ProductID
WHERE P.Name IN ('Touring-2000 Blue, 50', 'Touring-3000 Blue, 62')
AND SOH.OrderDate = '2014-03-01'
WINDOW
-- Partition by product name window.
product_sales AS (PARTITION BY P.Name),
-- Last 3 sales by Product name order by date and Sales order ID.
last_three_sales AS (PARTITION BY P.Name ORDER BY SOH.OrderDate, SOH.SalesOrderID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
-- The current an all the previous sales window.
running_total AS (ORDER BY SOH.OrderDate, SOH.SalesOrderID ROWS UNBOUNDED PRECEDING)
Ik vermoed dat als ik dit "nu" zou moeten maken er 4 sub-selects zouden komen en dan is dit inderdaad een stuk leesbaarder.
Bij de vragen wordt ook gevraagd wat het voordeel hiervan is en het antwoord is "het voorkomen van duplicate code".
En dat zou waarschijnlijk gedaan worden in die sub-selects.
In SQL Server 2022 heb je nu ook IS [NOT] DISTINCT FROM, iets wat je gaat gebruiken als er NULL waardes in het spel zijn.
Zoals je nu in je code een WHERE isnull(a,0) = isnull(b,0) doet, zou een WHERE a IS NOT DISTINCT FROM b kunnen doen.
In het voorbeeld wordt gevraagd om de orders met korting te geven, dat is waarbij het kortingveld niet 0 is. Je zou verwachten dat NULL ook als 0 beschouwd wordt, maar dat is het in dit geval niet.
Ook die moeten meegenomen worden. Dan is dit een prima manier om het met dit statement te filteren.
Er zijn nieuwe datum- en tijdfuncties toegevoegd.
Met DATETRUNC kun je de starttijd van een deel van een datum opvragen, dus de dag, de week, de maand, het kwartaal of het jaar.
Dus als je de DATETRUNC toepast met de maand op 2023-04-28 14:23:16.635 dan krijg je 2023-04-01 00:00:00.000 terug.
Met DATE_BUCKET kun je wat DATETRUNC doet, maar heg je een extra parameter om het begin van het vorige jaar op te vragen.
Waarschijnlijk zou het huidige jaar DATE_BUCKET(YEAR, 1, datetime) zijn en is het vorige jaar dus DATE_BUCKET(YEAR, 2, datetime).
Hierna krijgen we nog een voorbeeld van FIRST_VALUE en LAST_VALUE. In het voorbeeld willen we van een klant zijn eerste ordernummer en zijn laatste.
In het statement zie je IGNORE NULLS om die te negeren, je kunt ook RESPECT NULLS gebruiken om NULL waardes juist wel mee te nemen.
DECLARE @LastPurchaseDate DATETIME
DECLARE @CustomerID INT = 11711SELECT @LastPurchaseDate = MAX(soh.OrderDate)
FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] soh
JOIN [AdventureWorks2012].Person.Person p ON p.BusinessEntityID = soh.CustomerID
WHERE soh.CustomerID = 11711
GROUP BY CustomerIDSELECT DISTINCT FIRST_VALUE(SalesOrderID) IGNORE NULLS OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstOrderId,
LAST_VALUE(SalesOrderID) IGNORE NULLS OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastOrderId
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
WHERE CustomerID = @CustomerID
AND DATETRUNC(YEAR,@LastPurchaseDate) = DATETRUNC(YEAR,OrderDate)
We krijgen nog even de tip dat je ook eens moet kijken naar GENERATE_SERIES, een functie waarmee je een serie waardes in een bepaalde range, met een bepaalde ophoging kunt opvragen.
Daarna de string-functies. We hadden al de LTRIM, RTRIM en TRIM. Deze functies zijn uitgebreid om optioneel karakters aan mee te geven (verwijder spaties, maar ook puntkomma's).
TRIM is uitgebreid zodat je ook aan kunt geven waar de karakters weg mogen, LEADING, TRAILING of BOTH.
STRING_SPLIT is uitgebreid met een extra optionele parameter. Als je die op 1 zet, krijg je als resultaat niet alleen een tabel met de kolom "value", maar ook een kolom "ordinal" waarin de positie staat waar in de string die waarde staat. Daarmee hoef je, als je extra dingen moet doen, niet zelf grote sub-select query's met CHARINDEX te maken.
Met GREATEST EN LEAST kun je het maximum of minimum van een lijst met expressies opvragen.
Zelf nog even nagezocht, zo kun je dit doen:
SELECT GREATEST('6.62', 3.1415, N'7') AS GreatestVal;
GO-- dit geeft 7.0000 terug.
SELECT GREATEST('Glacier', N'Joshua Tree', 'Mount Rainier') AS GreatestString;
GO-- dit geeft Mount Rainier terug.
En er zijn toevoegingen voor JSON!
Zo heb je JSON_OBJECT, waarmee je waardes als key-value pairs terug komen.
ISJSON valideert of de input valide JSON is (resultaat is 0 of 1).
In SQL Server 2022 is deze uitgebreid om json_type_constraint te ondersteunen, dus controle op VALUE, ARRAY, OBJECT of SCALAR.
Met JSON_PATH_EXISTS kun je valideren of bepaalde eigenschappen in je JSON aanwezig zijn (resultaat is 0 of 1).
Met JSON_ARRAY bouw je jouw eigen JSON array, een stuk makkelijker om je data om te zetten naar JSON array's.
We krijgen een voorbeeld wat je hiermee zou kunnen doen:
-- Query to collect data, run it every 5 minutes
INSERT INTO CollectionDB.dbo.QueryRequestStats
SELECT GETDATE() AS CollectionTime
, @@ServerName AS ServerName
, er.session_id
, JSON_OBJECT ('session_id':er.session_id
, 'status':er.status
, 'command':er.command
, 'performanceCounters':JSON_OBJECT(
'cpu':er.cpu_time
, 'duration':er.total_elapsed_time
, 'reads':er.logical_reads
)
, 'sql_text':JSON_OBJECT (
'text': est.text
, 'starting_offset':er.statement_start_offset
, 'ending_offset':er.statement_end_offset
)
, 'query_plan':eqp.query_plan
) AS QueryStats
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE er.status NOT IN ('sleeping', 'background')
AND session_id > 50
AND session_id != @@SPID-- Query to return the top 10 requests by CPU usage that have a query text for the collection time `2023-03-29 12:54:00.000`
SELECT *
FROM CollectionDB.dbo.QueryRequestStats
WHERE JSON_PATH_EXISTS(QueryStats, '$.sql_text.text') = 1
AND JSON_VALUE(QueryStats, '$.sql_text.text') IS NOT NULL
AND CollectionTime = '2023-03-29 12:54:00.000'
ORDER BY CAST(JSON_VALUE(QueryStats, '$.performanceCounters.cpu') AS INT) DESC
Nieuwe aggregatie-functies, als je hele grote datasets hebt, hoef je soms niet alle data te valideren, maar is een subset voldoende.
APPROX_PERCENTILE_CONT berekent op basis van het percentiel, interpoleren tussen datapunten de waarde die ongeveer het resultaat zou moeten zijn.
APPROX_PERCENTILE_DISC berekent op basis van het percentiel in een dataset de waarde die ongeveer voldoet en geeft het dichstbijzijnde datapunt terug.
Bit-manipulatie. Heb het ooit wel bij mijn opleiding Hogere Informatica gehad, maar daarna nooit weer gebruikt.
En dat is jammer, want je kunt daar hele coole dingen mee doen. Ik weet nog dat het "dam-project", dus je had een dambord en je maakte een soort algoritme waarmee je tegen de computer kon spelen, een bepaald team dat heel slim had gemaakt door "bit-wise" zaken te manipuleren.
In ieder geval, terug naar SQL Server 2022.
GET_BIT geeft de waarde van een specifiek bit in een binaire waarde. Handig als je individuele bits moet kunnen benaderen, zoals in sensor-statussen van IoT systemen.
BIT_COUNT, telt het aantal bits wat de waarde 1 heeft. Ter ondersteuning voor data density en hoeveel sensors problemen hebben in een monitoringsysteem.
LEFT_SHIFT en RIGHT_SHIFT. Voor sensors, maar ook voor data-compressie en netwerkprotocollen.