T-SQL Cheatsheet

Johan Beysen — AP Hogeschool

Planning & Installation

Reference architecture (lab)

Domain Controller (AD DS + DNS)
        |
    lab network
        |
  +-----+------------------+
  |                         |
SQL Server              Windows Client
(member server)         (SSMS / management)

Role	Function
Domain Controller	Active Directory, DNS
SQL Server	Member server running the database engine
Windows Client	Management workstation with SSMS

DNS first

SQL Server and client must use the domain controller as their DNS server, otherwise strange domain and authentication issues will follow.

Separating storage logically

Volume	Use
`C:`	OS, SQL Server binaries
`D:`	Data files (`.mdf`, `.ndf`)
`L:`	Log files (`.ldf`)
`B:`	Backups

Service accounts

Do not run SQL Server services under a regular user account — use a gMSA.

Installation order

Create lab network
Install Domain Controller
Configure static IP and DNS
Create domain
Install SQL member server and join it to the domain
Create service accounts
Install SQL Server
Configure data, log, and backup locations
Install SSMS on the client
Test DNS, domain login, and SQL connectivity

Things to determine before installation

Hostname, IP address, DNS server
Default or named instance?
Which accounts run the SQL services?
Location of data files, log files, backups
Authentication model: Windows-only or mixed mode?
Start SQL Server Agent automatically?
Firewall rules for management from the client
Lab, staging, or production-like?

Basic verification after installation

hostname
ipconfig /all
nltest /dsgetdc:lab.local

Verify in SSMS: connection by hostname (not just IP), Windows authentication, status of SQL Server + SQL Server Agent, default locations, and the presence of master, model, msdb, tempdb.

Common Issues

Problem	Likely cause
Server cannot join domain	Wrong DNS server configured
SSMS only connects via IP	Name resolution/DNS not correct
Windows authentication fails	Client has no domain context or wrong credentials
SQL Server Agent won't start	Check service account, permissions, or edition
Backups fail	SQL service account has no write permissions on backup folder
Slow/unexpected database growth	Autogrowth and initial size not deliberately configured

Installation on Windows Server Core (silent)

On Core a GUI is not available — installation requires /qs or /q.

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

Important

With a gMSA do not provide /SQLSVCPASSWORD — AD manages the password itself.

# Verify installation
Get-Service MSSQLSERVER

Mixed mode + enabling sa (after silent install)

Mixed mode is not enabled by default after a silent install — configure it via single-user mode.

# 1. Stop the service
Stop-Service MSSQLSERVER -Force

# 2. Start single-user mode (in a separate window)
& "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m -s MSSQLSERVER

# 3. Enable mixed mode + sa (2nd window)
& "...\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 = 'YOURPASSWORD'"

# 4. Verify — result 0 = mixed mode active
& "...\sqlcmd.exe" -S "." -E -Q "SELECT CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS INT)"

# 5. Ctrl+C in window 1, then start normally
Start-Service MSSQLSERVER

Firewall & DNS

# Allow remote access (port 1433)
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# Force DNS record after domain join
ipconfig /registerdns
ipconfig /flushdns

Basic rule

Plan first, then install. Poor choices around accounts, storage, and naming will come back to haunt you in permissions management, backups, and troubleshooting.

SQL Server: Processes & Databases

Services

Service	Description
SQL Server (MSSQLSERVER)	Main service — database engine
SQL Server Agent (MSSQLSERVER)	Job management and alerts
SQL Server Browser	Listens for connection requests
SQL Server CEIP service	Telemetry
SQL Server VSS Writer	Volume Shadow Copy support

In production

Stopping or restarting disconnects all active connections.

# Query status
Get-Service MSSQLSERVER
Get-Service *SQL*                                        # all SQL-related services
Get-Service -Name SQLSERVERAGENT | Select-Object Name, Status, StartType

# Start / stop / restart
Start-Service   MSSQLSERVER
Stop-Service    MSSQLSERVER -Force
Restart-Service MSSQLSERVER -Force

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

Named instance

For a named instance the service is called MSSQL$InstanceName (engine) and SQLAgent$InstanceName (agent) — the $ sign must be escaped in PowerShell with a backtick:

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

:: Alternative via net (also usable in a regular cmd, without PowerShell)
net start MSSQLSERVER
net stop  MSSQLSERVER
net start SQLSERVERAGENT
net stop  SQLSERVERAGENT

-- Query SQL Server Agent status 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	Function
`master`	System-level information for the instance
`msdb`	Used by SQL Server Agent (jobs, alerts)
`model`	Template for every new database
`Resource` (hidden)	Read-only system objects (via `sys` schema)
`tempdb`	Workspace for temporary objects — rebuilt on every restart

-- Overview of existing databases
SELECT name, database_id, create_date FROM sys.databases;

Planning a new database

Minimum required: database name, owner, ≥1 data file (.mdf/.ndf), ≥1 log file (.ldf), ≥1 filegroup, options (collation, recovery model, ...).

DB.mdf      → Raid 1      (primary data file)
DB.ldf      → Raid 10     (transaction log — separate physical disk!)
Sales1.ndf  → Raid 5      (additional data files)
TempDB      → Raid 5/10

Create database

-- Simple — uses default configuration
CREATE DATABASE sample;
GO

