MS Sql 70-461: Chapter 13

Les 1

Stored procedures(sp's) zijn routines die zijn opgeslagen in de database en bevatten code. Sql Server kent verschillende soorten sp's:

• T-Sql stored procedures, geschreven in T-Sql
• CLR stored procedures, opgeslagen als Microsoft .NET assemblies in de database
• Extended stored procedures, welke externe dll's aanroepen.

Understanding stored procedures

Een SP bestaat uit een enkele batch met T-Sql code. Features van een SP zijn:

• Ze kunnen worden aangeroepen door het EXEC command
• Parameters kunnen meegegeven worden als input parameters, maar SP's ondersteunen ook OUTPUT parameters
• Ze kunnen een result terug als resultaat teruggeven
• Ze kunnen data in tabellen aanpassen
• Ze kunnen tabellen en indexen aanmaken, wijzigen en verwijderen

Voordelen van een SP:

• Herbruikbaarheid. Een SP kan vanaf verschillende plekken worden aangeroepen met parameters.
• Ze kunnen de DB veiliger maken. Er kunnen rechten op SPs worden uitgedeeld
• De gebruikers hoeft de onderliggende tabelstructuur niet te kennen
• Betere performance omdat er executing plans worden gemaakt die opnieuw gebruikt kunnen worden

Een SP kan niet:

• het USE command gebruiken
• CREATE kan niet worden gebruikt voor AGRREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE en VIEW

Voorbeeld SP met OUTPUT parameter:

CREATE PROC Sales.GetCustomerOrders

 @custid AS INT, -- verplichte parameter

 @orderdatefrom AS DATETIME = '19000101', -- optionele parameter

 @orderdateto AS DATETIME = '99991231',

 @numrows AS INT = 0 OUTPUT

AS

BEGIN

 SET NOCOUNT ON;

 SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate

 FROM Sales.Orders

 WHERE custid = @custid

 AND orderdate >= @orderdatefrom

 AND orderdate < @orderdateto;

 SET @numrows = @@ROWCOUNT;

 RETURN;

END

Testing for the existence of a stored procedure

Om te testen of een SP al bestaat, gebruik het volgende statement:

IF OBJECT_ID('Sales.GetCustomerOrders', 'P') IS NOT NULL

 DROP PROC Sales.GetCustomerOrders;

GO

Stored procedure parameters

Binnen een SP kunnen parameters gedclareerd worden, op eenzelfde wijze als dat ook geldt voor de DECLARE syntax.

Opmerkingen bij SP's:

• CREATE PROC is hetzelfde als CREATE PROCEDURE
• Parameters zijn optioneel
• Als een parameter geen default initialisatie heeft, wordt deze als verplicht beschouwd.
• parameters met OUPUT zijn altijd optioneel
• het AS command is verplicht na de lijst van parameters
• De code kan omringd worden door BEGIN en END, maar dit is niet verplicht, maar kan de leesbaarheid wel vergroten.

SET NOCOUNT ON

Als SET NOCOUNT ON geplaatst is in een SP dan betekent dit dat berichten zoals '3 row(s) affected' niet getoond worden.

RETURN en return codes

Door het RETURN command te gebruiken kan bepaald worden wanneer de SP moet stoppen met uitvoeren. Statements na het RETURN command worden niet meer uitgevoerd.

RETURN zorgt er voor dat een status code terug wordt gestuurd naar de client. Status 0 betekent succesvol en een negatief nummer een fout. De error numbers zijn niet betrouwbaar. Beter is om @@ERROR te gebruiken of ERROR_NUMBER() in een CATCH block.

Executing stored  procedures

Als de SP die moet worden uitgevoerd het enige statement is, dan hoeft EXEC niet gebruikt te worden. Als er meerdere statements zijn, dan moet EXEC wel gebruikt worden.

Input parameters

Parameters kunnen aan een SP worden meegegeven op basis van hun posititie of op basis van de naam van de parameter zoals deze bekend is binnen de SP (de volgorde maakt dan niet meer uit):

EXEC Sales.GetCustomerOrders 37, '20070401', '20070701';-- op basis van positie

of

EXEC Sales.GetCustomerOrders @custid = 37, @orderdatefrom = '20070401',

 @orderdateto = '20070701'; -- op basis van de parameternaam. 

Aangeraden wordt om de parameters mee sturen op basis van de naam.

Output parameters

Om output parameters te gebruiken wordt gebruik gemaakt van het OUTPUT keyword, of korter OUT. Bij het aanroepen van een SP met een OUTPUT parameter, moet ook gebruik gemaakt worden van het OUTPUT keyword:

DECLARE @rowsreturned AS INT;

EXEC Sales.GetCustomerOrders

 @custid = 37,

 @orderdatefrom = '20070401',

 @orderdateto = '20070701',

 @numrows = @rowsreturned OUTPUT;

SELECT @rowsreturned AS 'Rows Returned';

Als OUTPUT wordt weggelaten wordt er geen waarde (NULL) teruggegeven.

Branching logic

SP's ondersteunen de volgende statements om de flow van de code te bepalen:

• IF/ELSE
• WHILE (met BREAK en CONTINUE)
• WAITFOR
• GOTO
• RETURN

If/else

Als IF/ELSE gebruikt wordt zonder BEGIN/END dan wordt één afgehandeld. Als BEGIN/END wel gebruikt wordt, wordt alles wat binnen zo'n block staat uitgevoerd.

While

Voorbeeld gebruik:

SET NOCOUNT ON;

DECLARE @count AS INT = 1;

WHILE @count <= 10

 BEGIN

 PRINT CAST(@count AS NVARCHAR);

 SET @count += 1;

 END;

Voorbeeld met BREAK en CONTINUE:

SET NOCOUNT ON;

DECLARE @count AS INT = 1;

WHILE @count <= 100

 BEGIN

 IF @count = 10

 BREAK;

 IF @count = 5

 BEGIN

 SET @count += 2;

 CONTINUE;

 END

 PRINT CAST(@count AS NVARCHAR);

 SET @count += 1;

 END;

Output:

1

2

3

4

7

8

9

Voorbeeld 3:

DECLARE @categoryname AS NVARCHAR(15);

SET @categoryname = (SELECT MIN(categoryname) FROM Production.Categories);

WHILE @categoryname IS NOT NULL

BEGIN

 PRINT @categoryname;

 SET @categoryname = (SELECT MIN(categoryname) FROM Production.Categories

 WHERE categoryname > @categoryname);

END;

Let op: dubbele categorienamen worden niet getoond.

WAITFOR

Met WAITFOR kan er een 'pauze' worden ingelast. WAITFOR kent drie opties: 

- WAITFOR DELAY

- WAITFOR TIME

- WAITFOR RECEIVE (wordt enkel gebruikt met Service Broker)

Pauzeren voor 20 seconden:

WATFOR DELAY '00:00:20';

WAITFOR TIME wacht met de uitvoering tot een bepaald tijdstip. De volgende code wacht totdat het 23:46 is.

WATFOR TIME '23:46:00';

GOTO

Met GOTO kan gesprongen worden naar gedefinieerde labels. Het wordt afgeraden GOTO te gebruiken.

Voorbeeld:

PRINT 'First PRINT statement';

GOTO MyLabel;

PRINT 'Second PRINT statement'; -- deze rege wordt niet uitgevoerd

MyLabel:

PRINT 'End';

Developing stored procedures

Stored procedures results

Een SP kan meerdere result sets retourneren, d.w.z. er kunnen meerdere SELECT statements in een SP zitten die elk hun eigen result set retourneren:

CREATE PROC Sales.ListSampleResultsSets

AS

 BEGIN

 SELECT TOP (1) productid, productname, supplierid,

 categoryid, unitprice, discontinued

 FROM Production.Products;

 SELECT TOP (1) orderid, productid, unitprice, qty, discount

 FROM Sales.OrderDetails;

 END

GO

Calling other stored procedures

Het is mogelijk om SPs aan te roepen binnen een SP. Let wel:

• Als in een SP een temporary table is aangemaakt, is deze beschikbaar in alle SPs die deze SP aanroept;
• Variabelen en parameters die in proc1 gedeclareerd zijn, zijn niet zichtbaar in de SPs die proc1 aanroept.

Les 2

Een trigger is een speciaal soort SP die geassocieerd is met geselecteerde DML events op een tabel of view. Een trigger kan niet expliciet worden uitgevoerd. Een trigger wordt uitgevoerd als het geassocieerde DML statement wordt uitgevoerd, zoals een INSERT, UPDATE of DELETE.

Sql Server ondersteunt triggers voor

- Data Manpulation events (DML triggers)

- Data Definition events (DDL triggers), zoals CREATE TABLE

DML triggers

Sql Server kent 2 soorten DML triggers:

- AFTER: Deze trigger gaat af nadat het geassocieerde event klaar is en kan enkel gedefinieerd worden op permanente tabellen;

- INSTEAD OF: Deze triggert gaat af in plaats van het event en kan enkel gedefinieerd worden op permanentie tabellen en views.

Een trigger gaat één keer af voor elk DML statement, ongacht het aantal rijen dat wordt aangeraakt door het statement. De trigger en de tabel moeten in hetzelfde schema zitten.

De trigger is onderdeel van de transactie waarin het DML statement wordt uitgevoerd. Als een ROLLBACK wordt uitgevoerd in een trigger, wordt  het geassocieerde statement teruggedraaid.

Met RETURN wordt aangegeven dat de trigger klaar is met uitvoeren.

In triggers kunnen de tabellen inserted en deleted geraadpleegd worden. Deze tabellen bevatten de data die door de DML statements geraakt zouden worden. Inserted bevat rijen van INSERT en UPDATE statements en deleted bevat rijen van DELETE en UPDATE statements.

AFTER Triggers

AFTER triggers kunnen enkel toegepast worden op tabellen. Deze trigger gaat af nadat blijkt dat het DML statement voldoet aan alle contraints (PK, FK, unique, check). Als het statement niet aan de constraints voldoet, gaat de trigger ook niet af.

CREATE TRIGGER Sales.tr_SalesOrderDetailsDML

ON Sales.OrderDetails

FOR DELETE, INSERT, UPDATE

AS

BEGIN

 SET NOCOUNT ON

END

In een trigger definitie is AFTER de default trigger soort als FOR wordt gebruikt. FOR kan vervangen worden door AFTER of INSTEAD TO om een ander trigger type aan te geven.

Als met een INSERT, UPDATE of DELETE statement geen rijen geraakt worden heeft het geen zin om de trigger uit te voeren. De trigger kan geoptimaliseerd worden door te kijken of @@ROWCOUNT 0 is op de eerste regel van de trigger.

Met onderstaand statement kan gekeken worden of een trigger reeds bestaat:

IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL

 DROP TRIGGER Sales.tr_SalesOrderDetailsDML;

GO

Voorbeeld trigger:

CREATE TRIGGER Sales.tr_SalesOrderDetailsDML

ON Sales.OrderDetails

AFTER DELETE, INSERT, UPDATE

AS

BEGIN

 IF @@ROWCOUNT = 0 RETURN;

 SET NOCOUNT ON;

 SELECT COUNT(*) AS InsertedCount FROM Inserted;

 SELECT COUNT(*) AS DeletedCount FROM Deleted;

END;

-- Let wel, het is een bad practice om result sets terug te geven vanuit triggers. In nieuwe versie van Sql zal dit niet mee mogelijk zijn.

CREATE TRIGGER Production.tr_ProductionCategories_categoryname

ON Production.Categories

AFTER INSERT, UPDATE

AS

BEGIN

 IF @@ROWCOUNT = 0 RETURN;

 SET NOCOUNT ON;

 IF EXISTS (SELECT COUNT(*)

                     FROM Inserted AS I

                     JOIN Production.Categories AS C ON I.categoryname = C.categoryname

                     GROUP BY I.categoryname

                     HAVING COUNT(*) > 1 )

 BEGIN

 THROW 50000, 'Duplicate category names not allowed', 0;

 END;

END;

Nested AFTER triggers

Als een AFTER trigger op tabel A een trigger laat uitvoeren op tabel B noemt men dit een nested trigger. Er kunnen maximaal 32 nested triggers uitgevoerd worden. Nested triggers kunnen aan- en uitgezet worden d.m.v. een SP. Default staat nesting aan.

EXEC sp_configure 'nested triggers', 0; -- 0 is uitzetten, 1 is aanzetten.

INSTEAD OF triggers

Een INSTEAD OF trigger voert statements uit in plaats van het INSERT, UPDATE of DELETE statement:

INSTEAD OF INSERT

AS

BEGIN

 SET NOCOUNT ON;

 IF EXISTS (SELECT COUNT(*)

                     FROM Inserted AS I

                     JOIN Production.Categories AS C ON I.categoryname = C.categoryname

                     GROUP BY I.categoryname

                     HAVING COUNT(*) > 1 ) 

  BEGIN

   THROW 50000, 'Duplicate category names not allowed', 0;

  END;

 ELSE

   INSERT Production.Categories (categoryname, description)

   SELECT categoryname, description FROM Inserted;

END;

DML trigger functions

Twee functies waar een trigger gebruik van kan maken:

- UPDATE(): kan worden gebruikt om te bepalen of een kolom voorkomt in een INSERT of UPDATE statement:

  IF UPDATE(qty)

 PRINT 'Column qty affected';

- COLUMNS_UPDATED(): kan gebruikt worden als het sequence nummer bekend is van de kolom in de tabel. Hiervoor is de bitwiwe AND operator nodig (&) om te zien of een kolom is bijgewerkt.

Les 3

Understanding User-Defined functions

Een User Defined Function (EDF) kan een scalar (enkele) waarde of een table teruggeven aan de aanroeper. UDFs ondersteunen het gebruik van parameters en worden uitgevoerd als onderdeel van eel Sql statement. Ze kunnen niet worden uitgevoerd d.m.v. het EXEC command.

UDFs kunnen geen DDL stataments uitvoeren, zoals het maken van tabellenof aanpassen van tabellen, indexes of andere objecten. Ook kunnen zij geen data aanpassen in permanente tabellen d.m.v. DML statements.

Een table-valued UDF met een enkele regel code er in heet een 'inline table-valued UDF'. Een table-valued UDF met meerdere regels code wordt een multistatement table-valued UDF genoemd.

3 afkortingen voor UDFs zijn (bij gebruik van OBJECT_ID()):

1. FN = Sql scalar function
2. IF = Sql Inline table-valued function
3. TF = Sql table-valued function

Scalar UDFs

Alle code in een scalar UDF moet binnen in een BEGIN/END block staan. Voorbeeld gebruik:

CREATE FUNCTION Sales.fn_extension

(

 @unitprice AS MONEY,

 @qty AS INT

)

RETURNS MONEY

AS

BEGIN

 RETURN @unitprice * @qty

END;

Toepassing:

SELECT Orderid, unitprice, qty, Sales.fn_extension(unitprice, qty) AS extension

FROM Sales.OrderDetails;

Table-Valued UDFs

An inline Table-Valued UDF

Een inline table valued UDF bevat enkel een SELECT statement dat een tabel teruggeeft. 

CREATE FUNCTION Sales.fn_FilteredExtension

(

 @lowqty AS SMALLINT,

 @highqty AS SMALLINT

 )

RETURNS TABLE AS RETURN

(

 SELECT orderid, unitprice, qty

 FROM Sales.OrderDetails

 WHERE qty BETWEEN @lowqty AND @highqty

);

Toepassing:

SELECT orderid, unitprice, qty

FROM Sales.fn_FilteredExtension (10,20);

De optimizer behandeld een UDF als een view.

Multistatement Table-Valued UDF

CREATE FUNCTION dbo.FunctionName

(

 @param1 int,

 @param2 char(5)

)

RETURNS @returntable TABLE

(

 c1 int,

 c2 char(5)

)

AS

BEGIN

 INSERT @returntable

 SELECT @param1, @param2

 RETURN

END;

Limitations on UDFs

Beperkingen van een UDF zijn:

• Kunnen geen schema of data aanpassingen doorvoeren in de DB
• Kunnen niet de status van een database aanpassen
• Kunnen geen tijdelijke tabellen raadplegen of aanmaken
• Kunnen geen SP's aanroepen
• Kunnen niet dynamische Sql uitvoeren
• Ze produceren side effects: RAND() en NEWID() zijn afhankelijk van hun vorige aanroep. Vertrouwen op voorgaande informatie is een side effect dat niet is toegestaan.

UDF options

Er kunnen vijf opties worden ingesteld voor UDFs

1. ENCRYPTION: Net als bij SPs en triggers, is dit verwarde tekst en geen encryptie
2. SCHEMABINDING: Bind het schema van alle gebruikte objecten
3. RETURNS NULL ON NULL INPUT: Als deze optie aan staat, wordt NULL geretourneerd als ook maar één parameter NULL bevat
4. CALLED ON NULL INPUT: Dit is de default en betekent dat een scalar function uitgevoerd wordt ook als er een parameter NULL is
5. EXECUTE AS: Dit wordt uitgevoerd onder verscheidene contexts.

UDFs kunnen zelf ook meer UDFs aanroepen (nesting).

UDF performance considerations

UDFs dienen erg efficiënt te zijn omdat zij uitgevoerd worden voor elke rij in een tabel. Een scalar UDF in een SELECT die wordt toegepast op kolommen, wordt uitgevoerd voor elke rij die wordt opgehaald.

Een scalar UDF in de WHERE clause die een result set beperkt wordt uitgevoerd voor elke rij in de gerefereerde tabel.

Het gebruik van UDFs voorkomt dat queries worden geparallelliseerd