MS Sql 70-461: Chapter 12

Les 1

Understanding transactions

Alle wijzigingen op een DB vinden in Sql Server vinden plaats binnen een transactie. Dit betekent dat alle wijzigingen worden doorgevoerd, of geen. Op deze manier zorgt de Relational Database Management System (RDMS) er voor dat de integriteit van de data gewaarborgd blijft.

Alle operaties die in welke manier dan ook schrijven in de DB worden door Sql Server als een transactie beschouwd. Dit bevat:

- Alle Data Manipulation Language (DML) statements, zoals INSERT, UPDATE en DELETE

- Alle Data Definition Language (DDL) statements, zoals CREATE TABLE en CREATE INDEX

Als een transaction is goedgekeurd door de user, dan voert Sql Server een commit uit op de transactie. Als er fouten optreden, dan treeft er een rollback op.

ACID properties of transactions

In relationele database wordt de ACID acroniem gebruikt om de eigenschappen van transacties te beschrijven. ACID staat voor:

- Atomicity: Elke transactieis een atomische unit of work. Dit betekent dat of alle DB wijzigingen in de transactie slagen, of alle wijzigingen slagen niet. (All or nothing).

- Consistency: Elke transactie, geslaagd of niet, laat de DB achter in een geldige status. Als de status inconsistent is, voert Sql Server een rollback uit zodat de DB consistent blijft.

- Isolation: Elke transactie ziet eruit alsof het zich voordoet in isolatie t.o.v. andere transacties

met betrekking tot wijzigingen in de database . De mate van isolatie kan variëren op basis van isolatie

niveau.

- Durability: Elke transactie wordt vervolgd als de service is onderbroken. Als de service weer draait, worden alle transacties afgerond (comitted transactions) of afgebroken (rollback, uncomitted transactions).

Atomicity

Als er meerdere commando's in een transactie zitten, zal de transactie pas gecommit worden als alle commando's succesvol zijn uitgevoerd.

Consistency

Om de consistentie te waarborgen zorgt Sql Server er voor dat aan alle constraints in de DB wordt voldaan. Als dit niet het geval is genereert Sql Server een foutmelding. Het is mogelijk om hierop te anticiperen en logica op toe te passen.

Isolation

Als binnen een transactie wijzigingen worden gedaan op de database, zijn geen van deze objecten die worden aangepast, toegestaan om aangepast te worden door andere transacties. Als twee verschillende transacties dezelfde data aan willen passen, zal de een moeten wachten totdat de ander klaar is. Dit wordt door Sql Server bewerkstelligd door locking of door row versioning.

Durability

Sql Server behoud transactional durability door gebruik te maken van de database transaction log. Elke verandering in de database (data modification statement of DDL statement) wordt eerst weggeschreven naar het transaction log met de originele versie van de data (in het geval van updates en deletes). Als een transaction is comitted en alle consistency checks zijn succesvol, dan wordt het feit dat de transactie is geslaagd weggeschreven naar het transaction log.

Als de Sql service onverwachts uitvalt voordat de succesvol commit is weggeschreven in het log en de service wordt weer opgestart, dan vind er een rollback plaats. Alle wijzigingen worden dan dus ongedaan gemaakt.

Types of transactions

Sql Server kent twee soorten transacties:

System transactions

Sql Server gebruikt system transactions om al zijn interne persistant system taballen te onderhouden. Deze transacties kunnen niet door gebruikers beheerd worden.

User transactions

Transacties die automatisch, impliciet of expliciet door gebruikers gemaakt zijn om data aan te passen of te lezen worde user transactions genoemd. De namen van deze transacties kunnen bekeken worden door de dynamic management view (DMV) 'sys.dm_tran_active_transactions' te raadplegen. 

Transaction commands

Elke transactie begint met een T-Sql statament BEGIN TRANSACTION. Het werkelijke command kan ook geschreven worden als BEGIN TRAN [name]. 

Met het COMMIT TRANSACTION command wordt de transactie uitgevoerd. Alternatieve schrijfwijzen zijn COMMIT TRAN, COMMIT WORK of gewoon COMMIT. Met het commando ROLLBACK TRAN, ROLLBACK WORK of gewoon ROLLBACK worden de wijzigingen van de transactie ongedaan gemaakt. Het is mogelijk om transacties te nesten.