-- Extended, with filegroups and additional data file
CREATE DATABASE voorbeelddb
ON PRIMARY
(
    name       = voorbeelddb_dat,
    filename   = 'D:\data\voorbeelddb.mdf',
    size       = 10MB,
    maxsize    = 500MB,
    filegrowth = 10%
),
(
    name       = voorbeelddb_dat2,          -- extra data file (.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

Modify database

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

-- Single-user mode (maintenance)
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;

Drop database

USE master;
DROP DATABASE projects;

Irreversible

Always ensure you have a recent backup before dropping a database.

Recovery models

Determines how SQL Server maintains the transaction log, and therefore what is possible during recovery.

Model	Log behavior	Point-in-time recovery	Log backup required
Simple	Log is automatically truncated after checkpoint	No — only to last full/differential	No
Full	Fully maintained until explicit log backup	Yes, to the second	Yes
Bulk-Logged	Bulk operations minimally logged	Limited — not during bulk period	Yes

Simple → test environment, data loss of hours acceptable, no log backup management desired
Full → production, point-in-time recovery required, structured backup schedule
Bulk-Logged → temporarily before large bulk load, then log backup + switch back to Full

Forgetting log backups = log fills up

Without regular log backups the .ldf file will keep growing until the disk is full. Set up a SQL Server Agent job for automatic log backups.

-- Query / set
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MyDatabase';
ALTER DATABASE MyDatabase SET RECOVERY FULL;  -- or SIMPLE / BULK_LOGGED

Production critical?
├── No → SIMPLE
└── Yes
    ├── Large bulk operations planned? → temporarily BULK_LOGGED, then back to FULL
    └── Point-in-time recovery required? → FULL (+ regular log backups)

Database Snapshots

Read-only, static point-in-time view of a database — no separate recovery process, read only.

Contains only changed pages (copy-on-write); unchanged data is read from the source database
Useful for reporting, testing, or quick rollback without a restore
Source database must remain online as long as the snapshot exists

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

-- Revert database to the point in time of the snapshot
RESTORE DATABASE ClassicModels FROM DATABASE_SNAPSHOT = 'ClassicModels_snap';

-- Delete snapshot
DROP DATABASE ClassicModels_snap;

Limitations

Snapshots are not a backup — they disappear as soon as the source database crashes. Does not support full-text catalogs and requires the source database to be in ONLINE state.

Contained Databases

A contained database stores authentication data internally — logins do not need to exist at the instance level.

Type	Description
`NONE`	Default — fully dependent on the SQL Server instance
`PARTIAL`	Users and passwords stored in the database

-- Allow contained databases (once at server level)
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;

-- Set database to PARTIAL
ALTER DATABASE ClassicModels SET CONTAINMENT = PARTIAL;

-- Create contained user (no instance login needed)
USE ClassicModels;
CREATE USER [localuser] WITH PASSWORD = 'P@ssw0rd!';

-- Query containment status
SELECT name, containment_desc FROM sys.databases;

When to use?

Useful for migrations or multi-tenant applications where logins cannot be managed centrally. Downside: passwords fall outside the instance security policies.

Logins, Users & Permissions

	Login	User
Level	SQL Server engine (server)	Database
Stored in	`master`	User database
Grants access to	The SQL Server instance	One specific database
Permissions	Server roles	Database permissions (read, write, execute, ...)

One login can be linked to multiple users (one per database) and to one or more server roles.

Types

Login type	User type
Windows login (user or group account)	Windows user
SQL Server login (password managed by SQL Server)	SQL user with password
Certificate / asymmetric key login	SQL user with login — can map to multiple databases
	SQL user without login — no connection, but can own objects

-- 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];

Naming conventions

Windows login (user): always DOMAIN\username — e.g. DataJBE\jbeysen
Windows login (group): DOMAIN\Groupname — e.g. DataJBE\SQL_Admins
SQL Server login: function/person name, not sa or admin, no spaces
Service accounts: dedicated account per application, not shared
sa always disabled in production, renamed if possible

-- Modify login
ALTER LOGIN [training\SeniorDeveloper] DISABLE;

Create user

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

Server Roles

Roles at instance level — determine what a login can do on the SQL Server itself.

Role	Permissions
`sysadmin`	Full control over the SQL Server instance
`serveradmin`	Modify server configuration and shut down the instance
`securityadmin`	Manage logins, GRANT/DENY at server level
`processadmin`	Manage processes (incl. KILL)
`setupadmin`	Add/remove linked servers
`bulkadmin`	Execute BULK INSERT
`diskadmin`	Manage disk files
`dbcreator`	Create, modify, drop, and restore databases
`public`	Default role — every login is automatically a member

-- Add login to server role
ALTER SERVER ROLE sysadmin ADD MEMBER [JuniorDBA];

-- Query members of a server role
SELECT name FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;

Database Roles

Roles at database level — determine what a user can do within one database.

Role	Permissions
`db_owner`	Full control over the database
`db_datareader`	Read all tables
`db_datawriter`	Write all tables (INSERT, UPDATE, DELETE)
`db_ddladmin`	DDL statements (CREATE, ALTER, DROP)
`db_securityadmin`	Manage database roles and permissions
`db_backupoperator`	Perform database backups
`db_denydatareader`	Reading explicitly denied
`db_denydatawriter`	Writing explicitly denied

Granular permissions

GRANT  SELECT ON Customers TO [Fanny];   -- grant
REVOKE SELECT ON Customers FROM [Fanny]; -- revoke
DENY   SELECT ON Customers TO [Fanny];   -- explicitly deny

GRANT vs DENY

DENY takes precedence over GRANT — an explicit denial always wins, even if a role has GRANT.

Permissions per action

Permission	Allows
`SELECT`	Read data
`INSERT`	Add new rows
`UPDATE`	Modify existing rows
`DELETE`	Delete rows
`EXECUTE`	Execute procedure/function
`ALTER`	Modify object structure
`ALL` / `ALL PRIVILEGES`	All permissions on the object at once

-- One permission at a time
GRANT SELECT ON Customers TO [Fanny];
GRANT INSERT ON Customers TO [Fanny];
GRANT UPDATE ON Customers TO [Fanny];

-- Multiple permissions in one statement (comma-separated)
GRANT SELECT, INSERT, UPDATE ON Customers TO [Fanny];

-- All permissions on the object
GRANT ALL ON Customers TO [Fanny];

-- Same, but revoke/deny
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];

-- Column level — restrict permission to specific columns
GRANT SELECT, UPDATE ON Customers (customerName, phone) TO [Fanny];

ALL in moderation

GRANT ALL is quick but opaque — later you can no longer see exactly which permissions were granted. In production, explicitly specify SELECT, INSERT, UPDATE, DELETE, and reserve ALL for test environments or the object owner.

Service Accounts — sMSA / gMSA

Type	Password management	Multiple servers	Required
Regular domain account	Manual	Yes	Always available
sMSA	Automatic (AD)	No — 1 computer	Server 2008 R2+
gMSA	Automatic (AD)	Yes	Server 2012+ DC + KDS

Preferred for SQL Server: gMSA — no password management, no expiry risk, automatic SPN registration.

Create gMSA

# 1. KDS Root Key (once per domain — on the DC)
Add-KdsRootKey -EffectiveImmediately
# In a lab: immediately active (not for production)
Add-KdsRootKey -EffectiveTime (Get-Date).AddHours(-10)

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

# 3. Install on each target server
Install-ADServiceAccount -Identity "svc-sqlengine"
Test-ADServiceAccount    -Identity "svc-sqlengine"

PrincipalsAllowedToRetrieveManagedPassword = list of computer accounts (with $) that are allowed to retrieve the password.

Configure

Via SQL Server Configuration Manager (never services.msc!) → service → Properties → Log On tab → account domain\svc-sqlengine$, leave password empty, restart service.

Avoid Services.msc

If you change the account via Services.msc, ACLs and local permissions are not updated. Always use Configuration Manager.

Which services need their own account?

SQL Server (engine) — own gMSA
SQL Server Agent — own gMSA (or shared with engine)
SQL Server Browser — NT AUTHORITY\LOCAL SERVICE is sufficient
SSRS — own gMSA if installed

-- Check active service account
SELECT servicename, service_account FROM sys.dm_server_services;

Backup & Restore

Server backup ≠ database backup

Even if the server administrator already takes backups, separate database backups are necessary — application and human errors fall outside the scope of server backups.

Backup types

Type	Contains	Use
Full	All data, structure + log — mandatory starting point	Basis for all other backups
Differential	Changes since the last full backup	Faster, smaller than full
Transaction log	History since last log backup	Point-in-time recovery (only with Full/Bulk-Logged)
Tail log	Last part of the log — before restore	Prevents data loss just before restore
Copy-only	Full backup without affecting backup sequence	One-time extraction
File/filegroup, Partial	Individual file or filegroup	Large databases

Backup schedule (example)

Weekly:   Full backup
Daily:    Differential backup
Hourly:   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 for a backup file)

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

-- Use
BACKUP DATABASE [ClassicModels] TO CM_Device;

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

Download sample database (e.g. AdventureWorks)

No GUI/browser available on a Core server? Download a .bak file directly via PowerShell.

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

On exam / Core server

Invoke-WebRequest (alias iwr/wget) works without a GUI — ideal for quickly getting a sample .bak to the backup folder before a RESTORE DATABASE. Verify afterwards with Get-Item D:\backup\AdventureWorks2019.bak that the file was fully downloaded.

-- Restore directly to a new 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;

Check logical file names

The names after MOVE are the logical names in the backup, not the filename. Query them first:

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

Restore

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

-- 2. Restore full backup (NORECOVERY = not finished yet)
RESTORE DATABASE [ClassicModels]
    FROM DISK = N'D:\...\ClassicModels.bak'
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;

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

NORECOVERY vs RECOVERY

Use NORECOVERY as long as you still want to apply additional backups. Use RECOVERY (or omit it) only for the last restore step.

Recovery: automatic vs manual

Automatic — on every (re)start: uncommitted transactions are rolled back, completed but not yet written transactions are re-applied
Manual — deliberately restoring from backup: first full, then differential (if present), then transaction log(s)

Tips

Read this before you start

