T-SQL Cheatsheet

Johan Beysen — AP Hogeschool

Inhoudstafel

Setup & Beheer

Planning & Installatie
SQL Server: Processen & Databases
Database Snapshots
Contained Databases
Logins, Users & Rechten
Server Roles
Service Accounts — sMSA / gMSA
Backup & Restore

SQL Taal

Tips
Uitvoervolgorde SELECT
Datatypes
Constraints
DDL — Data Definition Language
DML — Data Manipulation Language
DQL — SELECT
Aggregatiefuncties
Stringfuncties & Opmaak
Datumfuncties
Subqueries
Relaties

Database Objecten

Views, Stored Procedures & Triggers

Data, Performance & Continuïteit

Import & Export
Performance & Indexing
Automatisatie — SQL Server Agent
Maintenance Plans
Migratie & Scalability
Reporting Services (SSRS)
Troubleshooting

Planning & Installatie

Referentie-architectuur (lab)

Domain Controller (AD DS + DNS)
        |
    labnetwerk
        |
  +-----+------------------+
  |                         |
SQL Server              Windows Client
(member server)         (SSMS / beheer)

Rol	Functie
Domain Controller	Active Directory, DNS
SQL Server	Member server met de database-engine
Windows Client	Beheerwerkstation met SSMS

DNS eerst

SQL Server en client moeten de domain controller als DNS-server gebruiken, anders volgen vreemde domein- en authenticatieproblemen.

Opslag logisch scheiden

Volume	Gebruik
`C:`	OS, SQL Server binaries
`D:`	Databestanden (`.mdf`, `.ndf`)
`L:`	Logbestanden (`.ldf`)
`B:`	Backups

Service accounts

Laat SQL Server services niet draaien onder een gewone gebruikersaccount — gebruik een gMSA.

Installatievolgorde

Labnetwerk aanmaken
Domain Controller installeren
Statisch IP en DNS configureren
Domein aanmaken
SQL member server installeren en in het domein zetten
Service accounts aanmaken
SQL Server installeren
Data-, log- en backuplocaties configureren
SSMS op de client installeren
DNS, domeinlogin en SQL-connectiviteit testen

Vastleggen vóór installatie

Hostname, IP-adres, DNS-server
Default of named instance?
Welke accounts draaien de SQL services?
Locatie datafiles, logfiles, backups
Authentication model: Windows-only of mixed mode?
SQL Server Agent automatisch mee starten?
Firewallregels voor beheer vanaf de client
Lab, staging of productieachtig?

Basiscontrole na installatie

hostname
ipconfig /all
nltest /dsgetdc:lab.local

Controleer in SSMS: connectie op hostname (niet enkel IP), Windows-authenticatie, status van SQL Server + SQL Server Agent, standaardlocaties, en de aanwezigheid van master, model, msdb, tempdb.

Valkuilen

Probleem	Waarschijnlijke oorzaak
Server kan niet joinen met domein	Verkeerde DNS-server ingesteld
SSMS connecteert alleen via IP	Naamresolutie/DNS niet correct
Windows-authenticatie faalt	Client geen domeincontext of verkeerde credentials
SQL Server Agent start niet	Service account, rechten of editie nakijken
Backups falen	SQL service account heeft geen schrijfrechten op backupmap
Trage/vreemde databasegroei	Autogrowth en initiële grootte niet bewust ingesteld

Installatie op Windows Server Core (silent)

Op Core is een GUI niet beschikbaar — installatie verplicht via /qs of /q.

C:\SQL2022\Developer_ENU\setup.exe `
    /qs `
    /ACTION=Install `
    /FEATURES=SQLEngine `
    /INSTANCENAME=MSSQLSERVER `
    /SQLSVCACCOUNT="DOMEIN\svc-sql$" `
    /SQLSYSADMINACCOUNTS="DOMEIN\Domain Admins" `
    /INSTANCEDIR="C:\Program Files\Microsoft SQL Server" `
    /SQLUSERDBDIR="D:\data" `
    /SQLUSERDBLOGDIR="D:\log" `
    /SQLBACKUPDIR="D:\backup" `
    /IACCEPTSQLSERVERLICENSETERMS

Important

Bij een gMSA geen /SQLSVCPASSWORD meegeven — AD beheert het wachtwoord zelf.

# Installatie verifiëren
Get-Service MSSQLSERVER

Mixed mode + sa activeren (na silent install)

Mixed mode staat na een silent install standaard niet aan — instellen via single-user mode.

# 1. Service stoppen
Stop-Service MSSQLSERVER -Force

# 2. Single-user mode starten (apart venster)
& "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m -s MSSQLSERVER

# 3. Mixed mode + sa inschakelen (2e venster)
& "...\sqlcmd.exe" -S "." -E -Q "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2; ALTER LOGIN sa ENABLE; ALTER LOGIN sa WITH PASSWORD = 'JOUWWACHTWOORD'"

# 4. Verifiëren — resultaat 0 = mixed mode actief
& "...\sqlcmd.exe" -S "." -E -Q "SELECT CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS INT)"

# 5. Ctrl+C in venster 1, dan normaal starten
Start-Service MSSQLSERVER

Firewall & DNS

# Remote toegang toelaten (poort 1433)
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# DNS-record forceren na domain join
ipconfig /registerdns
ipconfig /flushdns

Basisregel

Eerst plannen, dan installeren. Slechte keuzes rond accounts, opslag en naamgeving komen later terug bij rechtenbeheer, backups en troubleshooting.

SQL Server: Processen & Databases

Services

Service	Beschrijving
SQL Server (MSSQLSERVER)	Hoofdservice — databankengine
SQL Server Agent (MSSQLSERVER)	Jobbeheer en alerts
SQL Server Browser	Luistert naar verbindingsverzoeken
SQL Server CEIP service	Telemetrie
SQL Server VSS Writer	Volume Shadow Copy support

In productie

Stoppen of herstarten verbreekt alle actieve verbindingen.

# Status opvragen
Get-Service MSSQLSERVER
Get-Service *SQL*                                        # alle SQL-gerelateerde services
Get-Service -Name SQLSERVERAGENT | Select-Object Name, Status, StartType

# Starten / stoppen / herstarten
Start-Service   MSSQLSERVER
Stop-Service    MSSQLSERVER -Force
Restart-Service MSSQLSERVER -Force

Start-Service   SQLSERVERAGENT
Stop-Service    SQLSERVERAGENT -Force
Restart-Service SQLSERVERAGENT -Force

Named instance

Bij een named instance heet de service MSSQL$InstanceName (engine) resp. SQLAgent$InstanceName (agent) — het $-teken moet in PowerShell escaped worden met een backtick:

Restart-Service MSSQL`$INSTANCE01
Restart-Service SQLAgent`$INSTANCE01

:: Alternatief via net (ook bruikbaar in een gewone cmd, zonder PowerShell)
net start MSSQLSERVER
net stop  MSSQLSERVER
net start SQLSERVERAGENT
net stop  SQLSERVERAGENT

-- Status SQL Server Agent opvragen via T-SQL
DECLARE @agent NVARCHAR(512);
SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServerAgent');
EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

System databases

Database	Functie
`master`	System-level informatie van de instantie
`msdb`	Gebruikt door SQL Server Agent (jobs, alerts)
`model`	Template voor elke nieuwe database
`Resource` (verborgen)	Read-only systeem-objecten (via `sys` schema)
`tempdb`	Werkruimte voor tijdelijke objecten — herbouwd bij elke herstart

-- Overzicht bestaande databases
SELECT name, database_id, create_date FROM sys.databases;

Nieuwe database plannen

Minimaal nodig: databasenaam, eigenaar, ≥1 databestand (.mdf/.ndf), ≥1 logbestand (.ldf), ≥1 filegroup, opties (collation, recovery model, ...).

DB.mdf      → Raid 1      (primair databestand)
DB.ldf      → Raid 10     (transactielogboek — aparte fysieke schijf!)
Sales1.ndf  → Raid 5      (extra databestanden)
TempDB      → Raid 5/10

Database aanmaken

-- Simpel — gebruikt standaardconfiguratie
CREATE DATABASE sample;
GO

-- Uitgebreid, met filegroups en extra datafile
CREATE DATABASE voorbeelddb
ON PRIMARY
(
    name       = voorbeelddb_dat,
    filename   = 'D:\data\voorbeelddb.mdf',
    size       = 10MB,
    maxsize    = 500MB,
    filegrowth = 10%
),
(
    name       = voorbeelddb_dat2,          -- extra datafile (.ndf)
    filename   = 'D:\data\voorbeelddb2.ndf',
    size       = 10MB,
    maxsize    = UNLIMITED,
    filegrowth = 50MB
)
LOG ON
(
    name       = voorbeelddb_log,
    filename   = 'D:\log\voorbeelddb.ldf',
    size       = 5MB,
    maxsize    = 100MB,
    filegrowth = 500KB
);
GO

Database wijzigen

-- Bestand toevoegen
ALTER DATABASE projects
ADD FILE (name = projects_dat1, filename = 'C:\projects1.ndf', size = 10, maxsize = 100, filegrowth = 5);

-- Single-user mode (onderhoud)
ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Read-only / multi-user
ALTER DATABASE AdventureWorks2012 SET READ_ONLY;
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;

Database verwijderen

USE master;
DROP DATABASE projects;

Onherroepelijk

Zorg altijd voor een recente backup vóór je een database verwijdert.

Recovery modellen

Bepaalt hoe SQL Server het transactielogboek bijhoudt, en dus wat mogelijk is bij herstel.

Model	Loggedrag	Point-in-time herstel	Log backup verplicht
Simple	Log wordt automatisch afgekapt na checkpoint	Nee — enkel tot laatste full/differential	Nee
Full	Volledig bijgehouden tot expliciete log backup	Ja, tot op de seconde	Ja
Bulk-Logged	Bulk-operaties minimaal gelogd	Beperkt — niet tijdens bulk-periode	Ja

Simple → testomgeving, dataverlies van uren aanvaardbaar, geen log backups willen beheren
Full → productie, point-in-time herstel vereist, gestructureerd backupschema
Bulk-Logged → tijdelijk vóór grote bulk-load, nadien log backup + terug naar Full

Log backup vergeten = log loopt vol

Zonder regelmatige log backups blijft het .ldf-bestand groeien tot de schijf vol is. Zet een SQL Server Agent job voor automatische log backups.

-- Opvragen / instellen
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MijnDatabase';
ALTER DATABASE MijnDatabase SET RECOVERY FULL;  -- of SIMPLE / BULK_LOGGED

Productiekritisch?
├── Nee → SIMPLE
└── Ja
    ├── Grote bulk-operaties gepland? → tijdelijk BULK_LOGGED, dan terug FULL
    └── Point-in-time herstel vereist? → FULL (+ regelmatige log backups)

Database Snapshots

Read-only, statisch beeld van een database op één moment — geen apart herstelproces, enkel lezen.

Bevat alleen gewijzigde pagina's (copy-on-write); ongewijzigde data wordt van de brondatabase gelezen
Nuttig voor rapportage, testen of snel terugdraaien zonder restore
Brondatabase moet online blijven zolang de snapshot bestaat

-- Snapshot aanmaken
CREATE DATABASE ClassicModels_snap
ON (
    NAME = ClassicModels_dat,
    FILENAME = 'D:\Snapshots\ClassicModels_snap.ss'
)
AS SNAPSHOT OF ClassicModels;

-- Database terugzetten naar het moment van de snapshot
RESTORE DATABASE ClassicModels FROM DATABASE_SNAPSHOT = 'ClassicModels_snap';

-- Snapshot verwijderen
DROP DATABASE ClassicModels_snap;

Beperkingen

Snapshots zijn geen back-up — ze vallen weg zodra de brondatabase crasht. Ondersteunt geen full-text catalogi en vereist dat de brondatabase in ONLINE staat is.

Contained Databases

Een contained database slaat authenticatiegegevens intern op — logins hoeven niet op instantieniveau te bestaan.

Type	Beschrijving
`NONE`	Standaard — volledig afhankelijk van de SQL Server instantie
`PARTIAL`	Gebruikers en wachtwoorden in de database opgeslagen

-- Contained databases toelaten (eenmalig op serverniveau)
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

-- Database instellen als PARTIAL
ALTER DATABASE ClassicModels SET CONTAINMENT = PARTIAL;

-- Contained user aanmaken (geen instantie-login nodig)
USE ClassicModels;
CREATE USER [localuser] WITH PASSWORD = 'P@ssw0rd!';

-- Containment-status opvragen
SELECT name, containment_desc FROM sys.databases;

Wanneer gebruiken?

Handig bij migraties of multi-tenant applicaties waar logins niet centraal beheerd mogen worden. Nadeel: wachtwoorden vallen buiten de instantie-beveiligingspolicies.

Logins, Users & Rechten

	Login	User
Niveau	SQL Server engine (server)	Database
Opgeslagen in	`master`	User database
Geeft toegang tot	De SQL Server instantie	Één specifieke database
Rechten	Server roles	Databaserechten (lezen, schrijven, uitvoeren, ...)

Eén login kan gekoppeld zijn aan meerdere users (één per database) en aan één of meer server roles.

Types

Login type	User type
Windows login (user of group account)	Windows user
SQL Server login (wachtwoord door SQL Server beheerd)	SQL user with password
Certificate / asymmetric key login	SQL user with login — kan naar meerdere databases
	SQL user without login — geen connectie, kan wel objecten bezitten

-- Windows login — user account
CREATE LOGIN [DataJBE\bbibber] FROM WINDOWS WITH DEFAULT_DATABASE = [master];

-- Windows login — group
CREATE LOGIN [DataJBE\Developers] FROM WINDOWS WITH DEFAULT_DATABASE = [AdventureWorks2019];

-- SQL Server login
CREATE LOGIN [JuniorDBA]
WITH PASSWORD = '-p@ssw0rd-' MUST_CHANGE,
     CHECK_EXPIRATION = ON,
     CHECK_POLICY = ON,
     DEFAULT_DATABASE = [ClassicModels];

Naamgevingsrichtlijnen

Windows login (user): altijd DOMEIN\gebruikersnaam — bv. DataJBE\jbeysen
Windows login (groep): DOMEIN\Groepsnaam — bv. DataJBE\SQL_Admins
SQL Server login: functie-/persoonsnaam, geen sa of admin, geen spaties
Serviceaccounts: dedicated account per applicatie, niet gedeeld
sa altijd uitschakelen in productie, indien mogelijk hernoemen

-- Login wijzigen
ALTER LOGIN [training\SeniorDeveloper] DISABLE;

User aanmaken

USE [ClassicModels];
CREATE USER [Marcel] FOR LOGIN [MarcelKiekeboe];
ALTER ROLE db_datareader ADD MEMBER [Marcel];

Server Roles

Rollen op instantieniveau — bepalen wat een login op de SQL Server zelf mag doen.

Role	Rechten
`sysadmin`	Volledige controle over de SQL Server instantie
`serveradmin`	Serverconfiguratie wijzigen en de instantie afsluiten
`securityadmin`	Logins beheren, GRANT/DENY op serverniveau
`processadmin`	Processen beheren (o.a. KILL)
`setupadmin`	Linked servers toevoegen/verwijderen
`bulkadmin`	BULK INSERT uitvoeren
`diskadmin`	Schijfbestanden beheren
`dbcreator`	Databases aanmaken, wijzigen, verwijderen en herstellen
`public`	Standaardrol — elke login is automatisch lid

-- Login aan server role toevoegen
ALTER SERVER ROLE sysadmin ADD MEMBER [JuniorDBA];

-- Leden van een server role opvragen
SELECT name FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;

Database Roles

Rollen op databaseniveau — bepalen wat een user binnen één database mag doen.

Role	Rechten
`db_owner`	Volledige controle over de database
`db_datareader`	Alle tabellen lezen
`db_datawriter`	Alle tabellen schrijven (INSERT, UPDATE, DELETE)
`db_ddladmin`	DDL-statements (CREATE, ALTER, DROP)
`db_securityadmin`	Database roles en rechten beheren
`db_backupoperator`	Database backups uitvoeren
`db_denydatareader`	Lezen expliciet geweigerd
`db_denydatawriter`	Schrijven expliciet geweigerd

Granulaire rechten

GRANT  SELECT ON Customers TO [Fanny];   -- toekennen
REVOKE SELECT ON Customers FROM [Fanny]; -- intrekken
DENY   SELECT ON Customers TO [Fanny];   -- expliciet weigeren

GRANT vs DENY

DENY heeft voorrang op GRANT — een expliciete weigering wint altijd, ook als een rol wel GRANT geeft.

Rechten per actie

Recht	Laat toe
`SELECT`	Data lezen
`INSERT`	Nieuwe rijen toevoegen
`UPDATE`	Bestaande rijen wijzigen
`DELETE`	Rijen verwijderen
`EXECUTE`	Procedure/functie uitvoeren
`ALTER`	Structuur van het object wijzigen
`ALL` / `ALL PRIVILEGES`	Alle rechten op het object ineens

-- Eén recht per keer
GRANT SELECT ON Customers TO [Fanny];
GRANT INSERT ON Customers TO [Fanny];
GRANT UPDATE ON Customers TO [Fanny];

-- Meerdere rechten in één statement (komma-gescheiden)
GRANT SELECT, INSERT, UPDATE ON Customers TO [Fanny];

-- Alle rechten op het object
GRANT ALL ON Customers TO [Fanny];

-- Idem, maar dan intrekken/weigeren
REVOKE SELECT, INSERT, UPDATE ON Customers FROM [Fanny];
DENY   SELECT, INSERT, UPDATE ON Customers TO [Fanny];
REVOKE ALL ON Customers FROM [Fanny];
DENY   ALL ON Customers TO [Fanny];

-- Kolomniveau — recht beperken tot specifieke kolommen
GRANT SELECT, UPDATE ON Customers (customerName, phone) TO [Fanny];

ALL met mate

GRANT ALL is snel maar ondoorzichtig — later is niet meer af te lezen wélke rechten precies zijn toegekend. Geef in productie liever expliciet SELECT, INSERT, UPDATE, DELETE op, en reserveer ALL voor testomgevingen of de eigenaar van het object.

Service Accounts — sMSA / gMSA

Type	Wachtwoordbeheer	Meerdere servers	Vereist
Gewoon domeinaccount	Handmatig	Ja	Altijd beschikbaar
sMSA	Automatisch (AD)	Nee — 1 computer	Server 2008 R2+
gMSA	Automatisch (AD)	Ja	Server 2012+ DC + KDS

Voorkeur voor SQL Server: gMSA — geen wachtwoordbeheer, geen risico op verloop, automatische SPN-registratie.

gMSA aanmaken

# 1. KDS Root Key (eenmalig per domein — op de DC)
Add-KdsRootKey -EffectiveImmediately
# In een lab: onmiddellijk actief (niet voor productie)
Add-KdsRootKey -EffectiveTime (Get-Date).AddHours(-10)

# 2. gMSA aanmaken
New-ADServiceAccount -Name "svc-sqlengine" `
    -DNSHostName "sql01.lab.local" `
    -PrincipalsAllowedToRetrieveManagedPassword "sql01$", "sql02$" `
    -Enabled $true

