Aanbevelingen voor SQL Server-ontwikkeling

Hier volgt een checklist voor het ontwikkelen van een SQL Server-applicatie. Deze lijst kan je gebruiken bij het zelf ontwikkelen van software, en ook bij het beoordelen van software van derden, bijvoorbeeld in het kader van pakket-evaluatie. Onderstaande regels dragen bij aan een goed onderhoudbare en goed presterende applicatie met een optimale afgedwongen kwaliteit van data.

Wat betreft mijn diensten, ik doe geen lange ontwikkeltrajecten, maar ik kan wel heel goed een ontwikkelteam bijscholen of adviseren bij het modelleren en de bijbehorende algoritmiek en code. De ervaring leert dat hoe eerder ik erbij betrokken word, hoe beter het resultaat en hoe minder werk er hoeft te worden overgedaan. Een frisse blik van buitenaf kan een systeem zeer ten goede komen. Dit is vooral het geval bij het aanpassen van bestaande software. Sommige ideeën over hoe het datamodel kan worden verbeterd, leven mogelijk al jaren, maar zijn misschien net niet optimaal.

De typische duur van een opdracht is één of enkele dagen. Kennisoverdracht staat altijd centraal. Zie ook de mogelijkheid van een in-house SQL Server crash course.

Planning en ontwerp

Als je een SQL Server-toepassing gaat ontwikkelen, zorg dan dat je goed op de hoogte bent van de mogelijkheden die het product biedt. Onderstaande features moet je in een vroegtijdig stadium overwegen omdat het later alsnog gebruiken grote aanpassingen vereist. Evalueer je een pakket van een derde, dan krijg je een idee van het kennisniveau van de leverancier, en in hoeverre de applicatie aangepast is aan nieuwe features van SQL Server.

Hier volgt een incomplete lijst van features die je enigszins moet kennen en begrijpen, respectievelijk waarvan je wilt dat de leverancier van een pakket ze waar toepasselijk gebruikt heeft.

de system supplied data types en de mogelijkheid zelf in .NET types te definiëren
nullability en defaults, computed columns, sequences en de identity property
primary keys en unique constraints, foreign keys en check constraints
stored procedures, scalar en table valued functions, DML triggers, CLR integration, views
transactions en isolation levels, row versioning
server en database triggers
(read only) filegroups, table partitioning en het weggooien of verplaatsen van partities
file tables, temporal tables, external tables, synoniemen
logins, database-users, server- en database rollen, applicatie rollen, schema's
row level security, encryption, dynamic data masking
full text search
in memory OLTP
service broker
replicatie

Modellering en database design

Let op: Het onderstaande geldt ook wanneer je code first ontwikkelt met een ORM zoals Entity Framework.

Bij het modelleren en creëren van databases en tabellen is het volgende aanbevolen.