Always specify the type — NOT NULL where possible
T-SQL has no UNSIGNED — use TINYINT or a CHECK constraint (>= 0) if negative values are not allowed
Sorting is ORDER BY — not sort by
Commas between values in SELECT
Multiple conditions in WHERE: use WHERE column IN ('option1','option2','option3')

SELECT Execution Order

The logical order in which SQL Server (T-SQL) processes a query:

FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT

Datatypes

Numbers

Type	Range	Note
`TINYINT`	0 to 255	Always unsigned — no negative values possible
`SMALLINT`	-32,768 to 32,767
`INT`	-2,147,483,648 to 2,147,483,647
`INT IDENTITY(1,1)`	same	Auto-increment PK — starts at 1, increments by 1 per new row
`BIGINT`	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`DECIMAL(m,d)` / `NUMERIC(m,d)`	m = total digits, d = after decimal	Exact — preferred for money/calculations
`FLOAT(n)`	Floating-point number, approximate	n = bits of precision
`REAL`	Floating-point number, lower precision than FLOAT	Equivalent to `FLOAT(24)`
`MONEY` / `SMALLMONEY`	Currency	Fixed precision to 4 decimal places

No UNSIGNED in T-SQL

T-SQL has no UNSIGNED modifier like MySQL. Only TINYINT is naturally unsigned (0–255); for other types with only positive values use a CHECK constraint, e.g. CHECK (quantity >= 0).

Text, Date & Other

Type	Description
`CHAR(n)`	Fixed-length text — padded with spaces
`VARCHAR(n)`	Variable-length text, max n characters
`VARCHAR(MAX)`	Long variable text (up to 2GB) — replaces the deprecated `TEXT`
`NVARCHAR(n)` / `NVARCHAR(MAX)`	Unicode text — needed for e.g. accented/non-Latin characters
`DATE`	Date (YYYY-MM-DD)
`DATETIME` / `DATETIME2`	Date and time — `DATETIME2` has higher precision and wider range
`TIME`	Time only (HH:MM:SS)
`BIT`	True/false value (0, 1 or NULL) — T-SQL has no `BOOLEAN`

TIMESTAMP means something different in T-SQL

In MySQL TIMESTAMP is a date/time data type. In T-SQL TIMESTAMP (also known as ROWVERSION) is an automatically incrementing binary version number per row — not a date, not a time. Use DATETIME2 for date/time columns.

Custom data type

-- Custom type based on an existing type (T-SQL)
CREATE TYPE PostalCode FROM VARCHAR(10) NOT NULL;

Constraints

Constraint	Description
`NOT NULL`	Column cannot contain a NULL value
`UNIQUE`	All values in the column must be unique
`CHECK`	Condition that must be satisfied for the value in the column
`DEFAULT`	Default value when no value is provided
`PRIMARY KEY`	Unique identifier for a row
`FOREIGN KEY`	Reference to a column in another table
`IDENTITY(start, step)`	Auto-incrementing number — T-SQL equivalent of MySQL's `AUTO_INCREMENT`

NULL values

NULL = unknown — not the same as 0 or empty string
NULL + 1 = NULL — any calculation involving NULL gives NULL
NULL = NULL is FALSE — always use IS NULL / IS NOT NULL

SELECT * FROM customers WHERE creditLimit IS NULL;

-- Replace NULL with a default value
SELECT customerName, COALESCE(creditLimit, 0) AS creditLimit FROM customers;

DDL — Data Definition Language

Category	Statements	Purpose
DDL	CREATE, ALTER, DROP	Structure of database objects
DQL	SELECT	Query data
DML	INSERT, UPDATE, DELETE	Manipulate data
DCL	GRANT, REVOKE	Manage permissions

Schema & temporary tables

Schema = namespace for database objects; dbo = default schema — notation schema.table (e.g. dbo.Customers)

Temporary table	Syntax	Visible to
Local	`#tablename`	Current session
Global	`##tablename`	All sessions

CREATE TABLE

-- Basic
CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

-- IDENTITY (T-SQL equivalent of AUTO_INCREMENT)
-- IDENTITY(start value, step)
CREATE TABLE students (
    student_id    INT          IDENTITY(1,1) PRIMARY KEY,
    naam          VARCHAR(100) NOT NULL,
    geboortedatum DATE,
    email         VARCHAR(100) UNIQUE
);

-- Query IDENTITY info
SELECT SCOPE_IDENTITY();   -- last inserted ID in current scope
SELECT @@IDENTITY;         -- last inserted ID (including across triggers)
DBCC CHECKIDENT('students', RESEED, 0);  -- reset the counter

AUTO_INCREMENT vs IDENTITY

MySQL uses AUTO_INCREMENT, T-SQL uses IDENTITY(start, step). In T-SQL you can also combine IDENTITY with NOT FOR REPLICATION in replication scenarios.

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