# 3. Installeren op elke doelserver
Install-ADServiceAccount -Identity "svc-sqlengine"
Test-ADServiceAccount    -Identity "svc-sqlengine"

PrincipalsAllowedToRetrieveManagedPassword = lijst van computeraccounts (met $) die het wachtwoord mogen ophalen.

Configureren

Via SQL Server Configuration Manager (nooit services.msc!) → service → Properties → tab Log On → account domein\svc-sqlengine$, wachtwoord leeg laten, service herstarten.

Services.msc vermijden

Wijzig je het account via Services.msc, dan worden ACL's en lokale rechten niet bijgewerkt. Gebruik altijd Configuration Manager.

Welke services een eigen account?

SQL Server (engine) — eigen gMSA
SQL Server Agent — eigen gMSA (of gedeeld met engine)
SQL Server Browser — NT AUTHORITY\LOCAL SERVICE volstaat
SSRS — eigen gMSA indien geïnstalleerd

-- Actief service account controleren
SELECT servicename, service_account FROM sys.dm_server_services;

Backup & Restore

Server backup ≠ database backup

Ook als de serverbeheerder al backups neemt, zijn aparte databasebackups noodzakelijk — applicatie- en menselijke fouten vallen buiten het bereik van serverbackups.

Backup types

Type	Bevat	Gebruik
Full	Alle data, structuur + log — verplicht startpunt	Basis voor alle andere backups
Differential	Wijzigingen tov de laatste full backup	Sneller, kleiner dan full
Transaction log	Historiek sinds laatste log backup	Point-in-time recovery (enkel bij Full/Bulk-Logged)
Tail log	Laatste deel van de log — vóór restore	Voorkomt dataverlies net voor restore
Copy-only	Full backup zonder invloed op backupsequentie	Eenmalige extractie
File/filegroup, Partial	Individueel bestand of filegroup	Grote databases

Backup schema (voorbeeld)

Wekelijks:  Full backup
Dagelijks:  Differential backup
Elk uur:    Transaction log backup

Full & Differential backup

-- Full
BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH NOFORMAT, INIT;