Geef kwaliteit, begrijpelijkheid en onderhoudbaarheid van je data(model) de hoogste prioriteit.
Normaliseer; zorg dat ieder atomair feit precies één keer in de database voorkomt. Voorkom redundantie, anders dan redundatie die door SQL Server zelf gemanaged wordt zoals persisted computed columns, nonclustered indexes en indexed views.
Vermijd meerdere tabellen met dezelfde primary key.
Denormalisatie of het dupliceren van data op een andere server zijn laatste redmiddellen. Doe dit niet voordat je de andere mogelijkheden voor verbetering van performance of schaalbaarheid hebt uitgeput. De complexiteit die je introduceert zal je de rest van de levensduur van de applicatie achtervolgen.
Gebruik in principe voor een applicatie één database, dat is het eenvoudigst voor databasebeheer, versiebeheer van code, high availability en programmering. Gebruik eventueel schema's, row level security, (read only) filegroups, table partitioning, etc.
Het goed gebruik van datatypes en nullability vereenvoudigt je programmacode aanzienlijk, helpt performance en maakt het datamodel inzichtelijk voor anderen. Gebruik altijd het minimaal geschikte datatype, bijvoorbeeld:
- geen int of char(1), maar bit voor een boolean
- geen datetime, maar date voor een datum
- geen varchar, maar date, time of datetime voor een datum en/of tijd
- geen int maar smallint voor een jaartal en tinyint voor een kwartaal, alhoewel dit meestal afgeleide (computed) waardes zullen zijn
- geen varchar, maar char voor waardes van vaste lengte zoals een landcode
- geen nvarchar(4000), maar bijvoorbeeld nvarchar(200) voor een persoonsnaam; de opgegeven lengte van een datatype kan consequenties hebben voor het geschat benodigd geheugen van een query
Gebruik geen leading zero's zoals in '0001' of andere prefixes. Doe dat indien nodig in een computed kolom, en gebruik voor de data gewoon een numeriek datatype.
Zet nooit twee attributen in één kolom, bijvoorbeeld bedrijfscode en volgnummer 'CORP-0001'. Gebruik twee kolommen en voeg ze eventueel samen in een computed kolom.
Weersta de verleiding om kolommen te misbruiken, zoals code -1 betekent 'niet gebruikt'. Specificeer in plaats daarvan een extra kolom.
Overweeg het systematisch gebruik van unicode in plaats van ASCII. Daarmee zijn alle problemen met betrekking tot ASCII code-pages en het vergelijken van data afkomstig uit verschillende landen verleden tijd. Dit ten koste van een dubbele hoeveelheid opslag voor character data, twee bytes in plaats van één per teken.
Vermijd het gebruik van (n)text en image, gebruik in plaats daar van (n)varchar(max) en varbinary(max).
Gebruik hierarchyid om hiërarchische relaties tussen records in één tabel vast te leggen. Je kunt gebruik maken van een aantal handige voorgedefinieerde functies.
Wees strict met nullability, gebruik eventueel defaults. Zorg wel dat je de logica van nulls zeer goed begrijpt.
Maak maximaal gebruik van key, referentiële en check constraints en garandeer data-integriteit. Programmeren tegen de data wordt daarmee ook makkelijker, immers aannames over regelmatigheden hoef je niet meer eerst te testen, en uitzonderingen (orderregels zonder order, niet-unieke BSN's) hoef je niet te behandelen. Daarnaast kunnen constraints ook goed zijn voor de performance.
- Iedere tabel heeft een primary key. Gebruik betekenisloze sleutels, waarvoor dus nooit een reden is die te updaten. Incidenteel zijn ook betekenisvolle sleutels acceptabel, bijvoorbeeld een landcode char(2) volgens de ISO standaard.
- Koppeltabellen met referenties naar betekenisloze sleutels kunnen een samengestelde sleutel krijgen en behoeven niet zelf nog een extra betekenisloze sleutel. Voor begrijpelijkheid en performance is dit het beste. De volgorde van de kolommen in de sleutel kan wel van belang zijn.
- Definieer unique keys waar relevant.
- Gebruik foreign keys voor referentiële integriteit. Overweeg cascade delete voor FK's waar dat zinvol en veilig is, bijvoorbeeld doorgaans kan een rij uit een koppeltabel wel verwijderd worden als de gerefereerde rij wordt verwijderd.
- Gebruik check constraints om de waardes binnen kolommen verder te beperken.
- Let er op dat foreign keys (en check constraints) trusted zijn en blijven. Verifiëer dit middels de catalog views sys.foreign_keys en sys.check_constraints.
Hanteer een naamgevingsconventie, let daarbij op
- gebruik van verklarende namen
- al of niet prefixen van namen met het objecttype, bijv 'tbl', 'vw', 'usp' etc. ('sp_' is bedoeld voor procedures in de master-database, gebruik deze prefix dus niet, net zo min als 'xp_', 'fn_', 'ms_' of 'if_')
- een werkwoord in procedurenaam, doe of laat het consequent
- tabelnamen in meervoud/enkelvoud
- gebruik van underscores, spaties, hoofdletters, 'CamelCase'
- namen van key-kolommen, refererende kolommen, constraints
- Engels of Nederlands. Reserved words zijn altijd te omzeilen met [ ], als in create table dbo.[order].

Programmeerconstructies

Voor het schrijven van duidelijke, correcte, onderhoudbare en goed presterende T-SQL code is het volgende aanbevolen.

Bepaal een consistente manier om de database te benaderen, bijvoorbeeld via stored procedures, ook voor selects. Dit heeft een aantal voordelen:
- De T-SQL code staat op één plaats, in de database en niet in de applicatiecode.
- Hergebruik van code volgens de principes van low coupling, high cohesion is eenvoudig.
- Stored procedures presteren in de meeste gevallen beter dan ad hoc queries.
- Beveiliging kan goed geregeld worden door alleen rechten te geven op de procedures en niet op de onderliggende tabellen.
Kies je niet voor stored procedures, parametriseer statements dan met SqlCommand.Parameters.Add() (in c#). Zo voorkom je dat je veel gelijkvormige statements naar SQL Server stuurt die allemaal apart gecompileerd worden.
Gebruik set based routines in plaats van procedurele code. Gebruik loops (inclusief cursors) alleen wanneer echt nodig, bijvoorbeeld bij het verzenden van mails of het schrijven van bestanden. Loops zijn één van de grootste performance-killers in relationele databases. Bovendien maken ze je code onnodig lang en ingewikkeld. Soms kunnen loops vermeden worden door te joinen met een 'nums' tabel. Ook table valued parameters kunnen soms worden gebruikt om loops te voorkomen. Geef vanuit de client-applicatie een lijst van waardes niet door als een string; gebruik een table valued parameter.
Schrijf in één query wat in één query geschreven kan worden. Maak daartoe bijvoorbeeld gebruik van common table expressions (cte's) en de output clause. Het conceptueel in stappen verdelen van een query gaat heel goed met cte's en is efficiënter dan het gebruik van tijdelijke tabellen om tussenresultaten op te slaan, omdat een cte samen met de rest van het statement wordt geoptimaliseerd en uitgevoerd.
Als je tijdelijke tabellen gebruikt, geef ze dan een primary key en hanteer verder de eisen die je ook aan gewone tabellen stelt (datatypes, nullability, constraints).
Gebruik geen tabelvariabelen voor grote datasets. Voor tabelvariabelen worden geen statistics bijgehouden en de optimizer 'schat' dat er één rij in zit. Een tabelvariabele met veel rijen kan dus leiden tot een slecht query plan.
Wees zorgvuldig met datatypes van variabelen en parameters. Gebruik het datatype van de kolom waarmee je vergelijkt. Doe je dit onzorgvuldig, dan kan dat grote gevolgen hebben voor de performance, omdat statistics mogelijk niet kunnen worden gebruikt. Voorkom impliciete datatype conversies, in het bijzonder tussen varchar en nvarchar.
Het gebruik van de output clause kan code versimpelen met minder kans op blocking en deadlocks. Bijvoorbeeld bij een insert .. select waarbij de ge-inserte data in het vervolg van de code nodig is.
Gebruik computed columns, eventueel persisted, waar mogelijk om de logica van een afgeleid attribuut in de tabel vast te leggen, bijv.
naam as voornaam+' '+isnull(tussenvoegsel+' ', '') + achternaam
T-SQL is gemaakt om te werken met relationele data. Het is een minder geschikte taal voor bijvoorbeeld string processing en grote berekeningen. Overweeg voor dergelijke taken het gebruik van .NET assemblies binnen SQL Server, of voer de taak uit in de client.
Vermijd indexhints in operationele code. Daarmee wordt immers de optimizer overruled en is de kans op een beter plan verkeken. Bovendien leidt het tot een fout als ooit de index gedropt wordt.
Gebruik de nolock hint of het read uncommitted isolation level alleen als je echt bereid bent ongecommitte en soms zelfs verkeerde data te lezen. Overweeg anders de read committed snapshot optie om te voorkomen dat readers en writers elkaar blokken.
Schrijf triggers zo dat ze geldig zijn voor een willekeurig aantal gemodificeerde rijen. Join daartoe met de inserted en deleted tabellen. Gebruik daarvoor geen cursor.
Gebruik transacties om statements samen te voegen die als logisch geheel dienen te worden uitgevoerd. Realiseer je dat iedere query om externe redenen kan falen, bijvoorbeeld door een deadlock, en je code nog steeds geldig moet zijn en de database achterlaat in een duidelijke en consistente toestand. Zet de transactie binnen een try .. catch .. om de foutafhandeling inclusief rollback te regelen.
Houdt transacties in alle gevallen zo kort mogelijk. Doe geen werk binnen een transactie dat daarbuiten kan, zoals het checken van de geldigheid van parameters.
Gebruik het juiste isolation level voor transacties. Soms is het nodig data gelockt te houden vanaf het moment dat deze in een transactie gelezen is, bijvoorbeeld bij het uitvoeren van een overboeking waarbij eerst wordt gecontroleerd op het saldo. Het default isolation level van read committed is dan niet genoeg. In plaats daarvan moet worden gebruikt: repeatable read als het saldo is opgeslagen in een enkel updatetable veld, of serializable als het saldo wordt berekend uit een set mutaties.
Check het default isolation level dat je programmatuur gebruikt. Soms is dat nodeloos hoog, leidend tot een beperking in de concurrency. Check het isolation level van bestaande connecties middels de dynamic management view sys.dm_exec_sessions.
Wees voorzichtig met het gebruik van user defined scalar functions. Deze worden per regel aangeroepen en leiden vaak tot een slechte performance. Vanaf SQL Server 2019 is dit beter geregeld met 'scalar UDF inlining'. Ook multi-statement table valued functies presteren meestal slecht.
Gebruik geen codefragmenten van internet tenzij je precies begrijpt wat er gebeurt of de bron helemaal vertrouwt. Er zijn veel scalar functies in omloop, bijvoorbeeld voor het formatteren van datums, die ware performance-killers zijn. Incorrecte of verouderde code komt ook veel voor.
Gebruik 'yyyymmdd' voor invoer van een datum, dit werkt altijd onafhankelijk van de set dateformat optie.
In het geval van dynamic SQL, gebruik geen exec, maar sp_executesql met expliciete parameters. Dit zowel ten behoeve van caching als om SQL injection te voorkomen.
Gebruik bij voorkeur with schemabinding bij de definitie van views, functies en triggers.
Zorg voor een consequente en duidelijke layout van je code. Dit bevordert de leesbaarheid en verkleint de kans op fouten:
- Geef bij elk object het schema. Dit vermijdt misverstanden, onvoorziene effecten en levert een (beperkt) performancevoordeel op.
- Gebruik expliciete kolomlijsten bij inserts en updates, zodat het toevoegen van een kolom aan een tabel nooit leidt tot falende code.
- Kwalificeer alle kolommen met de schema- en tabelnaam dan wel de table alias (tenzij triviaal).
- Gebruik de join syntax met het join keyword, in plaats van de verouderde ANSI syntax.
- Gebruik optionele keywords zoals inner join consequent.
- Gebruik haakjes in de where clause als and en or door elkaar worden gebruikt, gewoon om elke twijfel weg te nemen.
- Let op een consequente indentatie en wijze waarop je statements verdeelt over regels.
- Zorg voor consequent hoofdlettergebruik.
- Sluit statements af met ";".
Voeg op beslissende punten commentaar toe, niet te weinig en ook niet te veel:
- Documenteer het doel van de procedure, functie etc.
- Geef meta-informatie zoals auteurs, datum, status, wijzigingshistorie. Doe dit op een systematische wijze.
- Voeg ook commentaar toe aan dynamic sql om in ieder geval de genererende code te kunnen vinden.
- In grotere stukken code kan het zinvol zijn bij een else aan te geven welke voorwaarden gelden.
- Voor elke common table expression (of tijdelijke tabel met tussenresutaten), geef aan waaruit de set bestaat.
- Documenteer aannames over de data die niet worden afgedwongen of gecontroleerd.
- Geef aan als een functioneel minder logische keuze gemaakt wordt ten behoeve van performance, bijv: "rijen worden ingevoegd met dummywaarde om te voorkomen dat ze groeien na een update.
- Geef bij een query aan als er een covering index voor gemaakt is. Bij het wijzigen van de query is dan duidelijk dat ook de index moet worden aangepast.

Performance

De meeste van de bovenstaande aanbevelingen zijn niet alleen goed voor datakwaliteit en correcte en onderhoudbare code, maar ook voor performance. Meestal is het afwijken van deze principes ten behoeve van performane een slecht idee. Specifiek op het gebied van performance adviseer ik voor de ontwikkelaar nog het volgende:

Indexeer tabellen bij het schrijven van queries (en niet pas als er in productie performance-problemen optreden).
- Maak nooit zomaar een index zonder te kijken wat er al is. SQL Server weerhoudt je er niet van twee identieke, of anderszins redundante indexes aan te maken. Vaak kan ook een bestaande index worden aangepast, door het toevoegen van key- of included kolommen.
- Ga na of een index fragmenteert en of er queries zijn die daar last van hebben. Zo ja, zet eventueel een fill factor en neem de index op in een defragmentatie-routine.
Test de queries die je schrijft op performance.
- Een makkelijke manier om performance-gegevens te zien is met de volgende set options in de Management Studio:
  set statistics io, time on;
- Bekijk het actuele query plan, en behandel eventuele warnings en missing indexes.
Zie ook: Wat moet je weten over SQL Server performance?

Laatst gewijzigd: 28 december 2022