-- With FOREIGN KEY
CREATE TABLE registrations (
    registration_id INT  IDENTITY(1,1) PRIMARY KEY,
    student_id      INT,
    course_id       INT,
    registration_date  DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

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

-- With DEFAULT
CREATE TABLE orders (
    order_id     INT      IDENTITY(1,1) PRIMARY KEY,
    customer_id  INT,
    order_date   DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- With UNIQUE
CREATE TABLE users (
    user_id   INT          IDENTITY(1,1) PRIMARY KEY,
    username  VARCHAR(50)  UNIQUE,
    password  VARCHAR(255) NOT NULL
);

DROP TABLE

DROP TABLE IF EXISTS table_name;

Warning

Dropping a table with a FK constraint produces an error. Drop the child table or the constraint first.

ALTER TABLE

-- Add column (T-SQL: no COLUMN keyword after ADD)
ALTER TABLE table_name ADD column_name datatype constraint;

-- Modify column (data type/length)
ALTER TABLE dbo.Customers ALTER COLUMN customerName VARCHAR(100) NOT NULL;

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Rename column — via sp_rename (no ALTER TABLE ... RENAME COLUMN in T-SQL)
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';

-- Rename table — via sp_rename (no RENAME TABLE in T-SQL)
EXEC sp_rename 'old_name', 'new_name';

No ADD COLUMN, CHANGE COLUMN or RENAME TABLE in T-SQL

These are MySQL statements. T-SQL uses ADD without COLUMN, ALTER COLUMN to change type/length, and sp_rename to rename columns or tables — there is no separate statement to change name and type in one step like MySQL's CHANGE COLUMN.

-- Add DEFAULT to an existing column (T-SQL: no MODIFY!)
ALTER TABLE table_name ADD CONSTRAINT DF_name DEFAULT value FOR column_name;

-- Remove that DEFAULT
ALTER TABLE table_name DROP CONSTRAINT DF_name;

DEFAULT is a named constraint

In T-SQL there is no MODIFY COLUMN ... DEFAULT as in MySQL. A DEFAULT on an existing column is a separate constraint with a name (DF_name) that you add separately with ADD CONSTRAINT and remove with DROP CONSTRAINT — not with ALTER COLUMN.

Table info & rename (T-SQL)

-- Overview of columns, constraints, keys
EXEC sp_help 'dbo.Customers';

-- Overview of indexes
EXEC sp_helpindex 'dbo.Customers';

-- Rename table / column (also works for database, view, stored procedure)
EXEC sp_rename @objname = 'Offices', @newname = 'Department';
EXEC sp_rename @objname = 'Employees.jobTitle', @newname = 'title';

Data Integrity

Type	Description
Declarative	Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
Procedural	Triggers — code that automatically executes on INSERT/UPDATE/DELETE

Foreign Key — referential integrity

Table with the FK = child table; table with the PK = parent table.

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

Option	Behavior on change/deletion in parent table
`NO ACTION`	Only allowed if it has no impact on the child — otherwise error
`CASCADE`	Automatically propagated to child table
`SET NULL`	FK in child is set to NULL
`SET DEFAULT`	FK in child is set to default value

DML — Data Manipulation Language

INSERT

-- Basic
INSERT INTO table_name (column1, column2, ...)
VALUES ('string_value', 'date_value', numeric_value);

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

-- With SELECT
INSERT INTO new_table (column1, column2)
SELECT column1, column2 FROM existing_table WHERE condition;

UPDATE

-- Basic
UPDATE table_name
SET column1 = 'value1', column2 = 'value2'
WHERE condition;

-- With subquery
UPDATE Students
SET Status = 'Top Student'
WHERE IQ > (
    SELECT AVG(IQ) FROM Students
);

-- UPDATE with 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

-- Specific row
DELETE FROM students WHERE student_id = 1;

-- With multiple conditions
DELETE FROM students
WHERE geboortedatum < '2000-01-01' AND email LIKE '%@example.com';

-- All rows
DELETE FROM students;

-- With subquery
DELETE FROM students
WHERE student_id IN (
    SELECT student_id FROM registrations WHERE course_id = 2
);

Danger

DELETE FROM table without WHERE deletes all rows.

Session variables (T-SQL)

DECLARE @variable datatype;
SET     @variable = value;

-- or via SELECT
SELECT  @variable = column FROM table;
SELECT  @variable;

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

IF – ELSE

IF (@num1 > @num2)
    PRINT '1st number is greater.'
ELSE IF (@num2 > @num1)
    PRINT '2nd number is greater.'
ELSE
    PRINT 'The numbers are equal.';

-- Nested IF
IF (@num1 > @num2)
BEGIN
    PRINT '1st number is greater.';
    IF (@num1 > 75)
        PRINT 'Greater than 75.';
    ELSE
        PRINT 'Less than or equal to 75.';
END
ELSE
    PRINT 'The numbers are equal.';

CASE Statement

Usable in SELECT, WHERE, ORDER BY, GROUP BY, UPDATE and INSERT.

-- In SELECT
SELECT EmployeeName,
       CASE
           WHEN Salary >= 80000 THEN 'Director'
           WHEN Salary >= 50000 THEN 'Senior Consultant'
           ELSE 'Employee'
       END AS JobTitle
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	Behavior
`BREAK`	Exits the loop immediately
`CONTINUE`	Skips remaining instructions, goes to next iteration

Warning

CONTINUE can cause an infinite loop if the counter update comes after it.

Transactions & Rollbacks

A sequence of SQL instructions executed as one unit — fully successful or fully rolled back.

Type	Description
Autocommit	Default — each instruction is its own transaction
Implicit	Automatically started with each DML — must be closed with COMMIT/ROLLBACK
Explicit	You determine the start and end

BEGIN TRANSACTION;
-- ... instructions ...
COMMIT TRANSACTION;   -- or ROLLBACK TRANSACTION;

-- Save points — roll back part of a transaction
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;  -- only DELETE is undone
COMMIT;

-- Named transaction with mark (for point-in-time log restore)
BEGIN TRANSACTION GenerateInvoices WITH MARK N'Billing of 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;

-- Implicit transactions — T-SQL automatically starts a transaction with each DML
SET IMPLICIT_TRANSACTIONS ON;
-- ... DML ...
COMMIT;  -- or ROLLBACK — must be explicitly closed!
SET IMPLICIT_TRANSACTIONS OFF;

Auto rollback

If one instruction within a transaction fails, all changes in that transaction are automatically rolled back.

IMPLICIT_TRANSACTIONS

With SET IMPLICIT_TRANSACTIONS ON every DML instruction starts a transaction that you must always explicitly close. A forgotten COMMIT or ROLLBACK keeps the table locked.

Cursors

Processes query results row by row instead of as a set.

Cursors are slow

As long as a cursor is open, the table is locked. Replace cursors with JOINs wherever possible.

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

-- 3. Fetch first row
FETCH NEXT FROM EmpCursor INTO @EmpId, @EmpName;

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

-- 5. Close and deallocate
CLOSE      EmpCursor;
DEALLOCATE EmpCursor;

-- Replace cursor with JOIN (preferred!)
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

Basic

SELECT column1, column2 FROM table_name;
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;
SELECT column1, column2 FROM table_name WHERE condition;
SELECT column1, column2 FROM table_name WHERE c1 AND c2;
SELECT column1, column2 FROM table_name WHERE c1 OR  c2;
SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;
SELECT TOP number column1, column2 FROM table_name ORDER BY column1;
SELECT DISTINCT column1  FROM table_name;

Table name

Required when names are ambiguous — use tablename.columnname or an alias.

No LIMIT in T-SQL

MySQL uses LIMIT number [OFFSET start]. T-SQL uses TOP number (first N rows) or, for pagination, OFFSET ... ROWS FETCH NEXT ... ROWS ONLY — see TOP & OFFSET-FETCH.

WHERE Clauses

Operator	Meaning
`= < > <> >= <=`	Compare
`LIKE '%hello%'`	Pattern match
`IS NULL` / `IS NOT NULL`	Never use `= NULL`
`AND` / `OR` / `NOT`	Logical operators
`IN (a, b, c)`	Is one of these values
`BETWEEN a AND b`	Within a range

-- IN
SELECT column1, column2 FROM table_name
WHERE column1 IN (value1, value2, ...);

-- BETWEEN
SELECT column1, column2 FROM table_name
WHERE column1 BETWEEN value1 AND value2;

-- LIKE
SELECT column1 FROM table_name WHERE column1 LIKE 'pre%';   -- starts with 'pre'
SELECT column1 FROM table_name WHERE column1 LIKE '%fix';   -- ends with 'fix'
SELECT column1 FROM table_name WHERE column1 LIKE '%mid%';  -- contains 'mid'

-- IS NULL / IS NOT NULL
SELECT column1, column2 FROM table_name WHERE column1 IS NULL;
SELECT column1, column2 FROM table_name WHERE column1 IS NOT NULL;

Wildcard	Meaning
`%`	Zero or more characters
`_`	Exactly one character

Order of clauses

SELECT  ….….  AS …, …      -- which columns? table.column, SUM(...), COUNT(*) with GROUP BY
FROM    …                   -- first table (order not important)
JOIN    … ON ….…. = ….….   -- table.column when PK/FK don't share the same name
…                           -- join as many tables as you want
WHERE   …                   -- filter: =, <>, LIKE, AND, OR, ...
GROUP BY …,…                -- group records by equal column values
HAVING  …                   -- filter after grouping
ORDER BY …,…                -- sort on columns

JOIN

-- INNER JOIN (only matches in both tables)
SELECT l.Naam, kl.KlasNaam
FROM Leerlingen l
JOIN Klassen kl ON l.KlasID = kl.ID;

-- LEFT JOIN (all rows from left table, even without a match)
SELECT l.Naam, kl.KlasNaam
FROM Leerlingen l
LEFT JOIN Klassen kl ON l.KlasID = kl.ID;

-- LEFT JOIN: only rows without a match in right table
SELECT l.Naam
FROM Leerlingen l
LEFT JOIN Klassen kl ON l.KlasID = kl.ID
WHERE kl.ID IS NULL;

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

-- JOIN with 3 tables (chain of 1:N relationships)
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 without JOIN keyword
SELECT village.name
FROM village, inhabitant
WHERE village.villageid = inhabitant.villageid
AND   inhabitant.name = 'Dirty Dieter';

-- Many-to-Many relationship — requires a 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 overview

Type	Result
INNER JOIN	Only rows with a match in both tables — classic 1-N relationship
LEFT JOIN	All rows from the left table + matches from the right (NULL if no match)
OUTER JOIN	All rows from both tables (including unmatched)
CROSS JOIN	Cartesian product — every row of A paired with every row of B
UNION	Combine results from two queries (removes duplicates)

TOP & OFFSET-FETCH

-- TOP: first N rows (requires ORDER BY for deterministic results)
SELECT TOP 10 customerName, creditLimit FROM Customers ORDER BY creditLimit DESC;
SELECT TOP 10 PERCENT customerName FROM Customers ORDER BY customerName;

-- OFFSET-FETCH: pagination (requires ORDER BY)
SELECT customerName, creditLimit
FROM Customers
ORDER BY creditLimit DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
-- → skip the first 10 rows, return the next 5

LIMIT vs OFFSET-FETCH

MySQL uses LIMIT n OFFSET m, T-SQL uses OFFSET m ROWS FETCH NEXT n ROWS ONLY. Both require an ORDER BY.

GROUP BY & HAVING

-- GROUP BY
SELECT column1, COUNT(*) AS count
FROM table_name
GROUP BY column1;

-- HAVING
SELECT column1, COUNT(*) AS count
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 10;

SELECT column1, SUM(column2) AS total
FROM table_name
GROUP BY column1
HAVING SUM(column2) > 1000;

Aggregate Functions

Function	Description
`COUNT(*)`	Number of rows
`SUM(column)`	Sum
`AVG(column)`	Average
`MIN(column)`	Minimum value
`MAX(column)`	Maximum value

SELECT COUNT(*) AS total          FROM table_name;
SELECT SUM(column)  AS total       FROM table_name;
SELECT AVG(column)  AS average     FROM table_name;
SELECT MIN(column)  AS minimum     FROM table_name;
SELECT MAX(column)  AS maximum     FROM table_name;

-- With GROUP BY
SELECT column1, COUNT(*)    AS count    FROM table_name GROUP BY column1;
SELECT column1, SUM(column2) AS total    FROM table_name GROUP BY column1;
SELECT column1, AVG(column2) AS average  FROM table_name GROUP BY column1;

-- Multiple aggregations in one query (scalar subqueries)
SELECT
    (SELECT COUNT(*) FROM Liedjes2)                                     AS Song_Count,
    (SELECT TOP 1 Duurtijd FROM Liedjes2 ORDER BY Duurtijd DESC)        AS Longest_Song,
    (SELECT SUM(Duurtijd) FROM Liedjes2 WHERE Artiest = 'U2')           AS Total_Duration;

String Functions & Formatting

String functions

Function	Description	Result
`LEN(str)`	Length of a string	`LEN('example')` → `7`
`CONCAT(s1,s2,...)`	Concatenate strings	`CONCAT('exam','ple')` → `'example'`
`SUBSTRING(str,pos,len)`	Part of a string	`SUBSTRING('example',1,4)` → `'exam'`
`UPPER(str)`	Convert to uppercase	`UPPER('example')` → `'EXAMPLE'`
`LOWER(str)`	Convert to lowercase	`LOWER('EXAMPLE')` → `'example'`
`TRIM(str)`	Trim spaces	`TRIM(' example ')` → `'example'`
`REPLACE(str,from,to)`	Replace part	`REPLACE('exam-ple','-','')` → `'example'`
`CHARINDEX(sub,str)`	Position of substring	`CHARINDEX('amp','example')` → `3`
`LEFT(str,n)`	n characters from the start	`LEFT('example',4)` → `'exam'`
`RIGHT(str,n)`	n characters from the end	`RIGHT('example',3)` → `'ple'`
`COALESCE(a,b,...)`	First non-NULL value	`COALESCE(NULL, NULL, 'a')` → `'a'`
`GETDATE()`	Current date and time (T-SQL)	—

LENGTH/INSTR do not exist in T-SQL

MySQL uses LENGTH(str) and INSTR(str, sub). T-SQL uses LEN(str) and CHARINDEX(sub, str) — note the reversed order of arguments in CHARINDEX (search term first, then the string).

SELECT naam, LEN(naam) AS name_length FROM students;
SELECT CONCAT(voornaam, ' ', achternaam) AS full_name FROM students;
SELECT email FROM students WHERE email LIKE '%@example.com';
SELECT REPLACE(product_naam, '-', ' ') AS product_name FROM products;
SELECT LEFT(naam, 3) AS initials FROM students;
SELECT RIGHT(email, 10) AS domain    FROM students;
SELECT CHARINDEX('oor', naam) AS position FROM students;

Rounding & Formatting

Function	Output	Rounding	Use
`ROUND(n, d)`	Numeric	Yes	Calculations
`ROUND(n, d, 1)`	Numeric	No — truncate	Financial (exact truncation)
`FORMAT(n, d)`	String	Yes	Display in reports

No TRUNCATE() function in T-SQL

MySQL has a separate TRUNCATE(n, d) function. T-SQL uses the same ROUND() function with a third parameter: any value ≠ 0 disables rounding and simply truncates.

SELECT ROUND(1234.56789, 2);             -- 1234.57
SELECT ROUND(AVG(Score), 1) AS RoundedAverage FROM Enrollments;

SELECT ROUND(1234.56789, 2, 1);          -- 1234.56 (truncated, not rounded)
SELECT ROUND(SUM(Amount), 0, 1) AS TotalAmount FROM Invoices;

SELECT FORMAT(1234.56789, 2);            -- '1,234.57' (string!)
SELECT FORMAT(SUM(Price), 2) AS TotalAmount FROM Purchases;

FORMAT — custom format strings & culture

FORMAT(value, format, culture) uses .NET custom format strings, not a proprietary T-SQL syntax. Each character in the format string has a fixed meaning:

Token	Meaning
`0`	Required digit — shows `0` if there is no digit
`#`	Optional digit — shows nothing if there is no digit (no leading zeros)
`,` (comma)	Thousands separator — only when placed immediately left of `0`/`#` or the decimal placeholder
`.` (period)	Decimal placeholder
literal characters (space, `€`, `%`, ...)	Are taken over exactly at that position

# ##0.00 is wrong

A space between the # characters is not a thousands separator — it is simply a literal space at that one fixed position, and only works by coincidence for numbers with exactly 4 digits before the decimal. The thousands separator must be indicated with a comma:

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

The comma in the format string is only a signal ("group here") — the character that is actually displayed (., ,, or a space) depends on the specified culture (3rd parameter), not on what you type.

-- More examples
SELECT FORMAT(1234567.891, '#,##0.00');             -- '1,234,567.89'  (default culture = en-US)
SELECT FORMAT(0.256, '0.0%');                        -- '25.6%'         (0 forces the digit)
SELECT FORMAT(7, '000');                              -- '007'
SELECT FORMAT(SUM(Prijs), '#,##0.00 €', 'nl-BE') AS TotalAmount FROM Purchases;

Date Functions

Function	Description	Example
`GETDATE()`	Current date and time	`GETDATE()` → `2026-08-14 10:30:00`
`SYSDATETIME()`	Higher precision than GETDATE	—
`DATEADD(part, n, date)`	Increase/decrease a date	`DATEADD(DAY, 10, GETDATE())`
`DATEDIFF(part, start, end)`	Difference between two dates	`DATEDIFF(DAY, orderDate, shippedDate)`
`DATEPART(part, date)`	Numeric part of a date	`DATEPART(MONTH, GETDATE())` → `8`
`YEAR(date)`	Year as integer	`YEAR(GETDATE())` → `2026`
`MONTH(date)`	Month as integer	`MONTH(GETDATE())` → `8`
`DAY(date)`	Day as integer	`DAY(GETDATE())` → `14`
`CONVERT(type, value, style)`	Convert data type (with formatting)	`CONVERT(VARCHAR, GETDATE(), 103)` → `'14/08/2026'`
`CAST(value AS type)`	Convert data type (without formatting)	`CAST(GETDATE() AS DATE)` → `2026-08-14`
`FORMAT(value, format)`	Display as string	`FORMAT(GETDATE(), 'dd/MM/yyyy')` → `'14/08/2026'`

`part` values	Meaning
`YEAR` / `YY`	Year
`MONTH` / `MM`	Month
`DAY` / `DD`	Day
`HOUR` / `HH`	Hour
`MINUTE` / `MI`	Minute
`SECOND` / `SS`	Second

-- Delivery time per order
SELECT orderNumber,
       DATEDIFF(DAY, orderDate, shippedDate) AS DeliveryDays
FROM Orders
WHERE shippedDate IS NOT NULL;

-- Orders from the last 30 days
SELECT * FROM Orders
WHERE orderDate >= DATEADD(DAY, -30, GETDATE());

-- Format date as string
SELECT FORMAT(orderDate, 'dd MMMM yyyy', 'nl-BE') AS OrderDate
FROM Orders;

-- Calculate required date
DECLARE @OrderDate DATE = '2026-08-14';
DECLARE @RequiredDate DATE = DATEADD(DAY, 10, @OrderDate);

Subqueries

-- In WHERE with IN
SELECT Naam FROM Students
WHERE ID NOT IN (
    SELECT StudentID FROM Enrollments
);

-- In WHERE with EXISTS
SELECT v.Naam FROM Courses v
WHERE EXISTS (
    SELECT 1 FROM Enrollments i
    WHERE i.CourseID = v.ID AND i.Score > 90
);

-- Correlated subquery: student with highest IQ per class
SELECT s.Naam, k.Naam AS Class, s.IQ
FROM Students s
JOIN Classes k ON s.ClassID = k.ID
WHERE s.IQ = (
    SELECT MAX(s2.IQ) FROM Students s2
    WHERE s2.ClassID = s.ClassID
);

-- HAVING with subquery: courses below the overall average
SELECT v.Naam, AVG(i.Score) AS AverageScore
FROM Courses v
JOIN Enrollments i ON v.ID = i.CourseID
GROUP BY v.ID
HAVING AVG(i.Score) < (
    SELECT AVG(Score) FROM Enrollments
);

-- SELECT in SELECT: highest IQ in the class with the most students
SELECT Naam, IQ
FROM Students
WHERE ClassID = (
    SELECT TOP 1 ClassID FROM Students
    GROUP BY ClassID
    ORDER BY COUNT(*) DESC
)
AND IQ = (
    SELECT MAX(IQ) FROM Students
    WHERE ClassID = (
        SELECT TOP 1 ClassID FROM Students
        GROUP BY ClassID
        ORDER BY COUNT(*) DESC
    )
);

Relationships

Type	Description
Primary Key	Unique identifier for a row
Foreign Key	Reference to a column in another table
Many-to-Many	Junction table with Foreign Keys to both tables

Views, Stored Procedures & Triggers

Views

Stored SELECT query, accessible as a virtual table.

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;

Option	Effect
`WITH SCHEMABINDING`	Underlying tables cannot change structure as long as the view exists
`WITH CHECK OPTION`	INSERT/UPDATE through the view fails if the record does not satisfy the WHERE condition

-- WITH CHECK OPTION example
CREATE VIEW dbo.Motors
AS
    SELECT productCode, productName, productLine, buyPrice, MSRP
    FROM   products
    WHERE  productLine = 'Motorcycles'
    WITH CHECK OPTION;
GO
-- An INSERT with productLine = 'Classic Cars' through this view produces an error

Modifying views & permissions

-- Modify view — rewrites the complete definition
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];    -- grant
DENY   SELECT ON dbo.PersoneelKantoor TO [Fanny];    -- explicitly deny
REVOKE SELECT ON dbo.PersoneelKantoor FROM [Fanny];  -- revoke

-- Permission to modify the view itself (structure), separate from read permissions
GRANT ALTER ON dbo.PersoneelKantoor TO [JuniorDBA];

ALTER vs DROP + CREATE

ALTER preserves existing permissions (GRANT/DENY) and dependent objects of a view/procedure/function. DROP followed by CREATE loses them — permissions must then be re-granted.

Stored Procedures

Stored set of T-SQL instructions with parameters — write once, call anywhere.

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

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

-- With condition check
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

-- Modify procedure (permissions and dependencies are preserved)
ALTER PROCEDURE dbo.GetCustomersFromCountry
    @country NVARCHAR(100)
AS
BEGIN
    SELECT customerName, phone
    FROM   customers
    WHERE  country = @country
    ORDER BY customerName;
END;
GO

-- Permissions on a procedure
GRANT  EXECUTE ON dbo.GetCustomersFromCountry TO [Fanny];    -- grant
DENY   EXECUTE ON dbo.GetCustomersFromCountry TO [Fanny];    -- explicitly deny
REVOKE EXECUTE ON dbo.GetCustomersFromCountry FROM [Fanny];  -- revoke

-- Permission to modify the procedure itself
GRANT ALTER ON dbo.GetCustomersFromCountry TO [JuniorDBA];

Functions

A scalar function returns one value and — unlike a procedure — is usable in the middle of a query.

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

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

-- Modify function
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];    -- grant
DENY   EXECUTE ON dbo.GetCreditLimitCustomer TO [Fanny];    -- explicitly deny
REVOKE EXECUTE ON dbo.GetCreditLimitCustomer FROM [Fanny];  -- revoke