-- Differential
BACKUP DATABASE [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.bak'
    WITH DIFFERENTIAL, NAME = N'ClassicModels-Full Database Backup';

Transaction log backup

BACKUP LOG [ClassicModels]
    TO DISK = N'D:\Microsoft SQL Server\Backup\ClassicModels.trn'
    WITH NAME = N'ClassicModels-Full Database Backup';

Backup devices (named alias voor een backupbestand)

-- Aanmaken
EXEC sp_addumpdevice
    @devtype = N'disk', @logicalname = N'localDiskBackup',
    @physicalname = N'D:\Microsoft SQL Server\Backup\localDiskBackup.bak';

-- Gebruiken
BACKUP DATABASE [ClassicModels] TO CM_Device;

-- Verwijderen
EXEC sp_dropdevice @logicalname = 'localDiskBackup';

Sample database downloaden (bv. AdventureWorks)

Geen GUI/browser beschikbaar op een Core server? Download een .bak-bestand rechtstreeks via PowerShell.

Invoke-WebRequest -Uri "https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2019.bak" -OutFile "D:\backup\AdventureWorks2019.bak"

Op examen / Core server

Invoke-WebRequest (alias iwr/wget) werkt zonder GUI — ideaal om snel een sample .bak naar de backupmap te halen vóór een RESTORE DATABASE. Controleer nadien met Get-Item D:\backup\AdventureWorks2019.bak of het bestand volledig is gedownload.

-- Direct herstellen naar een nieuwe database
RESTORE DATABASE AdventureWorks2019
    FROM DISK = N'D:\backup\AdventureWorks2019.bak'
    WITH MOVE 'AdventureWorks2019' TO 'D:\data\AdventureWorks2019.mdf',
         MOVE 'AdventureWorks2019_log' TO 'D:\log\AdventureWorks2019.ldf',
         REPLACE;

Logische bestandsnamen controleren

De namen achter MOVE zijn de logische namen in de backup, niet de bestandsnaam. Vraag ze eerst op:

RESTORE FILELISTONLY FROM DISK = N'D:\backup\AdventureWorks2019.bak';

Restore

-- 1. Tail log backup (laatste log vóór restore)
BACKUP LOG [ClassicModels]
    TO DISK = N'D:\...\ClassicModels_LogBackup.trn'
    WITH NAME = N'ClassicModels_LogBackup';

-- 2. Full backup restoren (NORECOVERY = nog niet klaar)
RESTORE DATABASE [ClassicModels]
    FROM DISK = N'D:\...\ClassicModels.bak'
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;

-- 3. Transaction log restoren
RESTORE LOG [ClassicModels]
    FROM DISK = N'D:\...\ClassicModels.trn'
    WITH FILE = 1, NOUNLOAD, STATS = 5;

NORECOVERY vs RECOVERY

Gebruik NORECOVERY zolang je nog extra backups wil toepassen. Gebruik RECOVERY (of laat weg) enkel bij de laatste restore-stap.

Recovery: automatic vs manual

Automatic — bij elke (her)start: uncommitted transacties teruggedraaid, voltooide maar niet-doorgeschreven transacties opnieuw uitgevoerd
Manual — bewust herstellen vanaf backup: eerst full, dan differential (indien aanwezig), dan transaction log(s)

Tips

Lees dit voor je begint

Altijd type opgeven — NOT NULL daar waar mogelijk
T-SQL kent geen UNSIGNED — gebruik TINYINT of een CHECK-constraint (>= 0) als negatieve waarden niet mogen
Sorteren is ORDER BY — niet sort by
Komma's tussen waarden in SELECT
Meerdere voorwaarden in WHERE: gebruik WHERE kolom IN ('optie1','optie2','optie3')

Uitvoervolgorde SELECT

De logische volgorde waarin SQL Server (T-SQL) een query verwerkt:

FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT

Datatypes

Getallen

Type	Bereik	Opmerking
`TINYINT`	0 tot 255	Altijd unsigned — geen negatieve waarden mogelijk
`SMALLINT`	-32,768 tot 32,767
`INT`	-2,147,483,648 tot 2,147,483,647
`INT IDENTITY(1,1)`	idem	Auto-increment PK — start bij 1, telt op met 1 per nieuwe rij
`BIGINT`	-9,223,372,036,854,775,808 tot 9,223,372,036,854,775,807
`DECIMAL(m,d)` / `NUMERIC(m,d)`	m = totaal cijfers, d = na komma	Exact — voorkeur voor geld/berekeningen
`FLOAT(n)`	Zwevendekommagetal, benaderend	n = bits precisie
`REAL`	Zwevendekommagetal, lagere precisie dan FLOAT	Gelijk aan `FLOAT(24)`
`MONEY` / `SMALLMONEY`	Valuta	Vaste precisie op 4 decimalen

Geen UNSIGNED in T-SQL

T-SQL kent geen UNSIGNED-modifier zoals MySQL. Enkel TINYINT is van nature unsigned (0–255); voor andere types met enkel positieve waarden gebruik je een CHECK-constraint, bv. CHECK (aantal >= 0).

Tekst, Datum & Overig

Type	Omschrijving
`CHAR(n)`	Vaste lengte tekst — opvulling met spaties
`VARCHAR(n)`	Variabele lengte tekst, max n karakters
`VARCHAR(MAX)`	Lange variabele tekst (tot 2GB) — vervangt het verouderde `TEXT`
`NVARCHAR(n)` / `NVARCHAR(MAX)`	Unicode-tekst — nodig voor bv. accenten/niet-Latijnse tekens
`DATE`	Datum (YYYY-MM-DD)
`DATETIME` / `DATETIME2`	Datum en tijd — `DATETIME2` heeft hogere precisie en ruimer bereik
`TIME`	Enkel tijd (HH:MM:SS)
`BIT`	Waar/onwaar waarde (0, 1 of NULL) — T-SQL heeft geen `BOOLEAN`

TIMESTAMP betekent iets anders in T-SQL

In MySQL is TIMESTAMP een datum/tijd-datatype. In T-SQL is TIMESTAMP (ook wel ROWVERSION) een automatisch verhogend binair versienummer per rij — geen datum, geen tijd. Gebruik DATETIME2 voor datum/tijd-kolommen.

Eigen datatype

-- Eigen type gebaseerd op een bestaand type (T-SQL)
CREATE TYPE PostalCode FROM VARCHAR(10) NOT NULL;

Constraints

Constraint	Omschrijving
`NOT NULL`	Kolom mag geen NULL-waarde bevatten
`UNIQUE`	Alle waarden in de kolom moeten uniek zijn
`CHECK`	Voorwaarde die moet worden voldaan voor de waarde in de kolom
`DEFAULT`	Standaardwaarde als er geen waarde wordt opgegeven
`PRIMARY KEY`	Unieke identifier voor een rij
`FOREIGN KEY`	Verwijzing naar een kolom in een andere tabel
`IDENTITY(start, stap)`	Automatisch ophogend getal — T-SQL-equivalent van MySQL's `AUTO_INCREMENT`

NULL-waarden

NULL = onbekend — niet hetzelfde als 0 of lege string
NULL + 1 = NULL — elke berekening met NULL geeft NULL
NULL = NULL is FALSE — gebruik altijd IS NULL / IS NOT NULL

SELECT * FROM customers WHERE creditLimit IS NULL;

-- NULL vervangen door standaardwaarde
SELECT customerName, COALESCE(creditLimit, 0) AS creditLimit FROM customers;

DDL — Data Definition Language

Categorie	Statements	Doel
DDL	CREATE, ALTER, DROP	Structuur van database-objecten
DQL	SELECT	Data opvragen
DML	INSERT, UPDATE, DELETE	Data manipuleren
DCL	GRANT, REVOKE	Rechten beheren

Schema & tijdelijke tabellen

Schema = namespace voor database-objecten; dbo = default schema — notatie schema.tabel (bv. dbo.Customers)

Tijdelijke tabel	Syntax	Zichtbaar voor
Lokaal	`#tabelnaam`	Huidige sessie
Globaal	`##tabelnaam`	Alle sessies

CREATE TABLE

-- Basis
CREATE TABLE tabel_naam (
    kolom1 datatype constraint,
    kolom2 datatype constraint,
    ...
);

-- IDENTITY (T-SQL equivalent van AUTO_INCREMENT)
-- IDENTITY(startwaarde, stap)
CREATE TABLE studenten (
    student_id    INT          IDENTITY(1,1) PRIMARY KEY,
    naam          VARCHAR(100) NOT NULL,
    geboortedatum DATE,
    email         VARCHAR(100) UNIQUE
);

-- IDENTITY info opvragen
SELECT SCOPE_IDENTITY();   -- laatste ingevoegde ID in huidige scope
SELECT @@IDENTITY;         -- laatste ingevoegde ID (ook over triggers heen)
DBCC CHECKIDENT('studenten', RESEED, 0);  -- teller resetten

AUTO_INCREMENT vs IDENTITY

MySQL gebruikt AUTO_INCREMENT, T-SQL gebruikt IDENTITY(start, stap). In T-SQL kun je ook IDENTITY combineren met NOT FOR REPLICATION bij replicatie-scenario's.

-- Met PRIMARY KEY, NOT NULL, UNIQUE
CREATE TABLE studenten (
    student_id    INT          IDENTITY(1,1) PRIMARY KEY,
    naam          VARCHAR(100) NOT NULL,
    geboortedatum DATE,
    email         VARCHAR(100) UNIQUE
);

-- Met FOREIGN KEY
CREATE TABLE inschrijvingen (
    inschrijving_id INT  IDENTITY(1,1) PRIMARY KEY,
    student_id      INT,
    cursus_id       INT,
    inschrijfdatum  DATE,
    FOREIGN KEY (student_id) REFERENCES studenten(student_id),
    FOREIGN KEY (cursus_id)  REFERENCES cursussen(cursus_id)
);

-- Met CHECK
CREATE TABLE producten (
    product_id INT            IDENTITY(1,1) PRIMARY KEY,
    naam       VARCHAR(100)   NOT NULL,
    prijs      DECIMAL(10, 2) CHECK (prijs > 0)
);

-- Met DEFAULT
CREATE TABLE bestellingen (
    bestelling_id INT      IDENTITY(1,1) PRIMARY KEY,
    klant_id      INT,
    bestel_datum  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Met UNIQUE
CREATE TABLE gebruikers (
    gebruiker_id   INT          IDENTITY(1,1) PRIMARY KEY,
    gebruikersnaam VARCHAR(50)  UNIQUE,
    wachtwoord     VARCHAR(255) NOT NULL
);

DROP TABLE

DROP TABLE IF EXISTS tabel_naam;

Warning

Een tabel verwijderen met een FK-constraint geeft een fout. Verwijder eerst de child table of de constraint.

ALTER TABLE

-- Kolom toevoegen (T-SQL: geen COLUMN-keyword na ADD)
ALTER TABLE tabel_naam ADD kolom_naam datatype constraint;

-- Kolom wijzigen (datatype/lengte)
ALTER TABLE dbo.Customers ALTER COLUMN customerName VARCHAR(100) NOT NULL;

-- Kolom verwijderen
ALTER TABLE tabel_naam DROP COLUMN kolom_naam;

-- Kolom hernoemen — via sp_rename (geen ALTER TABLE ... RENAME COLUMN in T-SQL)
EXEC sp_rename 'tabel_naam.oude_naam', 'nieuwe_naam', 'COLUMN';

-- Tabel hernoemen — via sp_rename (geen RENAME TABLE in T-SQL)
EXEC sp_rename 'oude_naam', 'nieuwe_naam';

Geen ADD COLUMN, CHANGE COLUMN of RENAME TABLE in T-SQL

Dit zijn MySQL-statements. T-SQL gebruikt ADD zonder COLUMN, ALTER COLUMN om type/lengte te wijzigen, en sp_rename om kolommen of tabellen te hernoemen — er bestaat geen apart statement om naam en type in één keer te wijzigen zoals MySQL's CHANGE COLUMN.

-- DEFAULT toevoegen aan een bestaande kolom (T-SQL: geen MODIFY!)
ALTER TABLE tabel_naam ADD CONSTRAINT DF_naam DEFAULT waarde FOR kolom_naam;

-- Die DEFAULT weer verwijderen
ALTER TABLE tabel_naam DROP CONSTRAINT DF_naam;

DEFAULT is een named constraint

In T-SQL bestaat er geen MODIFY COLUMN ... DEFAULT zoals in MySQL. Een DEFAULT op een bestaande kolom is een eigen constraint met een naam (DF_naam) die je apart toevoegt met ADD CONSTRAINT en verwijdert met DROP CONSTRAINT — niet met ALTER COLUMN.

Tabel info & hernoemen (T-SQL)

-- Overzicht kolommen, constraints, keys
EXEC sp_help 'dbo.Customers';

-- Overzicht indexen
EXEC sp_helpindex 'dbo.Customers';

-- Tabel / kolom hernoemen (werkt ook voor database, view, stored procedure)
EXEC sp_rename @objname = 'Offices', @newname = 'Department';
EXEC sp_rename @objname = 'Employees.jobTitle', @newname = 'title';

Data Integriteit

Type	Omschrijving
Declaratief	Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
Procedureel	Triggers — code die automatisch uitvoert bij INSERT/UPDATE/DELETE

Foreign Key — referentiële integriteit

Tabel met de FK = child table; tabel met de PK = parent table.

CONSTRAINT FK_PersonOrder
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
    ON DELETE CASCADE
    ON UPDATE SET NULL

Optie	Gedrag bij wijziging/verwijdering in parent table
`NO ACTION`	Enkel toegestaan als het geen invloed heeft op child — anders fout
`CASCADE`	Automatisch doorgevoerd in child table
`SET NULL`	FK in child wordt NULL gezet
`SET DEFAULT`	FK in child wordt op default waarde gezet

DML — Data Manipulation Language

INSERT

-- Basis
INSERT INTO tabel_naam (kolom1, kolom2, ...)
VALUES ('string_waarde', 'datum_waarde', getal_waarde);

-- Meerdere rijen
INSERT INTO studenten (naam, geboortedatum, email)
VALUES
    ('Piet Pietersen', '1992-02-02', 'piet.pietersen@example.com'),
    ('Klaas Klaassen', '1994-03-03', 'klaas.klaassen@example.com');

-- Met SELECT
INSERT INTO nieuwe_tabel (kolom1, kolom2)
SELECT kolom1, kolom2 FROM bestaande_tabel WHERE conditie;

UPDATE

-- Basis
UPDATE tabel_naam
SET kolom1 = 'waarde1', kolom2 = 'waarde2'
WHERE conditie;

-- Met subquery
UPDATE Studenten
SET Status = 'Uitblinker'
WHERE IQ > (
    SELECT AVG(IQ) FROM Studenten
);

-- UPDATE met JOIN
UPDATE e
SET    e.salary = e.salary * 1.1
FROM   employee e
JOIN   department d ON d.id = e.dep_id
WHERE  d.dep_name = 'Sales';

DELETE

-- Specifieke rij
DELETE FROM studenten WHERE student_id = 1;

-- Met meerdere voorwaarden
DELETE FROM studenten
WHERE geboortedatum < '2000-01-01' AND email LIKE '%@example.com';

-- Alle rijen
DELETE FROM studenten;

-- Met subquery
DELETE FROM studenten
WHERE student_id IN (
    SELECT student_id FROM inschrijvingen WHERE cursus_id = 2
);

Danger

DELETE FROM tabel zonder WHERE verwijdert alle rijen.

Sessievariabelen (T-SQL)

DECLARE @variabele datatype;
SET     @variabele = waarde;

-- of via SELECT
SELECT  @variabele = kolom FROM tabel;
SELECT  @variabele;

DECLARE @numberEmployees INT = 0;
SET     @numberEmployees = (SELECT COUNT(*) FROM Employees);
SELECT  @numberEmployees;

IF – ELSE

IF (@num1 > @num2)
    PRINT '1ste getal is groter.'
ELSE IF (@num2 > @num1)
    PRINT '2e getal is groter.'
ELSE
    PRINT 'De getallen zijn gelijk.';

-- Geneste IF
IF (@num1 > @num2)
BEGIN
    PRINT '1ste getal is groter.';
    IF (@num1 > 75)
        PRINT 'Groter dan 75.';
    ELSE
        PRINT 'Kleiner dan of gelijk aan 75.';
END
ELSE
    PRINT 'De getallen zijn gelijk.';

CASE Statement

Bruikbaar in SELECT, WHERE, ORDER BY, GROUP BY, UPDATE en INSERT.

-- In SELECT
SELECT EmployeeName,
       CASE
           WHEN Salary >= 80000 THEN 'Director'
           WHEN Salary >= 50000 THEN 'Senior Consultant'
           ELSE 'Medewerker'
       END AS Functie
FROM Employee;

-- In UPDATE
UPDATE employee
SET StateCode = CASE StateCode
    WHEN 'Ar' THEN 'FL'
    WHEN 'GE' THEN 'AL'
    ELSE 'IN'
END;

WHILE Loop

DECLARE @i INT = 1;

WHILE @i <= 10
BEGIN
    PRINT CONCAT('Pass...', @i);
    SET @i = @i + 1;
END;

Keyword	Gedrag
`BREAK`	Verlaat de lus onmiddellijk
`CONTINUE`	Slaat resterende instructies over, gaat naar volgende iteratie

Warning

CONTINUE kan een oneindige lus veroorzaken als de teller-update erna staat.

Transacties & Rollbacks

Reeks SQL-instructies die als één geheel wordt uitgevoerd — volledig geslaagd of volledig teruggedraaid.

Type	Beschrijving
Autocommit	Standaard — elke instructie is een eigen transactie
Implicit	Automatisch gestart bij elke DML — afsluiten met COMMIT/ROLLBACK
Explicit	Jij bepaalt begin en einde

BEGIN TRANSACTION;
-- ... instructies ...
COMMIT TRANSACTION;   -- of ROLLBACK TRANSACTION;

-- Save points — deel van een transactie terugdraaien
BEGIN TRANSACTION;
INSERT INTO [Person] VALUES ('Mouse', 'Micky', '500 South Buena Vista Street', 'California', 43);
SAVE TRANSACTION InsertStatement;

DELETE Person WHERE PersonId = 3;
ROLLBACK TRANSACTION InsertStatement;  -- enkel DELETE ongedaan
COMMIT;

-- Named transaction met mark (voor point-in-time log restore)
BEGIN TRANSACTION GenerateInvoices WITH MARK N'Facturatie van shipped orders';
    INSERT INTO Invoices (orderNumber, invoiceDate, invoiceAmount)
    SELECT orderNumber, shippedDate, SUM(quantityOrdered * priceEach)
    FROM Orders JOIN OrderDetails ON Orders.orderNumber = OrderDetails.orderNumber
    WHERE status = 'Shipped'
    GROUP BY orderNumber, shippedDate;
COMMIT TRANSACTION;

-- Impliciete transacties — T-SQL start automatisch een transactie bij elke DML
SET IMPLICIT_TRANSACTIONS ON;
-- ... DML ...
COMMIT;  -- of ROLLBACK — verplicht afsluiten!
SET IMPLICIT_TRANSACTIONS OFF;

Auto rollback

Faalt één instructie binnen een transactie, dan worden alle wijzigingen in die transactie automatisch teruggedraaid.

IMPLICIT_TRANSACTIONS

Met SET IMPLICIT_TRANSACTIONS ON start elke DML-instructie een transactie die je altijd expliciet moet afsluiten. Vergeten COMMIT of ROLLBACK houdt de tabel vergrendeld.

Cursors

Verwerkt query-resultaten rij per rij in plaats van als set.

Cursors zijn traag

Zolang een cursor open is, wordt de tabel vergrendeld. Vervang cursors waar mogelijk door JOINs.

-- 1. Declareren + 2. Openen
DECLARE EmpCursor CURSOR FOR SELECT id, name FROM employee;
OPEN EmpCursor;

-- 3. Eerste rij ophalen
FETCH NEXT FROM EmpCursor INTO @EmpId, @EmpName;

-- 4. Verwerken
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT 'Employee Id = ' + CAST(@EmpId AS NVARCHAR(10)) + ' | Name = ' + @EmpName;
    FETCH NEXT FROM EmpCursor INTO @EmpId, @EmpName;
END;

-- 5. Sluiten en vrijgeven
CLOSE      EmpCursor;
DEALLOCATE EmpCursor;

-- Cursor vervangen door JOIN (voorkeur!)
UPDATE employee
SET salary = CASE
    WHEN dep_name = 'Sales' THEN (salary * 1.1)
    WHEN dep_name = 'HR'    THEN (salary * 1.2)
    WHEN dep_name = 'IT'    THEN (salary * 1.5)
END
FROM   employee
INNER JOIN department ON department.id = employee.dep_id
WHERE  dep_name IN ('Sales', 'HR', 'IT');

DQL — SELECT

Basis

SELECT kolom1, kolom2 FROM tabel_naam;
SELECT kolom1 AS alias1, kolom2 AS alias2 FROM tabel_naam;
SELECT kolom1, kolom2 FROM tabel_naam WHERE conditie;
SELECT kolom1, kolom2 FROM tabel_naam WHERE c1 AND c2;
SELECT kolom1, kolom2 FROM tabel_naam WHERE c1 OR  c2;
SELECT kolom1, kolom2 FROM tabel_naam ORDER BY kolom1 ASC|DESC;
SELECT TOP aantal kolom1, kolom2 FROM tabel_naam ORDER BY kolom1;
SELECT DISTINCT kolom1  FROM tabel_naam;

Tabelnaam

Verplicht bij naamverwarring — gebruik tabelnaam.kolomnaam of een alias.

Geen LIMIT in T-SQL

MySQL gebruikt LIMIT aantal [OFFSET start]. T-SQL gebruikt TOP aantal (eerste N rijen) of, voor paginering, OFFSET ... ROWS FETCH NEXT ... ROWS ONLY — zie TOP & OFFSET-FETCH.

WHERE Clausules

Operator	Betekenis
`= < > <> >= <=`	Vergelijken
`LIKE '%hallo%'`	Patroonmatch
`IS NULL` / `IS NOT NULL`	Nooit `= NULL` gebruiken
`AND` / `OR` / `NOT`	Logische operatoren
`IN (a, b, c)`	Is één van deze waarden
`BETWEEN a AND b`	Binnen een bereik

-- IN
SELECT kolom1, kolom2 FROM tabel_naam
WHERE kolom1 IN (waarde1, waarde2, ...);

-- BETWEEN
SELECT kolom1, kolom2 FROM tabel_naam
WHERE kolom1 BETWEEN waarde1 AND waarde2;

-- LIKE
SELECT kolom1 FROM tabel_naam WHERE kolom1 LIKE 'voor%';  -- begint met 'voor'
SELECT kolom1 FROM tabel_naam WHERE kolom1 LIKE '%beeld'; -- eindigt met 'beeld'
SELECT kolom1 FROM tabel_naam WHERE kolom1 LIKE '%oor%';  -- bevat 'oor'

-- IS NULL / IS NOT NULL
SELECT kolom1, kolom2 FROM tabel_naam WHERE kolom1 IS NULL;
SELECT kolom1, kolom2 FROM tabel_naam WHERE kolom1 IS NOT NULL;

Jokerteken	Betekenis
`%`	Nul of meer karakters
`_`	Exact één karakter

Volgorde van clauses

SELECT  ….….  AS …, …      -- welke kolommen? tabel.kolom, SUM(...), COUNT(*) bij GROUP BY
FROM    …                   -- eerste tabel (volgorde niet belangrijk)
JOIN    … ON ….…. = ….….   -- tabel.kolom als PK/FK niet dezelfde naam hebben
…                           -- zoveel tabellen joinen als je wil
WHERE   …                   -- filteren: =, <>, LIKE, AND, OR, ...
GROUP BY …,…                -- records groeperen op gelijke kolomwaarden
HAVING  …                   -- filteren na groepering
ORDER BY …,…                -- sorteren op kolommen

JOIN

-- INNER JOIN (alleen matches in beide tabellen)
SELECT l.Naam, kl.KlasNaam
FROM Leerlingen l
JOIN Klassen kl ON l.KlasID = kl.ID;

-- LEFT JOIN (alle van linkertabel, ook zonder match)
SELECT l.Naam, kl.KlasNaam
FROM Leerlingen l
LEFT JOIN Klassen kl ON l.KlasID = kl.ID;

-- LEFT JOIN: alleen rijen zonder match in rechtertabel
SELECT l.Naam
FROM Leerlingen l
LEFT JOIN Klassen kl ON l.KlasID = kl.ID
WHERE kl.ID IS NULL;

-- JOIN met filter
SELECT l.Naam, kl.KlasNaam
FROM Leerlingen l
JOIN Klassen kl ON l.KlasID = kl.ID
WHERE kl.Graad = 3;

-- JOIN met 3 tabellen (keten van 1:N relaties)
SELECT l.Naam, kl.KlasNaam, s.Naam AS SchoolNaam
FROM Leerlingen l
JOIN Klassen kl ON l.KlasID = kl.ID
JOIN Scholen  s  ON kl.SchoolID = s.ID;

-- Multi-table zonder JOIN keyword
SELECT village.name
FROM village, inhabitant
WHERE village.villageid = inhabitant.villageid
AND   inhabitant.name = 'Dirty Dieter';

-- Many-to-Many relatie — vereist een mapping table
SELECT *
FROM persoon_boek
    JOIN persoon ON persoon.id = persoon_boek.persoon_id
    JOIN boek    ON boek_id    = persoon_boek.boek_id;

Join types overzicht

Type	Resultaat
INNER JOIN	Enkel rijen met match in beide tabellen — klassieke 1-N relatie
LEFT JOIN	Alle rijen van linkertabel + matches van rechter (NULL als geen match)
OUTER JOIN	Alle rijen van beide tabellen (inclusief niet-gematchte)
CROSS JOIN	Cartesisch product — elke rij van A gekoppeld aan elke rij van B
UNION	Resultaten van twee queries samenvoegen (verwijdert duplicaten)

TOP & OFFSET-FETCH

-- TOP: eerste N rijen (vereist ORDER BY voor deterministische resultaten)
SELECT TOP 10 customerName, creditLimit FROM Customers ORDER BY creditLimit DESC;
SELECT TOP 10 PERCENT customerName FROM Customers ORDER BY customerName;

-- OFFSET-FETCH: paginering (vereist ORDER BY)
SELECT customerName, creditLimit
FROM Customers
ORDER BY creditLimit DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
-- → sla de eerste 10 rijen over, geef de volgende 5 terug

LIMIT vs OFFSET-FETCH

MySQL gebruikt LIMIT n OFFSET m, T-SQL gebruikt OFFSET m ROWS FETCH NEXT n ROWS ONLY. Beide vereisen een ORDER BY.

GROUP BY & HAVING

-- GROUP BY
SELECT kolom1, COUNT(*) AS aantal
FROM tabel_naam
GROUP BY kolom1;

-- HAVING
SELECT kolom1, COUNT(*) AS aantal
FROM tabel_naam
GROUP BY kolom1
HAVING COUNT(*) > 10;

SELECT kolom1, SUM(kolom2) AS totaal
FROM tabel_naam
GROUP BY kolom1
HAVING SUM(kolom2) > 1000;

Aggregatiefuncties

Functie	Omschrijving
`COUNT(*)`	Aantal rijen
`SUM(kolom)`	Som
`AVG(kolom)`	Gemiddelde
`MIN(kolom)`	Minimumwaarde
`MAX(kolom)`	Maximumwaarde

SELECT COUNT(*) AS totaal          FROM tabel_naam;
SELECT SUM(kolom)  AS totaal       FROM tabel_naam;
SELECT AVG(kolom)  AS gemiddelde   FROM tabel_naam;
SELECT MIN(kolom)  AS minimum      FROM tabel_naam;
SELECT MAX(kolom)  AS maximum      FROM tabel_naam;

-- Met GROUP BY
SELECT kolom1, COUNT(*)    AS aantal    FROM tabel_naam GROUP BY kolom1;
SELECT kolom1, SUM(kolom2) AS totaal    FROM tabel_naam GROUP BY kolom1;
SELECT kolom1, AVG(kolom2) AS gemiddelde FROM tabel_naam GROUP BY kolom1;

-- Meerdere aggregaties in één query (scalaire subqueries)
SELECT
    (SELECT COUNT(*) FROM Liedjes2)                                     AS Aantal_Liedjes,
    (SELECT TOP 1 Duurtijd FROM Liedjes2 ORDER BY Duurtijd DESC)        AS Langste_Liedje,
    (SELECT SUM(Duurtijd) FROM Liedjes2 WHERE Artiest = 'U2')           AS totale_Tijd;

Stringfuncties & Opmaak

Stringfuncties

Functie	Omschrijving	Resultaat
`LEN(str)`	Lengte van een string	`LEN('voorbeeld')` → `9`
`CONCAT(s1,s2,...)`	Strings samenvoegen	`CONCAT('voor','beeld')` → `'voorbeeld'`
`SUBSTRING(str,pos,len)`	Deel van een string	`SUBSTRING('voorbeeld',1,4)` → `'voor'`
`UPPER(str)`	Naar hoofdletters	`UPPER('voorbeeld')` → `'VOORBEELD'`
`LOWER(str)`	Naar kleine letters	`LOWER('VOORBEELD')` → `'voorbeeld'`
`TRIM(str)`	Spaties trimmen	`TRIM(' voorbeeld ')` → `'voorbeeld'`
`REPLACE(str,van,naar)`	Deel vervangen	`REPLACE('voor-beeld','-','')` → `'voorbeeld'`
`CHARINDEX(sub,str)`	Positie van substring	`CHARINDEX('oor','voorbeeld')` → `2`
`LEFT(str,n)`	n tekens vanaf begin	`LEFT('voorbeeld',4)` → `'voor'`
`RIGHT(str,n)`	n tekens vanaf einde	`RIGHT('voorbeeld',4)` → `'eld'`
`COALESCE(a,b,...)`	Eerste niet-NULL waarde	`COALESCE(NULL, NULL, 'a')` → `'a'`
`GETDATE()`	Huidige datum en uur (T-SQL)	—

LENGTH/INSTR bestaan niet in T-SQL

MySQL gebruikt LENGTH(str) en INSTR(str, sub). T-SQL gebruikt LEN(str) en CHARINDEX(sub, str) — let op de omgekeerde volgorde van de argumenten bij CHARINDEX (eerst het gezochte deel, dan de string).

SELECT naam, LEN(naam) AS naam_lengte FROM studenten;
SELECT CONCAT(voornaam, ' ', achternaam) AS volledige_naam FROM studenten;
SELECT email FROM studenten WHERE email LIKE '%@example.com';
SELECT REPLACE(product_naam, '-', ' ') AS product_naam FROM producten;
SELECT LEFT(naam, 3) AS voorletters FROM studenten;
SELECT RIGHT(email, 10) AS domein    FROM studenten;
SELECT CHARINDEX('oor', naam) AS positie FROM studenten;

Afronden & Formatteren

Functie	Output	Afronding	Gebruik
`ROUND(n, d)`	Numeriek	Ja	Berekeningen
`ROUND(n, d, 1)`	Numeriek	Nee — afkappen	Financieel (exact afkappen)
`FORMAT(n, d)`	String	Ja	Weergave in rapporten

Geen TRUNCATE()-functie in T-SQL

MySQL heeft een aparte TRUNCATE(n, d)-functie. T-SQL gebruikt hiervoor dezelfde ROUND()-functie met een derde parameter: elke waarde ≠ 0 schakelt afronding uit en kapt gewoon af.

SELECT ROUND(1234.56789, 2);             -- 1234.57
SELECT ROUND(AVG(Score), 1) AS AfgerondGemiddelde FROM Inschrijvingen;

SELECT ROUND(1234.56789, 2, 1);          -- 1234.56 (afgekapt, niet afgerond)
SELECT ROUND(SUM(Bedrag), 0, 1) AS TotBedrag FROM Facturen;

SELECT FORMAT(1234.56789, 2);            -- '1,234.57' (string!)
SELECT FORMAT(SUM(Prijs), 2) AS TotaalBedrag FROM Aankopen;

FORMAT — custom format strings & cultuur

FORMAT(waarde, format, cultuur) gebruikt .NET custom format strings, geen eigen T-SQL-syntax. Elk teken in de format-string heeft een vaste betekenis:

Token	Betekenis
`0`	Verplicht cijfer — toont `0` als er geen cijfer is
`#`	Optioneel cijfer — toont niets als er geen cijfer is (geen leidende nullen)
`,` (komma)	Duizendtal-scheiding — enkel als hij vlak links van `0`/`#` of het decimaalteken staat
`.` (punt)	Decimaalteken-plaatshouder
letterlijke tekens (spatie, `€`, `%`, ...)	Worden exact zo overgenomen op die positie

# ##0.00 is fout

Een spatie tussen de #-tekens is géén duizendtal-scheiding — dat is gewoon een letterlijke spatie op die ene vaste positie, en werkt dus enkel toevallig voor getallen met precies 4 cijfers vóór de komma. De duizendtal-scheiding moet je aangeven met een komma:

SELECT FORMAT(1234.5, '#,##0.00 €', 'nl-BE');  -- '1.234,50 €'
SELECT FORMAT(1234.5, '#,##0.00 €', 'fr-BE');  -- '1 234,50 €'

De komma in de format-string is enkel een signaal ("groepeer hier") — het teken dat effectief getoond wordt (., , of een spatie) hangt af van de opgegeven cultuur (3de parameter), niet van wat je zelf typt.

-- Meer voorbeelden
SELECT FORMAT(1234567.891, '#,##0.00');             -- '1,234,567.89'  (default cultuur = en-US)
SELECT FORMAT(0.256, '0.0%');                        -- '25.6%'         (0 dwingt het cijfer af)
SELECT FORMAT(7, '000');                              -- '007'
SELECT FORMAT(SUM(Prijs), '#,##0.00 €', 'nl-BE') AS TotaalBedrag FROM Aankopen;

Datumfuncties

Functie	Omschrijving	Voorbeeld
`GETDATE()`	Huidige datum en tijd	`GETDATE()` → `2026-08-14 10:30:00`
`SYSDATETIME()`	Hogere precisie dan GETDATE	—
`DATEADD(deel, n, datum)`	Datum verhogen/verlagen	`DATEADD(DAY, 10, GETDATE())`
`DATEDIFF(deel, begin, einde)`	Verschil tussen twee datums	`DATEDIFF(DAY, orderDate, shippedDate)`
`DATEPART(deel, datum)`	Numeriek deel van een datum	`DATEPART(MONTH, GETDATE())` → `8`
`YEAR(datum)`	Jaar als integer	`YEAR(GETDATE())` → `2026`
`MONTH(datum)`	Maand als integer	`MONTH(GETDATE())` → `8`
`DAY(datum)`	Dag als integer	`DAY(GETDATE())` → `14`
`CONVERT(type, waarde, stijl)`	Datatype omzetten (met opmaak)	`CONVERT(VARCHAR, GETDATE(), 103)` → `'14/08/2026'`
`CAST(waarde AS type)`	Datatype omzetten (zonder opmaak)	`CAST(GETDATE() AS DATE)` → `2026-08-14`
`FORMAT(waarde, formaat)`	Weergave als string	`FORMAT(GETDATE(), 'dd/MM/yyyy')` → `'14/08/2026'`

`deel`-waarden	Betekenis
`YEAR` / `YY`	Jaar
`MONTH` / `MM`	Maand
`DAY` / `DD`	Dag
`HOUR` / `HH`	Uur
`MINUTE` / `MI`	Minuut
`SECOND` / `SS`	Seconde

-- Levertijd per bestelling
SELECT orderNumber,
       DATEDIFF(DAY, orderDate, shippedDate) AS LevertijdDagen
FROM Orders
WHERE shippedDate IS NOT NULL;

-- Bestellingen van de laatste 30 dagen
SELECT * FROM Orders
WHERE orderDate >= DATEADD(DAY, -30, GETDATE());

-- Datum opmaken als string
SELECT FORMAT(orderDate, 'dd MMMM yyyy', 'nl-BE') AS BestellingsDatum
FROM Orders;

-- Vereiste datum berekenen
DECLARE @OrderDate DATE = '2026-08-14';
DECLARE @RequiredDate DATE = DATEADD(DAY, 10, @OrderDate);

Subqueries

-- In WHERE met IN
SELECT Naam FROM Studenten
WHERE ID NOT IN (
    SELECT StudentID FROM Inschrijvingen
);

-- In WHERE met EXISTS
SELECT v.Naam FROM Vakken v
WHERE EXISTS (
    SELECT 1 FROM Inschrijvingen i
    WHERE i.VakID = v.ID AND i.Score > 90
);

-- Gecorreleerde subquery: student met hoogste IQ per klas
SELECT s.Naam, k.Naam AS Klas, s.IQ
FROM Studenten s
JOIN Klassen k ON s.KlasID = k.ID
WHERE s.IQ = (
    SELECT MAX(s2.IQ) FROM Studenten s2
    WHERE s2.KlasID = s.KlasID
);

-- HAVING met subquery: vakken onder het algemeen gemiddelde
SELECT v.Naam, AVG(i.Score) AS GemiddeldeScore
FROM Vakken v
JOIN Inschrijvingen i ON v.ID = i.VakID
GROUP BY v.ID
HAVING AVG(i.Score) < (
    SELECT AVG(Score) FROM Inschrijvingen
);

-- SELECT in SELECT: hoogste IQ in de klas met de meeste studenten
SELECT Naam, IQ
FROM Studenten
WHERE KlasID = (
    SELECT TOP 1 KlasID FROM Studenten
    GROUP BY KlasID
    ORDER BY COUNT(*) DESC
)
AND IQ = (
    SELECT MAX(IQ) FROM Studenten
    WHERE KlasID = (
        SELECT TOP 1 KlasID FROM Studenten
        GROUP BY KlasID
        ORDER BY COUNT(*) DESC
    )
);

Relaties

Type	Omschrijving
Primary Key	Unieke identifier voor een rij
Foreign Key	Verwijzing naar een kolom in een andere tabel
Many-to-Many	Tussentabel met Foreign Keys naar beide tabellen

Views, Stored Procedures & Triggers

Views

Opgeslagen SELECT-query, aan te spreken als virtuele tabel.

CREATE VIEW dbo.PersoneelKantoor
WITH SCHEMABINDING
AS
    SELECT e.firstName, e.lastName,
           o.addressLine1, o.addressLine2, o.city, o.postalCode
    FROM   dbo.employees e
    JOIN   dbo.offices   o ON e.officeCode = o.officeCode;
GO

SELECT * FROM dbo.PersoneelKantoor;
GRANT SELECT ON dbo.PersoneelKantoor TO [Fanny];
DROP VIEW IF EXISTS dbo.PersoneelKantoor;

Optie	Effect
`WITH SCHEMABINDING`	Onderliggende tabellen mogen niet van structuur wijzigen zolang de view bestaat
`WITH CHECK OPTION`	INSERT/UPDATE via de view mislukt als het record niet aan de WHERE-conditie voldoet

-- WITH CHECK OPTION voorbeeld
CREATE VIEW dbo.Motors
AS
    SELECT productCode, productName, productLine, buyPrice, MSRP
    FROM   products
    WHERE  productLine = 'Motorcycles'
    WITH CHECK OPTION;
GO
-- Een INSERT met productLine = 'Classic Cars' via deze view geeft een fout

Views wijzigen & rechten

-- View wijzigen — herschrijft de volledige definitie
ALTER VIEW dbo.PersoneelKantoor
AS
    SELECT e.firstName, e.lastName, e.email,
           o.addressLine1, o.addressLine2, o.city, o.postalCode
    FROM   dbo.employees e
    JOIN   dbo.offices   o ON e.officeCode = o.officeCode;
GO

GRANT  SELECT ON dbo.PersoneelKantoor TO [Fanny];    -- toekennen
DENY   SELECT ON dbo.PersoneelKantoor TO [Fanny];    -- expliciet weigeren
REVOKE SELECT ON dbo.PersoneelKantoor FROM [Fanny];  -- intrekken

-- Recht om de view zelf te wijzigen (structuur), los van leesrechten
GRANT ALTER ON dbo.PersoneelKantoor TO [JuniorDBA];

ALTER vs DROP + CREATE

ALTER behoudt bestaande rechten (GRANT/DENY) en afhankelijke objecten van een view/procedure/functie. DROP gevolgd door CREATE verliest ze — rechten moeten dan opnieuw worden toegekend.

Stored Procedures

Opgeslagen reeks T-SQL-instructies met parameters — eenmalig schrijven, overal aanroepen.

IF OBJECT_ID('dbo.GetCustomersFromCountry', 'P') IS NOT NULL
    DROP PROCEDURE dbo.GetCustomersFromCountry;
GO

CREATE PROCEDURE dbo.GetCustomersFromCountry
    @country NVARCHAR(100)
AS
BEGIN
    SELECT customerName
    FROM   customers
    WHERE  country = @country
    ORDER BY customerName;
END;
GO

-- Uitvoeren
EXEC dbo.GetCustomersFromCountry @country = 'Belgium';

-- Met conditiecontrole
CREATE PROCEDURE dbo.SetCreditLimitForCustomer
    @customerNumber INT,
    @newLimit       DECIMAL(10,2)
AS
BEGIN
    IF @newLimit > (SELECT creditLimit FROM customers WHERE customerNumber = @customerNumber)
    BEGIN
        UPDATE customers SET creditLimit = @newLimit WHERE customerNumber = @customerNumber;
    END;
END;
GO

-- Procedure wijzigen (rechten en afhankelijkheden blijven behouden)
ALTER PROCEDURE dbo.GetCustomersFromCountry
    @country NVARCHAR(100)
AS
BEGIN
    SELECT customerName, phone
    FROM   customers
    WHERE  country = @country
    ORDER BY customerName;
END;
GO

-- Rechten op een procedure
GRANT  EXECUTE ON dbo.GetCustomersFromCountry TO [Fanny];    -- toekennen
DENY   EXECUTE ON dbo.GetCustomersFromCountry TO [Fanny];    -- expliciet weigeren
REVOKE EXECUTE ON dbo.GetCustomersFromCountry FROM [Fanny];  -- intrekken

-- Recht om de procedure zelf te wijzigen
GRANT ALTER ON dbo.GetCustomersFromCountry TO [JuniorDBA];

Functies

Een scalar function geeft één waarde terug en is — in tegenstelling tot een procedure — bruikbaar midden in een query.

CREATE FUNCTION dbo.GetCreditLimitCustomer (@customerNumber INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (SELECT creditLimit FROM customers WHERE customerNumber = @customerNumber);
END;
GO

-- Gebruiken
SELECT customerNumber, customerName,
       dbo.GetCreditLimitCustomer(customerNumber) AS creditLimit
FROM   customers;

-- Functie wijzigen
ALTER FUNCTION dbo.GetCreditLimitCustomer (@customerNumber INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (SELECT creditLimit FROM customers WHERE customerNumber = @customerNumber);
END;
GO

GRANT  EXECUTE ON dbo.GetCreditLimitCustomer TO [Fanny];    -- toekennen
DENY   EXECUTE ON dbo.GetCreditLimitCustomer TO [Fanny];    -- expliciet weigeren
REVOKE EXECUTE ON dbo.GetCreditLimitCustomer FROM [Fanny];  -- intrekken

Procedure vs functie

Een procedure voert acties uit (INSERT, UPDATE, DELETE) en geeft geen waarde terug. Een functie berekent en geeft een waarde terug — bruikbaar in SELECT, WHERE en andere expressies.

Triggers

Wordt automatisch uitgevoerd bij een INSERT, UPDATE of DELETE op een tabel.

CREATE TRIGGER dbo.Payments_CheckAmount
ON   dbo.Payments
AFTER INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE amount < 0)
    BEGIN
        UPDATE p
        SET    p.amount = ABS(p.amount)
        FROM   dbo.Payments p
        JOIN   inserted    i ON p.customerNumber = i.customerNumber
                            AND p.checkNumber    = i.checkNumber;
    END;
END;
GO

DROP TRIGGER IF EXISTS dbo.Payments_CheckAmount;

Virtuele tabel	Bevat
`inserted`	De nieuwe rijen (bij INSERT en UPDATE)
`deleted`	De oude rijen (bij DELETE en UPDATE)

-- Trigger wijzigen (volledige definitie herschrijven)
ALTER TRIGGER dbo.Payments_CheckAmount
ON   dbo.Payments
AFTER INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE amount < 0)
    BEGIN
        UPDATE p
        SET    p.amount = ABS(p.amount)
        FROM   dbo.Payments p
        JOIN   inserted    i ON p.customerNumber = i.customerNumber
                            AND p.checkNumber    = i.checkNumber;
    END;
END;
GO

-- Tijdelijk uit-/inschakelen zonder te verwijderen
DISABLE TRIGGER dbo.Payments_CheckAmount ON dbo.Payments;
ENABLE  TRIGGER dbo.Payments_CheckAmount ON dbo.Payments;

Rechten op triggers

Triggers voeren automatisch uit — er bestaat geen EXECUTE-recht op een trigger zoals bij een procedure. Rechtenbeheer verloopt via ALTER op de tabel/trigger zelf (bv. GRANT ALTER ON dbo.Payments TO [JuniorDBA];).

Import & Export

Tool	Type	Gebruik
bcp	Command-line	Snel importeren/exporteren van platte bestanden
BULK INSERT	T-SQL statement	Importeren vanuit bestand rechtstreeks in T-SQL
Import and Export Wizard	GUI (SSMS)	Eenvoudige import/export via wizard
SSIS	ETL-platform	Complexe import/export met transformaties en flows

!!! note "Alleen mogelijk op de sql server zelf, indien met gSMA gewerkt wordt zeker zien aangemeld als domain admin.

Bestanden op een Core server krijgen (geen GUI/drag-and-drop)

Op een Core server is er geen Verkenner om een lokaal bestand naartoe te slepen. Werkwijze: het bestand eerst op je eigen pc naar een netwerkshare slepen, en het dan vanaf de Core server met Copy-Item lokaal ophalen.

# Op de Core server — bestand van de share naar lokale schijf kopiëren
Copy-Item "\\ADServJBE.DatabJBE.local\Share\05_Dept_emp.txt" -Destination "C:\temp\05_Dept_emp.txt"

UNC-pad

\\server\share\bestand.ext — syntax hierboven klopt. Vervang ADServJBE.DatabJBE.local en Share door de effectieve servernaam/sharenaam in jouw omgeving, en controleer dat het 05_Dept_emp.txt-bestand ook echt op de share staat vóór je kopieert.

bcp

-- Export: tabel naar bestand
bcp DatabaseNaam.dbo.TabelNaam out bestand.csv -S ServerNaam -T -t ":"

-- Import: bestand naar tabel
bcp DatabaseNaam.dbo.TabelNaam in bestand.csv -S ServerNaam -T -t ";"

-- Voorbeeld: view exporteren naar csv, karakterformaat, komma als delimiter
bcp ClassicModels.dbo.PersoneelKantoor out C:\temp\test.csv -c -t "," -S localhost -T

Optie	Betekenis
`out`	Exporteren vanuit tabel
`in`	Importeren naar tabel
`-S`	Servernaam
`-T`	Windows authenticatie (trusted connection)
`-t`	Delimiter
`-c`	Karakterformaat (character data type) — leesbaar csv-bestand

# Bestand controleren na export
cd c:\temp

BULK INSERT

BULK INSERT dbo.TabelNaam
FROM 'C:\pad\naar\bestand.csv'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2        -- sla de headerrij over
);

Import and Export Wizard

Object Explorer → rechtermuisklik op database → Tasks → Import Data... / Export Data...

SSIS — kerncomponenten

Component	Functie
Control Flow	Volgorde van uitvoering (containers, tasks)
Data Flow	Verwerkt de data (lezen, transformeren, schrijven)
Flat File Source	Leest data uit een plat bestand (CSV, TXT)
OLE DB Destination	Schrijft data naar een SQL Server tabel
Lookup	Zoekt een waarde op in een andere tabel
Derived Column	Berekent of transformeert een kolomwaarde
Loop Container	Voert een reeks tasks herhaaldelijk uit (bv. per bestand)

Maximum insert commit size

Stel in op 100000 bij grote imports voor betere performantie.

Datatypeconversie

Velden zoals birthdate in een CSV zijn vaak tekst — gebruik een Derived Column met substring en concatenatie om ze te converteren naar het juiste date datatype.

Performance & Indexing

Indexes maken opzoeken sneller, maar kosten opslagruimte en vertragen schrijfoperaties. Zonder index = full table scan.

Design richtlijnen

Index voor kolommen die vaak in JOIN of WHERE voorkomen
Beperk indexes met meerdere kolommen
Vermijd indexering van kleine tabellen (overkill)
Vermijd indexering van heel grote tabellen met veel inserts/updates

Soorten indexes

Type	Beschrijving
Heap	Geen index — data ongeorganiseerd, snel gefragmenteerd
Clustered	Data gesorteerd en opgeslagen in B-tree — max 16 kolommen, sleutel max 900 bytes
Nonclustered	Aparte boomstructuur met verwijzingen naar de data (RID lookup)

-- Eenvoudige nonclustered index
CREATE NONCLUSTERED INDEX idx_nc_lastname
    ON dbo.Employees(lastName);

-- Samengesteld (composite) index — meerdere kolommen
-- Zet de kolom met de hoogste selectiviteit als eerste
CREATE NONCLUSTERED INDEX idx_nc_lastname_firstname
    ON dbo.Employees(lastName, firstName);

-- Gefilterd (filtered) index — enkel een subset van rijen
CREATE NONCLUSTERED INDEX idx_nc_active
    ON dbo.Employees(lastName)
    WHERE isActive = 1;

-- Index met INCLUDE-kolommen (covering index — voorkomt key lookup)
CREATE NONCLUSTERED INDEX idx_nc_lastname_incl
    ON dbo.Employees(lastName)
    INCLUDE (firstName, salary);

Kolomvolgorde composite index

Zet de meest selectieve kolom (meeste unieke waarden) als eerste — de optimizer filtert dan het effectiefst. Kolommen in INCLUDE worden niet gesorteerd maar wel in de leaf pages opgeslagen.

Fragmentatie

Oplossing	Beschrijving
`FILLFACTOR`	% van een page dat gevuld mag zijn vóór splitsen
`PAD_INDEX`	Past FILLFACTOR ook toe op tussenliggende index pages

ALTER INDEX ALL ON dbo.Employees REORGANIZE;  -- fragmentatie verminderen
ALTER INDEX ALL ON dbo.Employees REBUILD;      -- volledige rebuild

Index gebruiken

De query optimizer bepaalt zelf of een index gebruikt wordt, op basis van hoe de WHERE-clause geformuleerd is. Het execution plan toont hoe data werd opgezocht (Index Scan, Index Seek, ...).

Tip

Gebruik IN (waarde1, waarde2) of = waarde i.p.v. OR of berekeningen op de kolom — de optimizer kiest dan vaker een index.

-- Index verplichten
SELECT * FROM Employees WITH (INDEX([idx_nc_lastname])) WHERE lastName = 'Patterson';

-- Index usage opvragen
SELECT object_name(S.object_id) AS table_name, I.name AS index_name,
       S.user_seeks, S.user_scans, S.user_lookups
FROM   sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS I ON S.object_id = I.object_id AND S.index_id = I.index_id
WHERE  S.object_id = object_id(N'dbo.Employees', N'U');

Caching testen

CHECKPOINT;                 -- 1. Dirty pages wegschrijven naar schijf
DBCC DROPCLEANBUFFERS;      -- 2. Geheugenbuffer leegmaken
DBCC FREEPROCCACHE;         -- 3. Execution plans uit geheugen verwijderen

Statistics

SQL Server houdt automatisch statistieken bij over de waardedistributie in kolommen. De query optimizer gebruikt deze om het beste uitvoeringsplan te kiezen.

-- Statistics handmatig updaten (na grote bulk-operaties)
UPDATE STATISTICS dbo.Employees;

-- Alle statistics in een database updaten
EXEC sp_updatestats;

-- Statistics van een index bekijken
DBCC SHOW_STATISTICS('dbo.Employees', 'idx_nc_lastname');

-- Automatisch updaten controleren
SELECT name, is_auto_update_stats_on FROM sys.databases WHERE name = DB_NAME();

Tip

Na grote bulk-inserts of -updates altijd UPDATE STATISTICS uitvoeren — verouderde statistieken leiden tot slechte uitvoeringsplannen.

Query history opvragen

Methode	Beschrijving
`sys.dm_exec_requests`	Lopende queries op dit moment
`sys.dm_exec_query_stats`	Statistieken van gecachte query-plans
`sys.dm_exec_sql_text`	Tekst van gecachte SQL-queries
`sys.dm_exec_query_plan`	Uitvoeringsplan van gecachte queries
Activity Monitor	Grafische tool in SSMS
SQL Server Profiler	Trace-tool
Query Store	Ingebouwde query-history (vanaf SQL Server 2016)

-- Lopende queries
SELECT r.start_time, session_id, db_name(database_id) [Database],
       substring(t.text, (r.statement_start_offset/2)+1,
           case when statement_end_offset IN (-1, 0)
               then (datalength(t.Text)-r.statement_start_offset/2)+1
               else (r.statement_end_offset-r.statement_start_offset)/2+1
           end) [Executing SQL],
       status, command, wait_type, wait_time, wait_resource
FROM   sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE  session_id != @@SPID AND session_id > 50
ORDER BY r.start_time;

Query Store (vanaf SQL Server 2016)

ALTER DATABASE ClassicModels SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Rapporten: Regressed Queries, Overall Resource Consumption, Top Resource Consuming Queries, Queries With Forced Plans, Queries With High Variation, Query Wait Statistics, Tracked Queries.

Automatisatie — SQL Server Agent

Centrale service voor het automatiseren van beheertaken — elke instantie heeft er één.

Component	Beschrijving
SQL Server service	De database-engine zelf
Windows Application Log	Logt systeemgebeurtenissen; Agent leest dit
SQL Server Agent service	Voert geautomatiseerde taken uit, reageert op events

Automatiseert: Jobs (stappen, schema's), Alerts (bewaken gebeurtenissen), Operators (ontvangers van meldingen), Multi-server environments (master + target servers).

Operators

EXEC msdb.dbo.sp_add_operator
    @name = N'Marcel', @enabled = 1,
    @weekday_pager_start_time = 80000, @weekday_pager_end_time = 180000,
    @pager_days = 62, @email_address = N'marcel@ap.be';

-- Minimale operator — enkel naam + mailadres (bv. labo-oefening "Lambik")
EXEC msdb.dbo.sp_add_operator
    @name = N'Lambik',
    @enabled = 1,
    @email_address = N'jouw.naam@ap.be';

Vereist: Database Mail

Een operator ontvangt enkel effectief mails als Database Mail geconfigureerd en gekoppeld is aan de SQL Server Agent (zie Database Mail) — de operator aanmaken volstaat niet op zich.

Via de GUI: SQL Server Agent → rechtermuisklik Operators → New Operator... → Name = Lambik, E-mail name = je AP-mailadres → OK.

Alerts

Reageert op een event in het Windows Application Log of een performantieconditie.

EXEC msdb.dbo.sp_add_alert
    @name = N'TEMPDB Growing', @enabled = 1,
    @delay_between_responses = 120,
    @performance_condition = N'Databases|Data File(s) Size (KB)|Employees|>|0';

EXEC msdb.dbo.sp_add_notification
    @alert_name = N'TEMPDB Growing', @operator_name = N'Marcel Kiekeboe',
    @notification_method = 1;

Jobs

Object Explorer → SQL Server Agent → Jobs → New Job...

Tabblad	Inhoud
General	Naam, eigenaar, categorie
Steps	Type (T-SQL, CmdExec, applicatie), database, commando; gedrag bij success/failure
Schedules	Eenmalig, terugkerend, of bij opstart
Alerts	Koppelen van alerts aan de job
Notifications	Operator verwittigen + actie bij voltooiing
Targets	Lokale server of target-servers (multi-server)

Job history: sysjobhistory in msdb — max 1000 rijen totaal, max 100 rijen per job.

Typische geautomatiseerde taken: backups (database + log), data overzetten, indexen herberekenen, data integriteit controleren, databases verkleinen.

Database Mail

SQL Server verstuurt zelf e-mails via SMTP — handig bij job-failures of alerts.

Vereist lidmaatschap van rol DatabaseMailUserRole in msdb
Kan zoekresultaten en bijlagen versturen; bewaart kopieën in msdb

Configureren via SSMS: Management → Database Mail → Configure Database Mail (SMTP-account → mail-profiel → security → system parameters).

Multi-server environments

Master server beheert en distribueert jobs naar target servers — één centrale DBA-instantie voor alle instanties.

Maintenance Plans

Grafische wizard in SSMS om terugkerende onderhoudstaken te plannen zonder handmatige T-SQL.

Aanmaken: Object Explorer → Management → Maintenance Plans → New Maintenance Plan...

Taak	Beschrijving
Back Up Database	Full, differential of log backup uitvoeren
Rebuild Index	Indexen volledig herbouwen (`REBUILD`) — verwijdert fragmentatie
Reorganize Index	Lichte defragmentatie (`REORGANIZE`) — database blijft online
Update Statistics	Statistieken bijwerken na grote wijzigingen
Check Database Integrity	`DBCC CHECKDB` uitvoeren
Shrink Database	Databasebestand verkleinen (gebruik spaarzaam!)
Cleanup History	Historiek van jobs, backups en maintenance plans opruimen
Execute T-SQL Statement	Vrij T-SQL-blok uitvoeren als onderhoudsstap

Maintenance Plan vs. Agent Job

Een Maintenance Plan genereert automatisch Agent jobs op de achtergrond. Gebruik de wizard voor standaardtaken; schrijf eigen Agent jobs voor complexere logica of conditionele stappen.

Shrink Database

Shrink verplaatst pagina's en vergroot indexfragmentatie drastisch. Gebruik het alleen bij uitzonderlijk hoge vrije ruimte — plan daarna altijd een index rebuild in.

Voorbeeld (labo): Full backup maintenance plan

Object Explorer → Management → rechtermuisklik Maintenance Plans → New Maintenance Plan... → naam geven → sleep de gewenste taken op de designer-canvas.

1. Back Up Database Task

Instelling	Waarde
Naam	`ClassicModels Full Backup maintenance`
Backup type	Full
Database(s)	`ClassicModels`
Destination	`D:\Microsoft SQL Server\Backup` — vink Create a sub-directory for each database aan
Optie	Verify backup integrity aanvinken

2. Maintenance Cleanup Task

Instelling	Waarde
Maintenance type	Backup files
File location	zelfde destination-map als de backup task
File extension	`bak`
File age	ouder dan 2 weken

3. Notify Operator (als taak in de designer, of via het onderliggende Agent Job → tabblad Notifications)

Instelling	Waarde
Operator	`Lambik`
Wanneer	When the job fails — enkel bij een mislukte volledige taak

4. Schedule — icoon Calendar bovenaan de designer, of via de Agent Job → tabblad Schedules

Occurs: Daily
Daily frequency: Occurs every 12 hour(s), starting at 06:00:00, ending at 18:00:00
→ resultaat: exact 2 uitvoeringen per dag, om 6u en 18u

Voorbeeld (labo): Transaction log backup maintenance plan

1. Back Up Database Task (Transaction Log)

Instelling	Waarde
Naam	`classicModels Transaction Backup maintenance`
Backup type	Transaction Log
Database(s)	`ClassicModels`
Destination	`D:\Microsoft SQL Server\Backup` — vink Create a sub-directory for each database aan
Optie	Verify backup integrity aanvinken

2. Maintenance Cleanup Task

Instelling	Waarde
Maintenance type	Backup files
File extension	`trn`
File age	ouder dan 3 dagen

3. Notify Operator

Instelling	Waarde
Operator	`Lambik`
Wanneer	enkel als de transaction log backup mislukt

4. Schedule

Occurs: Daily
Daily frequency: Occurs every 1 hour(s), starting at 07:00:00, ending at 17:00:00
→ resultaat: elk uur tussen 7u en 17u

Volgorde van taken in de designer

Verbind de taken met een precedence-pijl (backup task → cleanup task) zodat cleanup pas start na een geslaagde backup — anders loop je het risico recent gemaakte backups meteen weer op te ruimen bij een verkeerd ingestelde file age.

Migratie & Scalability

Data migratie = data verhuizen tussen systemen — "systeem" omvat applicatie + DBMS + OS + storage.

Note

Data leeft meestal veel langer dan het systeem waarin het opgeslagen is.

Schema versiebeheer

Aanpak	Beschrijving
State based	Applicatie bevat schema, tool vergelijkt en voert `ALTER` uit
Migration based	Applicatie bevat migration scripts

Migratiescenario's

Methode	Beschrijving
Backup & restore	Backup op bron, restore op doel
Detach & attach	Database loskoppelen en opnieuw koppelen op andere instance
Copy database	Wizard om database naar andere instance te kopiëren
Generate scripts wizard	Script om database over te zetten (logical backup)

-- Detach — enkel indien niet gerepliceerd/gemirrored en geen snapshots
EXEC sp_detach_db @dbname = [AdventureWorks2019];

-- Attach — enkel indien alle bestanden aanwezig zijn
CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'D:\SQLServer\AdventureWorks_Data.mdf'),
       (FILENAME = 'D:\SQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;

Copy Database (SSMS: Tasks → Copy Database...): origineel blijft beschikbaar tijdens transfer — via detach/attach (sneller, offline) of SQL Management Object (trager, blijft online).

Scale UP vs Scale OUT

Strategie	Betekenis
Scale UP	Meer resources aan bestaande server (RAM, CPU, ...)
Scale OUT	Meer servers — mirroring, replication, Always On

Database Mirroring

Hoge beschikbaarheid voor één database via een gesynchroniseerde kopie.

Principal stuurt transacties naar de mirror (warm/hot standby)
Witness server nodig voor automatische failover
Enkel bij full recovery model; mirror database moet identieke naam hebben, eerst restoren met NORECOVERY

Modus	Data overdracht
High-safety	Synchroon
High-performance	Asynchroon

Database Replication

Data kopiëren naar een andere instance met synchronisatie — asynchroon, gebaseerd op publisher–distributor–subscriber.

Rol	Beschrijving
Publisher	Bevat de brondatabase
Article	Database-objecten in de replicatie
Publication	Verzameling van articles
Distributor	Ontvangt transacties, verdeelt naar subscribers
Subscriber	Ontvangt de gewijzigde data
Subscription	Verzoek om een publicatie te ontvangen

Type	Aangemaakt op	Wie initieert
Push	Publisher server	Publisher stuurt data door
Pull	Subscriber server	Subscriber start het proces

Replicatietype	Beschrijving	Ideaal voor
Transactional	Transaction log, éénrichtingsverkeer	Snelle updates bij subscriber
Snapshot	Kopieert alle gepubliceerde data	Onregelmatige wijzigingen, kleine volumes
Merge	Alle richtingen, ook subscriber-wijzigingen	Off-line updates die gesynchroniseerd worden
Peer-to-peer	Elke server = publisher + distributor + subscriber	Verdeelde data, lage kans op conflicten

Configuratie (SSMS): Configure Distribution → New Publication → New Subscription. Monitoring via Replication Monitor (vereist sysadmin of replmonitor).

SQL Server Always On

Technologie	Beschrijving
AlwaysOn AG (Availability Groups)	Database-level HA — geen shared storage
AlwaysOn FCI (Failover Cluster Instances)	Instance-level HA — vereist shared storage

Failover Cluster Instances	Availability Groups
Shared storage (iSCSI/Fibre Channel SAN)	Geen shared storage
Instance-level HA	Database-level HA
Logins, jobs, certs intact na failover	Handmatig toevoegen aan secondary's
Active/Passive nodes	DR-replica kan Active Secondary zijn (read-only)
Verbinding via virtuele servernaam	Verbinding via AG listener naam
Geen bescherming tegen I/O uitval	Bescherming tegen I/O uitval
Geen vereisten voor recovery model	Databases moeten in FULL recovery model staan

Reporting Services (SSRS)

Webgebaseerde tool voor interactieve rapporten op basis van SQL Server data — bereikbaar via http://[server]/Reports.

Rapporten opgesteld via Report Builder of Visual Studio (SSDT)
Data via views en stored procedures
Ondersteunt parameters, incl. cascading parameters (parameter 2 hangt af van waarde parameter 1)
Exporteerbaar naar PDF, Excel, Word, ...

Werkwijze

1. Data voorbereiden in SQL Server (views/stored procedures)
2. Rapport aanmaken in Report Builder / Visual Studio
   → data source, dataset, parameters, layout
3. Rapport publiceren op de Reporting Services webserver
4. Rapport raadplegen via browser: http://[server]/Reports

Cascading parameters — voorbeeld

-- Dataset voor parameter "luchthaven"
SELECT airport_id, name FROM airport ORDER BY name;

-- Dataset voor parameter "vlucht" (afhankelijk van gekozen luchthaven)
SELECT f.flight_id,
       f.flightno + ' - ' + a_dep.name + ' - ' + a_arr.name AS vlucht
FROM   flight f
JOIN   airport a_dep ON f.departure_airport = a_dep.airport_id
JOIN   airport a_arr ON f.arrival_airport   = a_arr.airport_id
WHERE  f.departure_airport = @airport_id;

Troubleshooting

Algemene strategie

Probleem definiëren — symptomen vs. verwacht gedrag, enkel feiten, sinds wanneer?
Reproduceren — bij voorkeur in een testomgeving, zonder risico
Informatie verzamelen — foutmelding goed lezen, logs van alle subsystemen doorzoeken
Oorzaak zoeken — checklist (meest frequente oorzaken nakijken) of bisecting (systematisch verifiëren tot waar het nog werkt)
Oplossing uitproberen — let op neveneffecten, werkt het niet: ongedaan maken

Workaround ≠ fix

Is het probleem weg na een restart, dan heb je een workaround, geen fix. Blijf zoeken naar de root cause.

Common errors

Fout	Controleer
Can't connect to SQL Server	Draait de service? Netwerk? Firewall?
Access denied	Login, user, wachtwoord, rechten correct?
Too many connections	Aantal actieve vs. max verbindingen

-- Actieve processen / connecties
EXEC sp_who2;
SELECT @@CONNECTIONS, @@MAX_CONNECTIONS;

-- Connecties per database
SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame AS LoginName
FROM   sys.sysprocesses
WHERE  dbid > 0
GROUP BY dbid, loginame;

Hulpmiddelen

# Service status / herstarten
Get-Service -Name mssqlserver
Start-Service -Name MSSQLSERVER

Error Log: SSMS → Management → SQL Server Logs → Current
Activity Monitor: rechtermuisklik op server → Activity Monitor
Dynamic Management Views (DMV's): serverstatus bewaken, problemen diagnosticeren, prestaties afstemmen

-- Overzicht beschikbare DMV's
SELECT * FROM sys.system_objects WHERE type = 'V' AND name LIKE 'dm_%';

T-SQL Cheatsheet

Inhoudstafel

Planning & Installatie

Referentie-architectuur (lab)

Opslag logisch scheiden

Installatievolgorde

Vastleggen vóór installatie

Basiscontrole na installatie

Valkuilen

Installatie op Windows Server Core (silent)

Mixed mode + sa activeren (na silent install)

Firewall & DNS

SQL Server: Processen & Databases

Services

System databases

Nieuwe database plannen

Database aanmaken

Database wijzigen

Database verwijderen

Recovery modellen

Database Snapshots

Contained Databases

Logins, Users & Rechten

Types

Login aanmaken

User aanmaken

Server Roles

Database Roles

Granulaire rechten

Rechten per actie

Service Accounts — sMSA / gMSA

gMSA aanmaken

Configureren

Backup & Restore

Backup types

Backup schema (voorbeeld)

Full & Differential backup

Transaction log backup

Backup devices (named alias voor een backupbestand)

Sample database downloaden (bv. AdventureWorks)

Restore

Recovery: automatic vs manual

Tips

Uitvoervolgorde SELECT

Datatypes

Getallen

Tekst, Datum & Overig

Eigen datatype

Constraints

NULL-waarden

DDL — Data Definition Language

Schema & tijdelijke tabellen

CREATE TABLE

DROP TABLE

ALTER TABLE

Tabel info & hernoemen (T-SQL)

Data Integriteit

Foreign Key — referentiële integriteit

DML — Data Manipulation Language

INSERT

UPDATE

DELETE

Sessievariabelen (T-SQL)

IF – ELSE

CASE Statement

WHILE Loop

Transacties & Rollbacks

Cursors

DQL — SELECT

Basis

WHERE Clausules

Volgorde van clauses

JOIN

Join types overzicht

TOP & OFFSET-FETCH

GROUP BY & HAVING

Aggregatiefuncties

Stringfuncties & Opmaak

Stringfuncties

Afronden & Formatteren