Transaction levels en states

Twee system functions waarme de state / level van een transactie kan worden achterhaald zijn:

@@Trancount: kan gebruikt worden om de level van een transactie te vinden

- Een level met waarde 0 betekent dat de code op dit moment niet binnen een transactie valt

- Een level groter dan 0 betekent dat er een actieve transactie is. Een nummer groter dan 1 betekent het geneste level van de transcatie

XACT_STATE(): kan gebruikt worden om de status van de transactie te achterhalen

- Status 0 betekent dat er geen actieve transactie is

- Status 1 betekent dat er een ongecomitteerde transactie is en wel kan worden gecomitteerd. Het nesting level wordt niet vermeld

- Status -1 betekent dat er een ongecomitteerde transactie is, maar niet kan worden gecomitteerd vanwege een eerdere opgetreden fout.

De twee functies vullen elkaar dus aan.

Transaction modes

Er zijn drie modes voor een user transactie. De betekent dat er drie manieren zijn om met transacties te werken. Een modus verandert niet het gedrag van een transactie.

Autocommit mode

In deze modus worden single data modification en DDL statements uitgevoerd in de context van een transactie die automatisch gecommit wordt als het statement succesvol is uitgevoerd. Bij een fout wordt er een rollback uitgevoerd.

Dit is de standaard modes waarin statements worden uitgevoerd.

In deze modus wordt geen gebruik gemaakt van commando's zoals BEGIN TRAN, ROLLBACK TRAN of COMMIT TRAN.

Implicit transaction mode

Als een DML of DDL statement wordt uitgevoerd binnen een transactie met de modus implcit transaction mode, dan wordt @@TRANCOUNT met 1 verhoogd, maar de COMMIT of ROLLBACK wordt niet automatisch uitgevoerd. Er moet altijd handmatig nog het COMMIT en ROLLBACK commando geschreven worden.

Omdat Implicit transaction mode niet de standaard is, moet deze handmatig geactiveerd worden met:

SET IMPLICIT_TRANSACTIONS ON;

Explicit transaction mode

Een expliciete transactie is een transactie die gedefinieerd is met BEGIN TRANSACTION of BEGIN TRAN. Als een expliciete transactie wordt gestart, wordt @@TRANCOUNT direcht met 1 verhoogd.

Afhankelijk van de fout die kan optreden vindt er wel of geen ROLLBACK plaats. Foreign key violations worden bijv. niet teruggedraaid. Om er zorg voor te dragen dat er toch een volledige rollback plaatsvind, moet foutafhandeling worden toegevoegd.

Nested transactions

Bij geneste transacties veranderd het gedrag van COMMIT en ROLLBACK. Schematische weergave van een geneste transactie met enkel een COMMIT:

De COMMIT van een geneste transactie heeft niet echt effect op de transactie. Het zorgt er voor dat de @@TRANCOUNT verminderd wordt met 1. Enkel de COMMIT van de meest buitenste transactie, dus degene met @@TRANCOUNT = 1 commit daadwerkelijk de transactie.

Het maakt niet uit op welk level ROLLBACK wordt aangeroepen. ROLLBACK zal alle wijzigingen van de transactie ongedaan maken en de @@TRANCOUNT op 0 zetten.

Marking a transaction

Een transactie kan een naam krijgen door deze op te geven na het BEGIN TRAN statement. Sql Server pakt enkel de eerste 32 karakters, de rest wordt genegeerd. De naam van de transactie wordt getoond in de view 'sys.dm_tran_active_transactions'.

Sql Server onthoud enkel de namen van de meeste buitenste transactie. De namen van geneste transacties worden genegeerd.

Transacties met een naam worden gebruikt om een markering aan te brengen in het transaction log om te kunnen specificeren op welk punt een DB kan worden restored.

USE TSQL2012

BEGIN TRAN Tran1 WITH MARK;

 --

COMMIT TRAN; -- or ROLLBACK TRAN

--

Het volgende statement kan gebruikt worden om de DB te restoren tot de markering:

RESTORE DATABASE TSQ2012 FROM DISK = 'C:SQLBackups\TSQL2012.bak'

 WITH NORECOVERY;

GO

RESTORE LOG TSQL2012 FROM DISK = 'C:\SQLBackups\TSQL2012.trn'

 WITH STOPATMARK = 'Tran1';

GO

Opmerking bij gebruik WITH MARK:

- De transctie moet een naam hebben als WITH MARK gebruikt wordt;

- Er kan een beschrijving worden opgegeven bij WITH MARK, dit wordt genegeerd door Sql Server;

- De DB kan restored worden totdat het het commando STOPBEFOREMARK tegenkomt;

- Als RECOVERY wordt toegevoegd aan de WITH lijst, dan heeft dit geen effect.

Additional transaction options

Andere specialistischere opties voor transacties zijn:

• Savepoints: Dit zijn locaties binnen transacties die gebruikt kunnen worden om een rollback uit te voeren op een selectieve subset
• Een savepoint kan gedefinieerd worden door het SAVE TRANSACTION command.
• Het ROLLBACK statement moet verwijzen naar het savepoint. Als geen naam wordt opgegeven bij een ROLLBACK dan wordt de gehele transactie teruggedraaid.
• Cross-database transactions: Een transactie kan over meerdere databases gaan op één Sql Server instance zonder daar iets extra's voor te hoeven doen.
• Sql Server behoud de ACID standaarden
• Er zijn beperkingen bij database mirroring. Een cross-database transaction kan niet bewaard blijven na een failover van een van de databases.
• Distributed transactions: Het is mogelijk om een transactie over meerdere servers te laten gaan door gebruik te maken van linked servers. In dit geval wordt de transactie in plaats van een local transaction een distributed transaction genoemd.
• Bij distributed transactions maakt Sql Server gebruik van de Distributed Transport Coordinator (MSDTC)

Basic locking

Om de isolation te behouden implementeert Sql Server een set van locking protocollen. In de basis zijn er twee algemene modes van locking:

- Shared locks: Wordt gebruikt in sessies die data lezen

- Exclusive locks: Wordt gebruikt om data te wijzigen

Als binnen een sessie data wordt gewijzigd zal Sql Server proberen een exclusive lock toe te passen op de objecten in kwestie. Deze exclusive locks gebeuren altijd in de context van een transaction, ook als de transaction in de autocommit mode zit en de sessie niet automatisch wordt gestart. 

Als een sessie een exclusieve lock heeft op een object, dan geen enkele andere transactie data van dit object wijzigingen. De andere transactie zal moeten wachten totdat de lock weer wordt vrijgegeven door een commit of rollback. 

Lock compatibility

Als een sessie enkel data leest zal Sql Server standaard kort een shared lock op het object zetten. Meerdere sessies kunnen dezelfde objecten lezen omdat shared locks compatible zijn met elkaar.

Andere combinaties van shared en excluive locks zijn niet compatible met elkaar.

Blocking 

Een block is als een sessie een exclusive lock op een resource heeft, wat er voor zorgt dat andere sessies geen enkele vorm van een lock kan toepassen op dezelfde resource. Binnen een transactie worden de exclusie locks net zo lang op de resource gehouden tot het einde van de transactie. Het is niet mogelijk dat twee sessie tegelijk schrijven in dezelfde tabel.

Een exclusive lock zorgt er ook voor dat geen data kan worden gelezen uit de tabel d.m.v. een shared lock. Binnen een transactie kan met de standaard READ COMITTED isolation level er voor worden gezorgd dat de shared lock zo snel mogelijk wordt vrijgegeven, Er wordt dus niet gewacht tot het einde van de transactie.

Deadlocking

Een deadlock is het resultaat van het wederzijds blocken van twee of meerdere sessies. 

Bij een deadlock heeft de ene transactie toegang nodig tot een resource die door een andere transactie is geblokkeerd. 

Transaction isolation levels

Sql Server staat het toe om in de ene transactie data lezen van een andere transactie of om data te wijzigen van andere transacties die enkel data lezen. Dit kan op basis van het gekozen transaction islolation level.