Procedure vs function

A procedure performs actions (INSERT, UPDATE, DELETE) and does not return a value. A function calculates and returns a value — usable in SELECT, WHERE, and other expressions.

Triggers

Automatically executed on an INSERT, UPDATE, or DELETE on a table.

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;

Virtual table	Contains
`inserted`	The new rows (on INSERT and UPDATE)
`deleted`	The old rows (on DELETE and UPDATE)

-- Modify trigger (rewrite complete definition)
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

-- Temporarily disable/enable without dropping
DISABLE TRIGGER dbo.Payments_CheckAmount ON dbo.Payments;
ENABLE  TRIGGER dbo.Payments_CheckAmount ON dbo.Payments;

Permissions on triggers

Triggers execute automatically — there is no EXECUTE permission on a trigger like there is for a procedure. Permission management goes via ALTER on the table/trigger itself (e.g. GRANT ALTER ON dbo.Payments TO [JuniorDBA];).

Import & Export

Tool	Type	Use
bcp	Command-line	Fast import/export of flat files
BULK INSERT	T-SQL statement	Import from file directly in T-SQL
Import and Export Wizard	GUI (SSMS)	Simple import/export via wizard
SSIS	ETL platform	Complex import/export with transformations and flows

!!! note "Only possible on the SQL server itself; if working with gMSA make sure to be logged in as domain admin.

