Følgende beskriver retningslinjer for udvikling af Stored Procedures,
Rretningslinjerne skal sikre ensartethed og god performance.
Stored Procedures kan bruges til mange ting, både store batch job lignende konstruktioner, der kun kører få gange om dagen, og små konstruktioner der kaldes tusindvis af gange, f.eks. via webservice kald
Stored procedures der kører tusindvis af gange, skal selvsagt holdes simple uden for mange logiske udtryk som if-then-else osv. og den enkelte stored procedure bør kunne eksekveres på (mili)sekunder og ikke minutter.
Start altid med
SET NOCOUNT ON.
Herved undertrykkes beskeden om hvor mange rækker der blev udtrukket/slettet/indsat, hvorved spares lidt netværkstrafik og aftagere af resultatet, undgår mulig forvirring over hvad der returneres fra stored procedure
Ved brug af følgende udtryk, skal succes af operationen testes, enten med @@Error eller en TRY...CATCH blok:
INSERT, DELETE, UPDATE
SELECT INTO
Kald af stored procedures
Afvikling af dynamisk SQL
COMMIT TRANSACTION
DECLARE ogd OPEN CURSOR
FETCH from cursor
WRITETEXT og UPDATETEXT
Vælg fornuftig navngivning af variable.
Resten er standard SQL Best Practice
Input parametres datatyper skal svare til de datatyper der anvendes i de tabeller der benyttes af den stored procedure som filtre, joins etc.
Hvis datatyper ikke svarer til de underliggende, konverteres disse implicit af SQL serveren, med forøget memory-, cpu- og tidsforbrug til følge.
(se nærmere under 'SQL Best practices').
Altid default værdier for input parametre.
Output bør holdes simpelt, som enten lister af værdier eller enkeltværdier, flere resultatsæt bør som hovedregel undgåes.
Benyt altid returværdien til at signalere succes eller ej
Retur værdien skal være 0 ved succes og større end 0 ved fejl.
Som for parametre gælder at datatyper skal svare til den forventede datatype ved anvendelsen.
(se nærmere under 'SQL Best practices')
Brug TRY...CATCH blokke
Brug sysmessage, sp_addmessage istedet for hardkodede fejlbeskeder
Med sp_addmessage og sysmessages, benyt altid fejlbesked nummer fra 50001 og opefter.
Når RAISERROR benyttes, sæt 'severity level' <= 10 ved warning.
Når RAISERROR benyttes, sæt 'severity level' mellem 11 og 16 ved fejl.
For at sikre ensartede og idempotente scripts, er udviklet en stribe templatescripts
anvendelsen er beskrevet her DBInstaller SSMS Templates - STAR City - Internt område - Confluence (atlassian.net)
Best practice, enten på grund af performance, læsbarhed, kompatibilitet ...
Ikke i prioriteret rækkefølge.
Der kan altid findes eksempler hvor det giver mening, ikke at følge best practice, men generelt vil det være en fordel at følge disse.
Det er en udbredt misforståelse at benyttelsen af query hint 'WITH (NOLOCK)' betyder at tabellen ikke låses mens der læses.
SELECT EfterNavn FROM Person.Person WITH (NOLOCK) |
I virkeligheden betyder 'WITH (NOLOCK)' det samme som READ UNCOMMITED
(og der sættes faktisk en ekstra lås på data når 'WITH (NOLOCK)' benyttes, der kan forårsage deadlocks, se senere)
Det vil sige i ovenstående eksempel, læses alle rækker fra tabellen Person, uanset om det der står i rækken er commitet eller ej
Det betyder at man kan risikere følgende i resultatsættet
For temp tabeller gælder at her skal 'WITH (NOLOCK)' ikke anvendes. Det har absolut ingen effekt, hverken den ene eller den anden vej. Det vil blot forvirre i læsning af SQL
Der er selvfølgelig steder hvor det giver mening at benytte 'WITH (NOLOCK)'
Der findes masser af artikler om emnet, her er een af de mere humoristiske: NOLOCK Is Bad And You Probably Shouldn't Use It. - Brent Ozar Unlimited®
'Implicit conversion' ses i query plan, oftest markeret med en advarselstrekant.
'Implicit conversion' skyldes enten forskel i datatype mellem parameter/variable ('prædikatet') og datatypen for den kolonne der søges mod,
eller at en beregning udføres på tabelkolonnen i stedet for prædikatet.
I begge tilfælde kan SQL Server ikke lave en indekssøgning på kolonnen og vil istedet foretage en scanning.
Dette skyldes at SQL Serveren først skal konvertere hver eneste række i datasættet før den kan foretage den ønskede operation.
Eksempler:
/* Kolonnen efternavn i tabellen Person er defineret som NVARCHAR(30) Der findes et indeks på kolonnen Efternavn */ DECLARE @cEfterNavn CHAR(30) SELECT @cEfterNavn = 'Nielsen' /* Dette giver implicit conversion på grund af forskellen i datatype */ SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn = @cEfterNavn /* det kan rettes på tre måder, men den sidste er IKKE best practice: Man kan enten ved at ændre datatypen for variablen */ DECLARE @cEfterNavn NVARCHAR(30) /* eller udføre konverteringen på variablen */ SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn = CAST(@cEfterNavn AS NVARCHAR(30) ) /* i praksis kan man også komme af med advarslen om 'implicit conversion' på denne måde (så har vi ikke ændret datatypen for @cEfterNavn) Men det vil stadig gøre at SQL Server ikke kan benytte det korrekte indeks og man får det der kaldes 'NON-SARGABLE'- prædikat */ SELECT pers.EfterNavn FROM Person.Person pers WHERE CAST(pers.EfterNavn AS CHAR(30) ) = @cEfterNavn |
SARGABLE er en forkortelse af 'Search ARGument Able', der betyder at man kan benytte et indeks til sin operation (hvis det findes :-))
Det gælder altså om at opbygge sin WHERE betingelse så SQL Server kan benytte et index seek, istedet for scan
Vi bruger samme tabel som ovenfor
/*variablen sættes til at være korrekt type */ DECLARE @cEfterNavn NVARCHAR(30) SELECT @cEfterNavn = 'Niels' /* og vil gerne have alle der begynder med 'Niels' */ SELECT pers.EfterNavn FROM Person.Person pers WHERE LEFT(pers.EfterNavn,5) = @cEfternavn |
Dette er også et 'NON-SARGABLE'- prædikat og løsningen er igen at lade operationen foregå på variablen og i dette tilfælde er det ok at lave en LIKE:
SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn LIKE @cEfternavn+'%' |
Helt generelt vil ethvert prædikat hvor en kolonne manipuleres med en funktion, blive NON-Sargable
SELECT pers.EfterNavn FROM Person.Person pers |
SELECT pers.EfterNavn FROM Person.Person pers |
EXEC person.GetPersonData @CPR='xxxxxxxxxx' |
SELECT borger.Navn, adr.vejnavn FROM borger borger INNER JOIN borger_adr adr ON borger.cpr = adr.cpr |
og ikke
SELECT borger.Navn, adr.vejnavn FROM borger borger, borger_adr adr WHERE borger.cpr = adr.cpr |
Der er ikke noget forkert med RIGHT JOIN, men det er nemmere at læse hvis alle JOINs peger i samme retning
Man kan assigne værdier til mere end een variabel, og det er hurtigere end een SET pr variabel.
SELECT @Var1 = 45,
@Var2 = 56
istedet for
SET @Var1 = 45
SET @Var2 = 56
CAST er ANSI standard, mens CONVERT kun virker på SQLServer.
Kun ved DATETIME konverteringer er CONVERT bedste valg, det kan CAST ikke i nær samme omfang
Prøv også at undgå IN.
Hvis eksistensen af værdier skal kontrolleres, benyttes EXISTS i stedet for IN.
IN tæller også NULL værdierne, men det gør EXISTS ikke.
EXISTS returnerer boolean Ja eller Nej), mens IN returnerer alle værdier, derfor er resultatsættet for IN større end EXISTS
Brug:
SELECT pers.EfterNavn FROM Person.Person pers WHERE EXISTS (SELECT 1 FROM person.Borgere cpr WHERE pers.cpr = cpr.cpr) |
istedet for
SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.cpr IN (SELECT cpr.cpr FROM person.Borgere cpr) |
Det er bare en masse ekstraarbejde for databasen, hvis det ikke behøves.
Vær opmærksom på læsbarheden,
Ved få kolonner er det ok at have det i een linie:
SELECT Col1, Col2, Col3, Col4, FROM TabelA |
men når antallet af kolonner vokser er det bedre at lave een linie pr kolonne
SELECT Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8 FROM TabelA |
SELECT INTO er en ikke logget operation, og derfor meget hurtigere end INSERT
SELECT Col1 , Col2 INTO TabelA FROM TabelB |
istedet for
INSERT INTO TabelA (Col1, Col2) SELECT Col1 , Col2 FROM TabelB |
-KeyWords og datatyper med stort: SELECT, VARCHAR, DATETIME, INSERT, FROM et
-Indentering med tabs
-Kommentering i block comment:
/* kommentar */
Ikke kommentere det åbenlyse, men istedet hvad en klump sql gør, eller hvorfor en specifik lille detalje i koden er med:
SELECT o.name FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] WHERE i.type_desc = 'HEAP' /* vi vil kun se tabeller uden clustered index */ AND o.[type] = 'U' /* kun user tables */ |
-for JOIN kriterier gælder at tabellen der joines mod, nævnes sidst
SELECT A.* FROM TabelA A LEFT JOIN TabelB B ON A.ID = B.ID LEFT JOIN TabelC C ON B.ID = C.ID |
istedet for
SELECT A.* FROM TabelA A LEFT JOIN TabelB B ON B.ID = A.ID LEFT JOIN TabelC C ON C.ID = B.ID |
-Boolean udtryk (AND, OR, NOT)
Benyt parenteser til at vise hvordan det skal læses
SQL Server skal nok gøre det rigtigt, men læsbarheden er meget bedre
Ikke:
… A = B AND A = C OR A = D …
Men istedet:
… (A = B AND A = C) OR A = D …