De meest gebruikte isolation levels zijn:

READ COMMITTED

Dit is het standaard isolation level. Alle lezers in deze sessie zullen enkel data lezen dat is gecommit. Alle SELECT statemens zullen proberen een shared lock te verkrijgen en elke onderliggend data resource die wordt gewijzigd door een andere sessie zal de READ COMITTED sessie blocken.

READ UNCOMMITTED

Dit level staat het toe om nog niet gecomitteerde data te lezen. Dit level verwijderd de shared lock die door het SELECT statement is neergezet, zodat readers niet langer geblocked worden door writers. Data die nog niet gecommit is, maar wel is gelezen wordt dirty data genoemd. Het is namelijk mogelijk om data op te halen van een transactie die is teruggedraaid is door een ROLLBACK.

READ COMMITTED SNAPSHOT

Dit level is een optionele manier om de standaard READ COMMITTED te gebruiken en is het standaard level van Azure Sql Database.

• Vaak afgekort met RCSI (Read Committed Snapshot Isolation). Gebruikt een tijdelijke tabel om originele waarden op te slaan van de gewijzigde data. Deze versies worden enkel opgeslagen zolang e data nodig is om door readers te worden gelezen in de originele staat. Het resultaat hiervan is dat er geen shared lock nodig is.
• De RCSI optie wordt gezet op database niveau en is een persistent database eigenschap
• RCSI is geen apart isolation level, het is een andere implementatie van READ COMMITTED.

REPEATABLE READ

Dit level wordt per sessie ingesteld en garandeerd dat alle data die gelezen is in een transactie later nog een keer kan worden ingelezen in de transactie. Verwijderde en bijgewerkte data blijven behouden. Er wordt wel een shared lock geplaatst tot het einde van de transactie. Omdat het resultaat nieuwe rijen kan bevatten nadat de data de eerste keer gelezen is, wordt dit een phantom read genoemd.

SNAPSHOT

Dit isolation level gebruikt row versioning in een tijdelijke tabel, net als RCSI. Het leven wordt op database niveau ingesteld en kan dan worden ingesteld per transactie. Een snapshot zorgt er voor dat er geen phantom reads ontstaan. Ook dit level kent geen shared lock. 

SERIALIZABLE

Dit is het sterkste level en wordt per sessie ingesteld. Met dit level zijn alle readers herhaaldelijk en zijn nieuwe rijen niet toegestaan in de onderliggende tabellen.

Alle isolation levels worden per sessie ingesteld.

Les 2

Detecting and raising errors

Als Sql Server een error genereert dan bevat de system function @@ERROR een positief getal dat het error nummer bevat. Zonder een TRY/CATCH block wordt de foutmelding doorgegeven aan de client en niet afgevangen in T-Sql code. Met onderstaande statements is het mogelijk om handmatig errorste genereren:

- de oudere RAISERROR command

- de Sql Server 2012 THROW command

Analyzing error messages

Een foutmelding van Sql Server bestaat uit vier onderdelen:

Error number

• Foutmeldingen zijn genummerd van 1 tot 49999
• Custom error messages zijn genummerd vanaf 50001
• Error nummer 50000 is gereserveerd voor een custom message die geen custom error nummer heeft

Severity level

• Sql Server definieert 26 severity levels, genummerd van 0 t/m 25
• Als een algemene regel, fouten met een severity level van 16 en hoger worden automatisch gelogd in het Sql Server log en het Windows Application Log
• Errors met een severity level van 19 tot 25 kunnen enkel gespecificeerd worden door gebruikers van de sysadmin rol
• Levels 20 tot 25 worden gezien als fataal en sluiten de verbinding en open transactions worden teruggedraaid dmv een rollback
• Errors met severity level 0 - 10 zijn enkel informatief

State

• Dit is een getal met een maximum waarde van 127, welke wordt gebruikt door Microsoft voor interne doeleinden

Error message

• De foutmelding kan maximaal 255 unicode karakters lang zijn
• Worden opgeslagen in sys.messages
• Custom error messages kunnen worden toegevoegd middels de SP sp_addmessage

RAISERROR