Getting files onto a Core server (no GUI/drag-and-drop)

On a Core server there is no Explorer to drag a local file to. Approach: first drag the file from your own PC to a network share, then copy it from the Core server locally using Copy-Item.

# On the Core server — copy file from share to local disk
Copy-Item "\\ADServJBE.DatabJBE.local\Share\05_Dept_emp.txt" -Destination "C:\temp\05_Dept_emp.txt"

UNC path

\\server\share\file.ext — the syntax above is correct. Replace ADServJBE.DatabJBE.local and Share with the actual server name/share name in your environment, and verify that the 05_Dept_emp.txt file is actually on the share before copying.

bcp

-- Export: table to file
bcp DatabaseName.dbo.TableName out file.csv -S ServerName -T -t ":"

-- Import: file to table
bcp DatabaseName.dbo.TableName in file.csv -S ServerName -T -t ";"

-- Example: export view to csv, character format, comma as delimiter
bcp ClassicModels.dbo.PersoneelKantoor out C:\temp\test.csv -c -t "," -S localhost -T

Option	Meaning
`out`	Export from table
`in`	Import to table
`-S`	Server name
`-T`	Windows authentication (trusted connection)
`-t`	Delimiter
`-c`	Character format (character data type) — readable csv file

# Check file after export
cd c:\temp

BULK INSERT

BULK INSERT dbo.TableName
FROM 'C:\path\to\file.csv'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2        -- skip the header row
);

Import and Export Wizard

Object Explorer → right-click on database → Tasks → Import Data... / Export Data...

SSIS — core components

Component	Function
Control Flow	Sequence of execution (containers, tasks)
Data Flow	Processes the data (read, transform, write)
Flat File Source	Reads data from a flat file (CSV, TXT)
OLE DB Destination	Writes data to a SQL Server table
Lookup	Looks up a value in another table
Derived Column	Calculates or transforms a column value
Loop Container	Executes a set of tasks repeatedly (e.g. per file)

Maximum insert commit size

Set to 100000 for large imports for better performance.

Data type conversion

Fields like birthdate in a CSV are often text — use a Derived Column with substring and concatenation to convert them to the correct date data type.

Performance & Indexing

Indexes make lookups faster but cost storage space and slow down write operations. Without an index = full table scan.

Design guidelines

Index columns that frequently appear in JOIN or WHERE
Limit indexes with multiple columns
Avoid indexing small tables (overkill)
Avoid indexing very large tables with many inserts/updates

Types of indexes

Type	Description
Heap	No index — data unorganized, fragments quickly
Clustered	Data sorted and stored in a B-tree — max 16 columns, key max 900 bytes
Nonclustered	Separate tree structure with references to the data (RID lookup)

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

-- Composite index — multiple columns
-- Place the column with the highest selectivity first
CREATE NONCLUSTERED INDEX idx_nc_lastname_firstname
    ON dbo.Employees(lastName, firstName);

-- Filtered index — only a subset of rows
CREATE NONCLUSTERED INDEX idx_nc_active
    ON dbo.Employees(lastName)
    WHERE isActive = 1;

-- Index with INCLUDE columns (covering index — prevents key lookup)
CREATE NONCLUSTERED INDEX idx_nc_lastname_incl
    ON dbo.Employees(lastName)
    INCLUDE (firstName, salary);

Column order in composite index

Put the most selective column (most unique values) first — the optimizer then filters most effectively. Columns in INCLUDE are not sorted but are stored in the leaf pages.

Fragmentation

Solution	Description
`FILLFACTOR`	% of a page that may be filled before splitting
`PAD_INDEX`	Applies FILLFACTOR also to intermediate index pages

ALTER INDEX ALL ON dbo.Employees REORGANIZE;  -- reduce fragmentation
ALTER INDEX ALL ON dbo.Employees REBUILD;      -- full rebuild

Using indexes

The query optimizer determines itself whether an index is used, based on how the WHERE clause is formulated. The execution plan shows how data was looked up (Index Scan, Index Seek, ...).

Tip

Use IN (value1, value2) or = value instead of OR or calculations on the column — the optimizer then more often chooses an index.

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

-- Query index usage
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');

Testing caching

CHECKPOINT;                 -- 1. Write dirty pages to disk
DBCC DROPCLEANBUFFERS;      -- 2. Clear memory buffer
DBCC FREEPROCCACHE;         -- 3. Remove execution plans from memory

Statistics

SQL Server automatically maintains statistics about the value distribution in columns. The query optimizer uses these to choose the best execution plan.

-- Manually update statistics (after large bulk operations)
UPDATE STATISTICS dbo.Employees;

-- Update all statistics in a database
EXEC sp_updatestats;

-- View statistics of an index
DBCC SHOW_STATISTICS('dbo.Employees', 'idx_nc_lastname');

-- Check automatic update setting
SELECT name, is_auto_update_stats_on FROM sys.databases WHERE name = DB_NAME();

Tip

After large bulk inserts or updates always run UPDATE STATISTICS — outdated statistics lead to poor execution plans.

Query history

Method	Description
`sys.dm_exec_requests`	Currently running queries
`sys.dm_exec_query_stats`	Statistics of cached query plans
`sys.dm_exec_sql_text`	Text of cached SQL queries
`sys.dm_exec_query_plan`	Execution plan of cached queries
Activity Monitor	Graphical tool in SSMS
SQL Server Profiler	Trace tool
Query Store	Built-in query history (from SQL Server 2016)

-- Running 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 (from SQL Server 2016)

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

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

Automation — SQL Server Agent

Central service for automating management tasks — every instance has one.

Component	Description
SQL Server service	The database engine itself
Windows Application Log	Logs system events; Agent reads this
SQL Server Agent service	Executes automated tasks, responds to events

Automates: Jobs (steps, schedules), Alerts (monitor events), Operators (notification recipients), 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';

-- Minimal operator — name + email address only (e.g. lab exercise "Lambik")
EXEC msdb.dbo.sp_add_operator
    @name = N'Lambik',
    @enabled = 1,
    @email_address = N'your.name@ap.be';

Required: Database Mail

An operator only actually receives emails if Database Mail is configured and linked to SQL Server Agent (see Database Mail) — creating the operator alone is not sufficient.

Via the GUI: SQL Server Agent → right-click Operators → New Operator... → Name = Lambik, E-mail name = your AP email address → OK.

Alerts

Responds to an event in the Windows Application Log or a performance condition.

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...

Tab	Content
General	Name, owner, category
Steps	Type (T-SQL, CmdExec, application), database, command; behavior on success/failure
Schedules	One-time, recurring, or on startup
Alerts	Link alerts to the job
Notifications	Notify operator + action on completion
Targets	Local server or target servers (multi-server)

Job history: sysjobhistory in msdb — max 1000 rows total, max 100 rows per job.

Typical automated tasks: backups (database + log), data transfer, index rebuilds, data integrity checks, shrinking databases.

Database Mail

SQL Server sends emails via SMTP — useful for job failures or alerts.

Requires membership of role DatabaseMailUserRole in msdb
Can send query results and attachments; stores copies in msdb

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

Multi-server environments

Master server manages and distributes jobs to target servers — one central DBA instance for all instances.

Maintenance Plans

Graphical wizard in SSMS to schedule recurring maintenance tasks without manual T-SQL.

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

Task	Description
Back Up Database	Perform full, differential, or log backup
Rebuild Index	Fully rebuild indexes (`REBUILD`) — removes fragmentation
Reorganize Index	Light defragmentation (`REORGANIZE`) — database stays online
Update Statistics	Update statistics after major changes
Check Database Integrity	Run `DBCC CHECKDB`
Shrink Database	Reduce database file size (use sparingly!)
Cleanup History	Clean up history of jobs, backups, and maintenance plans
Execute T-SQL Statement	Run a free T-SQL block as a maintenance step

Maintenance Plan vs. Agent Job

A Maintenance Plan automatically generates Agent jobs in the background. Use the wizard for standard tasks; write your own Agent jobs for more complex logic or conditional steps.

Shrink Database

Shrink moves pages and drastically increases index fragmentation. Use it only for exceptionally high free space — always schedule an index rebuild afterwards.

Example (lab): Full backup maintenance plan

Object Explorer → Management → right-click Maintenance Plans → New Maintenance Plan... → give a name → drag the desired tasks onto the designer canvas.

1. Back Up Database Task

Setting	Value
Name	`ClassicModels Full Backup maintenance`
Backup type	Full
Database(s)	`ClassicModels`
Destination	`D:\Microsoft SQL Server\Backup` — check Create a sub-directory for each database
Option	Check Verify backup integrity

2. Maintenance Cleanup Task

Setting	Value
Maintenance type	Backup files
File location	same destination folder as the backup task
File extension	`bak`
File age	older than 2 weeks

3. Notify Operator (as a task in the designer, or via the underlying Agent Job → Notifications tab)

Setting	Value
Operator	`Lambik`
When	When the job fails — only on a failed complete task

4. Schedule — Calendar icon at the top of the designer, or via the Agent Job → Schedules tab