Met RAISERROR zijn de message, severity en de state verplicht. Voorbeelden:

RAISERROR ('Error in usp_InsertCategories stored procedure', 16, 0);

RAISERROR ('Error in % stored procedure', 16, 0, N'usp_InsertCategories');

GO

DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';

RAISERROR (@message, 16, 0, N'usp_InsertCategories');

GO

DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure';

SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');

RAISERROR (@message, 16, 0);

Geavanceerde toepassingen van RAISERROR zijn:

- Puur informatieve messages kunnen gebruikt worden als het severity level 0 tot 9 is

- RAISERROR met een severity level > 18 kan enkel gebruikt worden met de WITH LOG optie en als de gebruiker lid is van de sysadmin rol. Sql Server zal de verbinding verbreken als de fout is opgegooid.

- Met de NOWAIT optie kan de foutmelding direct naar de client worden gestuurd. De foutmelding wacht niet in de output buffer voordat hij verzonden wordt.

THROW

THROW lijkt veel op RAISERROR, maar kent wel verschillen:

• THROW gebruikt geen haakjes
• THROW kan in een CATCH van een TRY/CATCH block gebruikt worden zonder parameters
• Als er parameters worden meegegeven dan zijn error_number, message en state verplicht
• De error_number hoeft niet overeen te komen met een nummer in sys.messages
• De message staat geen formatting toe, maar FORMATMESSAGE() kan wel worden gebruikt
• De state parameter moet een integer zijn in de range van 0 - 255
• Elke parameter kan een variabele zijn
• Er is geen severity parameter, deze is altijd 16
• THROW stopt altijd met de batch, tenzij deze in een TRY block zit.
• Het statement dat voor THROW staat, moet eindigen met een ;
• THROW ondersteund niet het NOWAIT command

Voorbeelden:

THROW 50000, 'Error in usp_InsertCategories stored procedure', 0;

DECLARE @message AS NVARCHAR(1000) = 'Error in % stored procedure'; SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories'); THROW 50000, @message, 0;

TRY_CONVERT en TRY_PARSE

TRY_CONVERT probeert een waarde te casten naar het doeltype en als dat lukt, wordt deze waarde geretourneerd, en als het niet lukt dan wordt NULL geretourneerd:

SELECT TRY_CONVERT(DATETIME, '1752-12-31'); -- faalt

SELECT TRY_CONVERT(DATETIME, '1753-01-01'); -- geeft datum terug in DATETIME type

met TRY_PARSE wordt een string opgegeven en een datatype waar naar de string moet worden geconverteerd. Als de conversie niet lukt, geeft de methode NULL terug:

SELECT TRY_PARSE('1' AS INTEGER); -- geeft 1 terug

SELECT TRY_PARSE('B' AS INTEGER); -- geeft NULL terug

Handling errors after detection

Er zijn twee error handling methodes beschikbaar:

- Unstructered: de error moet afgehandeld worden door gebruik te maken van de @@ERROR functie

- Structured: Gebruikmaken van het CATCH block

Unstructered error handling using @@ERROR

Nadat een statement is uitgevoerd en deze is mislukt dan beat @@ERROR het foutnummer. Als er geen fout is opgetreden bevat @@ERRROR 0. @@ERROR bevat altijd de errorcode van het laatst uitgevoerde command. De code kan dus niet met een if worden uitgelezen omdat dit weer een nieuw statement is. De manier om de foutcode uit te lezen is door het resultaat van @@ERROR op te slaan in een variabele. Met @@ERROR kan de fout dus niet op een centrale plek worden afgehandeld. Na elk statement zal deze functie moeten worden aangeroepen om de foutcode op te halen. 

Using XACT_ABORT with transactions

XACT_ABORT (XACT staat voor transaction) werkt met alle soorten code en betreft de gehele batch. Het is mogelijk om een hele batch te laten falen als er een fout optreed. De batch moet dan beginnen met SET XACT_ABORT ON. XACT_ABORT wordt per sessie ingesteld. Als nu een fout optreed wordt de transactie teruggedraaid als de fout een severity > 10 heeft.

De beperkingen van XACT_ABORT zijn:

- De fout kan niet worden afgevangen, noch kan het error nummer achterhaald worden

- Elke error met een severity groter dan 10 zorgt dat de gehele transactie wordt teruggedraaid

- Geen enkele code na de fout wordt uitgevoerd, zelfs het PRINT statement niet

- Nadat de transaction is teruggedraaid kan enkel via de error message gekeken worden wat er fout ging.

Structured error handling using TRY/CATCH

Met TRY CATCH:

De te testen code wordt in een TRY block geplaatst

• Elk TRY block moet worden gevolgd door een CATCH block
• Beide blocken vormen paren en beide blokken moeten in dezelfde batch staan

Als er een error optreed in het TRY block, wordt de control overgedragen aan de CATCH

• De resterende statements in het TRY block worden niet uitgevoerd
• Na de CATCH wordt de code uitgevoerd dat na de END CATCH staat
• Als er geen fout optreed in de TRY, dan wordt het CATCH block overgeslagen

Als een fout optreed in het TRY block, wordt geen foutmelding getoond aan de client

• Dit in tegenstelling tot unstructured error handling, waar de error message altijd naar de client wordt gestuurd
• Een RAISERROR in een TRY block met een severity van 11 - 19 geeft geen foutmelding aan de client, maar zal zijn vervolg vinden in het CATCH block.

Regels voor gebruik van TRY/Catch

• Error met een severity > 10 en < 20 worden opgevangen in de CATCH
• Erors met eens everity > 20 sluiten niet de verbinding en worden afgehandeld in de CATCH
• Compile errors stoppen de batch direct en gaan niet naar de CATCH block
• Bij een fout in het CATCH block wordt de transactie geannuleerd. Met XACT_STATE kan de state worden opgevraagd van de transaction.
• Handelingen die er voor zorgen dat de verbinding wordt verbroken (zoals killen van verbinding) kunnen niet worden opgevangen in het CATCH block.
• TRY/CATCH blocken kunnen worden genest.

In het CATCH block zijn de volgende functies beschikbaar:

• ERROR_NUMBER
• ERROR_MESSAGE
• ERROR_SEVERITY
• ERROR_LINE
• ERROR_PROCEDURE (de naam van de functie, trigger of precedure die werd uitgevoerd toen de error gebeurde)
• ERROR_STATE

BEGIN CATCH

 -- Error handling

 SELECT ERROR_NUMBER() AS errornumber

 , ERROR_MESSAGE() AS errormessage

 , ERROR_LINE() AS errorline

 , ERROR_SEVERITY() AS errorseverity

 , ERROR_STATE() AS errorstate;

END CATCH;

THROW vs RAISERROR in TRY/CATCH

In het TRY block kan zowel THROW als RAISERROR gebruikt worden, beide komen dan in het CATCH blok. RAISERROR moet een severity hebben van 11-19 om in de CATCH te komen.

In het CATCH blok kunnen RAISERROR, THROW met en zonder parameters gebruikt worden.

Met RAISERROR in het CATCH block kan de originele foutmelding aan de client worden getoond. Het originele nummer kan niet worden gebruikt. De code in het CATCH block gaat verder na RAISERROR. Een THROW met parameters stopt de batch en kan wel gebruikt worden om originele error message te re-reaisen en terug te sturen naar de client. Dit is de bij uitstek de meest aanbevolen manier om te gebruiken: de originele foutmelding wordt teruggestuurd naar de client, onder eigen controle en het stopt de uitvoering van de batch direct.

THROW met of zonder parameters beide stoppen de batch direct. Code na een THROW wordt dus nooit uitgevoerd.

Using XACT_ABORT met TRY/CATCH

XACT_ABORT in een TRY block draait de transactie niet terug, maar wordt opgevangen in het CATCH block. De transactie is nog in een uncommitable state (en XACT_STATE = -1). Daarom kan de transactie niet comitted worden in een CATCH block als XACT_ABORT is aangezet.

De XACT_STATE() waarden zijn:

• 1: Een open transactie bestaat en kan worden gecommit of worden teruggedraaid
• 0: Er zijn geen openstaande transacties. Is hetzelfde als @@TRANCOUNT = 0
• -1: Er is een open transactie naar kan niet worden gecommit. De transactie kan enkel worden teruggedraaid.