Occurs: Daily
Daily frequency: Occurs every 12 hour(s), starting at 06:00:00, ending at 18:00:00
→ result: exactly 2 executions per day, at 6:00 and 18:00

Example (lab): Transaction log backup maintenance plan

1. Back Up Database Task (Transaction Log)

Setting	Value
Name	`classicModels Transaction Backup maintenance`
Backup type	Transaction Log
Database(s)	`ClassicModels`
Destination	`D:\Microsoft SQL Server\Backup` — check Create a sub-directory for each database
Option	Check Verify backup integrity

2. Maintenance Cleanup Task

Setting	Value
Maintenance type	Backup files
File extension	`trn`
File age	older than 3 days

3. Notify Operator

Setting	Value
Operator	`Lambik`
When	only when the transaction log backup fails

4. Schedule

Occurs: Daily
Daily frequency: Occurs every 1 hour(s), starting at 07:00:00, ending at 17:00:00
→ result: every hour between 7:00 and 17:00

Order of tasks in the designer

Connect the tasks with a precedence arrow (backup task → cleanup task) so that cleanup only starts after a successful backup — otherwise you risk immediately cleaning up recently created backups if the file age is incorrectly configured.

Migration & Scalability

Data migration = moving data between systems — "system" encompasses application + DBMS + OS + storage.

Note

Data lives much longer than the system in which it is stored.

Schema version control

Approach	Description
State based	Application contains schema, tool compares and executes `ALTER`
Migration based	Application contains migration scripts

Migration scenarios

Method	Description
Backup & restore	Backup on source, restore on target
Detach & attach	Detach database and reattach on another instance
Copy database	Wizard to copy a database to another instance
Generate scripts wizard	Script to transfer a database (logical backup)

-- Detach — only if not replicated/mirrored and no snapshots
EXEC sp_detach_db @dbname = [AdventureWorks2019];

-- Attach — only if all files are present
CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'D:\SQLServer\AdventureWorks_Data.mdf'),
       (FILENAME = 'D:\SQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;

Copy Database (SSMS: Tasks → Copy Database...): original remains available during transfer — via detach/attach (faster, offline) or SQL Management Object (slower, stays online).

Scale UP vs Scale OUT

Strategy	Meaning
Scale UP	Add more resources to existing server (RAM, CPU, ...)
Scale OUT	More servers — mirroring, replication, Always On

Database Mirroring

High availability for one database via a synchronized copy.

Principal sends transactions to the mirror (warm/hot standby)
Witness server needed for automatic failover
Only with full recovery model; mirror database must have identical name, first restore with NORECOVERY

Mode	Data transfer
High-safety	Synchronous
High-performance	Asynchronous

Database Replication

Copy data to another instance with synchronization — asynchronous, based on publisher–distributor–subscriber.

Role	Description
Publisher	Contains the source database
Article	Database objects in the replication
Publication	Collection of articles
Distributor	Receives transactions, distributes to subscribers
Subscriber	Receives the changed data
Subscription	Request to receive a publication

Type	Created on	Who initiates
Push	Publisher server	Publisher sends data
Pull	Subscriber server	Subscriber starts the process

Replication type	Description	Ideal for
Transactional	Transaction log, one-direction	Fast updates at subscriber
Snapshot	Copies all published data	Infrequent changes, small volumes
Merge	All directions, including subscriber changes	Offline updates to be synchronized
Peer-to-peer	Every server = publisher + distributor + subscriber	Distributed data, low conflict probability

Configuration (SSMS): Configure Distribution → New Publication → New Subscription. Monitoring via Replication Monitor (requires sysadmin or replmonitor).

SQL Server Always On

Technology	Description
AlwaysOn AG (Availability Groups)	Database-level HA — no shared storage
AlwaysOn FCI (Failover Cluster Instances)	Instance-level HA — requires shared storage

Failover Cluster Instances	Availability Groups
Shared storage (iSCSI/Fibre Channel SAN)	No shared storage
Instance-level HA	Database-level HA
Logins, jobs, certs intact after failover	Must be manually added to secondaries
Active/Passive nodes	DR replica can be Active Secondary (read-only)
Connect via virtual server name	Connect via AG listener name
No protection against I/O failure	Protection against I/O failure
No recovery model requirements	Databases must be in FULL recovery model

Reporting Services (SSRS)

Web-based tool for interactive reports based on SQL Server data — accessible via http://[server]/Reports.

Reports created via Report Builder or Visual Studio (SSDT)
Data via views and stored procedures
Supports parameters, including cascading parameters (parameter 2 depends on the value of parameter 1)
Exportable to PDF, Excel, Word, ...

Workflow

1. Prepare data in SQL Server (views/stored procedures)
2. Create report in Report Builder / Visual Studio
   → data source, dataset, parameters, layout
3. Publish report to the Reporting Services web server
4. Access report via browser: http://[server]/Reports

Cascading parameters — example

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

-- Dataset for parameter "flight" (depends on selected airport)
SELECT f.flight_id,
       f.flightno + ' - ' + a_dep.name + ' - ' + a_arr.name AS flight
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

General strategy

Define the problem — symptoms vs. expected behavior, facts only, since when?
Reproduce — preferably in a test environment, without risk
Gather information — read the error message carefully, search logs from all subsystems
Find the cause — checklist (check most frequent causes) or bisecting (systematically verify where it still works)
Try a solution — watch for side effects; if it doesn't work: undo it

Workaround ≠ fix

If the problem disappears after a restart, you have a workaround, not a fix. Keep looking for the root cause.

Common errors

Error	Check
Can't connect to SQL Server	Is the service running? Network? Firewall?
Access denied	Login, user, password, permissions correct?
Too many connections	Number of active vs. max connections

-- Active processes / connections
EXEC sp_who2;
SELECT @@CONNECTIONS, @@MAX_CONNECTIONS;

-- Connections 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;

Tools

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

Error Log: SSMS → Management → SQL Server Logs → Current
Activity Monitor: right-click on server → Activity Monitor
Dynamic Management Views (DMVs): monitor server state, diagnose problems, tune performance

-- Overview of available DMVs
SELECT * FROM sys.system_objects WHERE type = 'V' AND name LIKE 'dm_%';

T-SQL Cheatsheet

Table of Contents

Planning & Installation

Reference architecture (lab)

Separating storage logically

Installation order

Things to determine before installation

Basic verification after installation

Common Issues

Installation on Windows Server Core (silent)

Mixed mode + enabling sa (after silent install)

Firewall & DNS

SQL Server: Processes & Databases

Services

System databases

Planning a new database

Create database

Modify database

Drop database

Recovery models

Database Snapshots

Contained Databases

Logins, Users & Permissions

Types

Create login

Create user

Server Roles

Database Roles

Granular permissions

Permissions per action

Service Accounts — sMSA / gMSA

Create gMSA

Configure

Backup & Restore

Backup types

Backup schedule (example)

Full & Differential backup

Transaction log backup

Backup devices (named alias for a backup file)

Download sample database (e.g. AdventureWorks)

Restore

Recovery: automatic vs manual

Tips

SELECT Execution Order

Datatypes

Numbers

Text, Date & Other

Custom data type

Constraints

NULL values

DDL — Data Definition Language

Schema & temporary tables

CREATE TABLE

DROP TABLE

ALTER TABLE

Table info & rename (T-SQL)

Data Integrity

Foreign Key — referential integrity

DML — Data Manipulation Language

INSERT

UPDATE

DELETE

Session variables (T-SQL)

IF – ELSE

CASE Statement

WHILE Loop

Transactions & Rollbacks

Cursors

DQL — SELECT

Basic

WHERE Clauses

Order of clauses

JOIN

Join types overview

TOP & OFFSET-FETCH

GROUP BY & HAVING

Aggregate Functions

String Functions & Formatting

String functions

Rounding & Formatting