Les 3

Dynamic Sql is het genereren van T-Sql in T-Sql en vervolgens deze gegenereerde T-Sql uit laten voeren door Sql Server.

Use for dynamic Sql

In T-Sql statements zijn de volgende onderdelen niet te vervangen door variabelen:

• De database naam in het USE statement
• Tabelnamen in de FROM clause
• Kolomnamen in de SELECT, WHERE, GROUP BY, HAVING en ORDER BY clauses
• Inhoud van lijsten in bijv. IN en PIVOT clauses

Als een van bovenstaand toch variabel moet zijn, dan moet gebruik gemaakt worden van dynamic Sql.

Generating T-Sql strings

Als T-Sql statements gegenereert worden, wordt altijd met strings gewerkt. Standaard wordt in Sql Server 2012 the apostrophe (') gebruikt om strings te scheiden. Dit komt door de QUOTED_IDENTIFIER setting. 

Als QUOTED_IDENTIFIER op ON staat (de default) worden strings gescheiden door de apostrophe en worden dubbele quotes gebruikt om T-Sql identiers te scheiden.

Als QUOTED_IDENTIFIER op OFF staat kunnen ook zowel de enkele quote als de dubbele quotes gebruikt worden, maar moeten de T-Sql identifiers omrings worden door blokhaken.

Een enkele quote kan worden escaped door er nog een enkele quote voor te zetten:

SELECT custid, companyname, contactname, contacttitle, addressFROM [Sales].[Customers]

WHERE address = N'5678 rue de l''Abbaye'; -- extra quote voor l'Abbaye

QUOTENAME kan gebruikt worden om de leesbaarheid van gegenereerde quueries te vergroten:

PRINT N'SELECT custid, companyname, contactname, contacttitle, address FROM [Sales].[Customers] WHERE address = N''5678 rue de l''''Abbaye'';';

Met QUOTENAME

PRINT QUOTENAME(N'5678 rue de l''Abbaye', '''');

Nadat de T-Sql is gegenereert kan dit worden uitgevoerd met het EXECUTE statement of d.m.v.  de sp_executesql stored procedure.

The execute command

EXEC, kort voor EXECUTE, kan op verschillende manieren gebruikt worden en één daarvan is het uitvoeren van dynamic T-Sql:

• Uitvoeren van SP's
• Imporsonating users of logins
• Queryen op een linked server
• Uitvoeren van dynamische Sql

Opmerkingen over EXEC:

• De string kan vele T-Sql commands bevatten, maar geen GO delimiters. De string moet dus een enkele batch zijn
• Er kan gebruik gemaakt worden van strings, variabelen of een combinatie hiervan.

Voorbeeld:

DECLARE @SQLString AS NVARCHAR(4000)

 , @tablename AS NVARCHAR(261) = '[Production].[Products]';

SET @SQLString = 'SELECT COUNT(*) AS TableRowCount FROM ' + @tablename;

EXEC(@SQLString);

Sql injection

Een hacker kan kijken of Sql injection mogelijk is door een enkele quote in te voeren en vervolgens de response te bekijken. 

Using sp_executesql

De stored procedure sp_executesql is geintroduceerd als alternatief voor het EXEC command. Met deze sp kan sql injection voorkomen en kan ook beter performen omdat de variabelen als parameters meegegeven worden. Dit performt beter omdat de query plan gecached kan worden.

DECLARE @SQLString AS NVARCHAR(4000), @address AS NVARCHAR(60);

SET @SQLString = N'

  SELECT custid, companyname, contactname, contacttitle, address

  FROM [Sales].[Customers]

  WHERE address = @address';

SET @address = N'5678 rue de l''Abbaye';

EXEC sp_executesql

 @statement = @SQLString -- sql statement dat moet worden uitgevoerd

 , @params = N'@address NVARCHAR(60)' -- lijst van parameters met type

 , @address = @address; -- de variabelen

Tot slot onderstend sp_executesql ook OUTPUT parameters zodat informatie teruggegeven kan worden aan de caller.