MS Sql 70-461: Chapter 11

Les 1

De IDENTITY column property en het sequence object zijn beide manieren om automatisch een nummer te laten genereren. De IDENTITY property bestaat al lang, terwijl  het sequence objectin Sql Server 2012 is geïntroduceerd,

Using the IDENTITY column property

IDENTITY is een eigenschap van een kolom. De eigenschap voorziet de kolom automatisch van een waarde als een rij wordt aangemaakt. Voor een IDENTITY kan een increment en een seed worden opgegeven. Increment: Hoeveel moet de vorige waarde van IDENTITY worden verhoogd. Seed: de beginwaarde van de IDENTITY kolom.

T-Sql biedt drie maniere om de laatste identity waarde op te halen:

- De SCOPE_IDENTITY functie geeft de laatst gegenereerde identity waarde terug van de sessie in de huidige scope

- De @@IDENTITY functie geeft de laatst gegenereerde identity waarde terug, ongeacht de scope

- De IDENT_CURRENT functie heeft een tabel als input en geeft de laatst gegenereerde identity waarde van deze tabel.

Het verschil tussen @@IDENTITY en SCOPE_IDENTITY

- Een INSERT zorgt er voor dat een nieuwe identity waarde wordt gegenereerd;

- Een aanroep van SP1 doet ook een INSERT, wat wederom een nieuwe identity waarde genereerd;

- SCOPE_IDENTITY: zal de waarde van de eerste INSERT statement teruggeven (zelfde sessie en scope)

- @@IDENTITY: zal de gegenereerde identity waarde van SP1 teruggeven (zelfde sessie, scope onafhankelijk).

Bij een TRUNCATE wordt de seed weer op 1 gezet. 

Om de seed weer met 1 te laten beginnen kan de volgende command worden uitgevoerd:

DBCC CHECKIDENT('Sales.MyOrders', RESEED, 4);

Standaard biedt IDENTITY geen uniekheid. Dit kan enkel bewerkstelligd worden door van de kolom een PK te maken of een UNIQUE constraint.

Ook kunnen er gaten onstaan tussen de waarden. Als een rij in het midden van de tabel wordt verwijderd, wordt de bijbehorende identity waarde niet hergebruikt. Dit gebeurt ook als een INSERT statement niet lukt, de waarde wordt dan niet teruggedraaid.

Als bij de IDENTITY het maximaal aantal te gebruiken nummers is behaald, dan kunnen er geen nieuwe rijen meer worden toegevoegd, de INSERT zal falen.

Using the sequence object

In Sql Server 2012 is het sequence object geïntroduceerd. In tegenstelling tot de IDENTITY kolom eigenschap, is het sequence object een onafhankelijk object in de database. Verschillen tussen de IDENTITY kolom roperty en het sequence object zijn:
- De IDENTITY eigenschap is gebonden aan een kolom van een tabel
- Soms is het nodig om over meerdere tabellen een unieke sleutel te definiëren, dit kan niet met de IDENTITY kolom property
- Soms is het nodig dat eerst de waarde wordt gegenereerd voordat deze wordt gebruikt. Met IDENTITY moet eerst een rij worden aangemaakt voordat de waarde bekend is.
- Een IDENTITY-waarde kan niet worden bijgewerkt
- IDENTITY ondersteund geen cycling
- Een TRUNCATE statement reset de identity waarde.

Het sequence object kent bovenstaande beperkingen niet. Een sequence is een database object en wordt als volgt aangemaakt:

CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CYCLE;

Als geen type wordt opgegeven wordt het type BIGINT gebruikt. Een type kan worden opgegeven met AS na de naam van de sequence. Sequence ondersteunt de volgende properties:
- INCREMENT BY: de increment waarde. De standaard is 1
- MINVALUE: De minimale waarde die ondersteunt wordt. De standaard is de minimale waarde van het type
- MAXVALUE: De maximale waarde die ondersteunt wordt. De standaard is de maximale waarde van het type:
- CYCLE of NO CYCLE: Definieert om de sequence te laten cyclen of niet
- START WITH: De sartwaarde van de sequence. Default is de minimale waarde van het type.

Met de  NEXT VALUE FOR functie kan de volgende waarde worden opgevraagd van een sequence:

SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

Alles kan van een sequence gewijzigd worden, behalve het data type. De huidige kan bijv. opnieuw gezet worden met het volgende statement:

ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1;

Als een tabel geen gebruik maakt van de IDENTITY eignschap, maar van een sequence object, dan ziet een INSERT statement er als volgt uit:

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) VALUES
 (NEXT VALUE FOR Sales.SeqOrderIDs, 1, 2, '20120620'),
 (NEXT VALUE FOR Sales.SeqOrderIDs, 1, 3, '20120620'),
 (NEXT VALUE FOR Sales.SeqOrderIDs, 2, 2, '20120620');

De NEXT VALUE FOR functie kan ook gebruikt worden in een DEFAULT constraint. Op deze manier wordt automatisch de waarde opgeslagen als een nieuwe rij wordt toegevoegd:

ALTER TABLE Sales.MyOrders
 ADD CONSTRAINT DFT_MyOrders_orderid
 DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;

Het sequence object kent een caching optie die bepaalt hoe vaak de huidige sequence waarde naar schijf wordt geschreven of naar het geheugen. Een sequence met CACHE 100 schrijft eens per 100 wijzigingen naar de schijf. Sql Server houdt twee members in het geheugen, de ene houdt de huidige sequence waarde vast en de andere hoeveel waarden er nog over zijn. Hij schrijft dus maar 100 keer naar het geheugen en als de waarden op zijn, dan schrijft hij de 100 waarden naar schijf. Het voordeel hiervan is performance voor het toekennen van de sequence waarden. Het risico is dat een range van waarden verloren gaat als de service onverwachts wordt afgesloten.

Net als IDENTITY kunnen er gaten onstaan in de nummering bij en sequence object. Dit kan gebeuren als een transactie faalt waarin een gebruik gemaakt wordt van een sequence object. De verandering van de waarde van het sequence object wordt dan niet ongedaan gemaakt.

Er is een groot performance verschil tussen het gebruik van CACHE en NO CACHE. Met NO CACHE moet Sql Server voor elk request een nieuwe sequence waarde naar de schijf schrijven. In 2015 is de standaard cache waarde 50.

Met de SP sp_sequence_get_range kan een range van sequence waarden worden toegewezen van een bepaalde grootte. De @range_size parameter bevat de grootte range en de output parameter @range_first_value bevat de eerste waarde van die range.

De view sys.sequences kan gebruikt worden om de eigenschappen van sequences op te vragen.

Les 2

Met het MERGE statement kan data worden samengevoegd van een brontabel nar een doeltabel.

Using the MERGE statement

Voorbeeld van MERGE statement:

DECLARE

 @orderid AS INT = 1,

 @custid AS INT = 1,

 @empid AS INT = 2,

 @orderdate AS DATE = '20120620';

MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT

USING (VALUES(@orderid, @custid, @empid, @orderdate))

 AS SRC( orderid, custid, empid, orderdate)

 ON SRC.orderid = TGT.orderid

WHEN MATCHED THEN UPDATE

 SET TGT.custid = SRC.custid,

 TGT.empid = SRC.empid,

 TGT.orderdate = SRC.orderdate

WHEN NOT MATCHED THEN INSERT

 VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

Toelichting gebruik clauses van MERGE statement:

- MERGE INTO : Definieer de doeltabel. Kan gebruik maken van een alias

- USING : Defiieer de brontabel. Kan van een alias gebruik maken. De USING clause is hetzelfde ontworpen als de FROM clause van een SELECT statement. Er kan dus gebruik gemaakt worden van joins, derived tables, CTEs of een table function zoals OPENROWSET. De output van de USING cluase wordt beschouwd als de bron van de merge operatie.

- ON : In deze clause wordt de predicate bepaald die de rijen matched tussen bron- en de doeltabellen.

- WHEN MATCHED [AND ] THEN : Bepaalt wat er moet gebeuren (action) als een rij uit de brontabel matched met een rij uit de doeltabel. Omdat een rij in de doeltabel reeds bestaat kan het INSERT statement niet gebruikt worden. Alleen de statements UPDATE en DELETE zijn toegestaan. Als verschillende acties nodig zijn bij rijen die matchen, dan kunnen er meerdere WHEN MATCHED clauses worden gebruikt, welke met een andere predicate.

- WHEN NOT MATCHED [BY TARGET] [AND ] THEN : Bepaalt wat er moet gebeuren als er geen matches zijn. Omdat de doeltabel geen overeenkomende rij bevat is enkel de INSERT statement toegestaan. 

- WHEN NOT MATCHED BY SOURCE [AND ] THEN: Bepaalt wat er moet gebeuren als een rij uit de doeltabel niet voorkomt in de brontabel. Omdat de rij reeds bestaat, is de INSERT statement niet geldig, maar wel een UPDATE of DELETE.

Onderstaand een voorbeeld als de doeltabel een rij heeft die niet voorkomt in de brontabel. Deze rijen zullen worden verwijderd:

MERGE INTO Sales.MyOrders AS TGT

USING @Orders AS SRC

 ON SRC.orderid = TGT.orderid

WHEN MATCHED AND ( TGT.custid <> SRC.custid

 OR TGT.empid <> SRC.empid

 OR TGT.orderdate <> SRC.orderdate) THEN UPDATE

Lesson 2: Merging Data Chapter 11 389

 SET TGT.custid = SRC.custid,

 TGT.empid = SRC.empid,

 TGT.orderdate = SRC.orderdate

WHEN NOT MATCHED THEN INSERT

 VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)

WHEN NOT MATCHED BY SOURCE THEN

 DELETE;

Les 3

T-Sql kent een OUTPUT cluase voor modification statements, welke gebruikt kan worden om informatie terug te geven van de aangepaste rijen.

Working with the OUTPUT clause

De OUTPUT is in gebruik gelijkwaardig als de SELECT clause in die zin dan expressies gespecificeerd kunnen worden en hieraan aliasses kunnen worden gekoppeld. Een verschil met de SELECT clause is dat bij de OUTPUT clause de kolomnamen een prefix moeten krijgen: inserted of deleted.

INSERT with OUTPUT

De OUTPUT clause kan gebruikt worden in een INSERT statement om informatie terug te geven over de ingevoerde rijen.

INSERT INTO Sales.MyOrders(custid, empid, orderdate)

 OUTPUT

   inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate

 SELECT custid, empid, orderdate

 FROM Sales.Orders

 WHERE shipcountry = N'Norway';

Het resultaat van de OUTPUT clause kan ook in een tabel worden opgeslagen:

INSERT INTO Sales.MyOrders(custid, empid, orderdate)

 OUTPUT

   inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate

   INTO SomeTable(orderid, custid, empid, orderdate)

 SELECT custid, empid, orderdate

 FROM Sales.Orders

 WHERE shipcountry = N'Norway';

DELETE with OUTPUT

DELETE FROM Sales.MyOrders

 OUTPUT deleted.orderid

WHERE empid = 1;

UPDATE with OUTPUT

UPDATE Sales.MyOrders

 SET orderdate = DATEADD(day, 1, orderdate)

 OUTPUT

   inserted.orderid,

   deleted.orderdate AS old_orderdate,

   inserted.orderdate AS neworderdate

WHERE empid = 7;

Bij een UPDATE verwijdt de deleted prefix naar de originele waarde en de inserted prefix naar de nieuwe waarde.

MERGE with OUTPUT

De $action function in een MERGE statement geeft aan welke actie er is uitgevoerd. De geeft doet dit door een string waarde te retourneren: 'INSERT', 'UPDATE', 'DELETE'.

MERGE INTO Sales.MyOrders AS TGT

USING (VALUES(1, 70, 1, '20061218'),

 (2, 70, 7, '20070429'),

 (3, 70, 7, '20070820'),

 (4, 70, 3, '20080114'),

 (5, 70, 1, '20080226'),

 (6, 70, 2, '20080410'))

 AS SRC(orderid, custid, empid, orderdate )

 ON SRC.orderid = TGT.orderid

WHEN MATCHED AND ( TGT.custid <> SRC.custid

   OR TGT.empid <> SRC.empid

   OR TGT.orderdate <> SRC.orderdate) 

 THEN UPDATE

 SET TGT.custid = SRC.custid,

 TGT.empid = SRC.empid,

 TGT.orderdate = SRC.orderdate

WHEN NOT MATCHED THEN INSERT

 VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)

WHEN NOT MATCHED BY SOURCE THEN

 DELETE

OUTPUT

 $action AS the_action,

 COALESCE(inserted.orderid, deleted.orderid) AS orderid;

Output:

the_action    orderid

----------       -----------

INSERT      1

INSERT      5

UPDATE    2

UPDATE    3

Composable DML (Data Manipulation Language)

Met T-Sql kan iets gedefinieerd worden dat lijkt op een derived table wat gebaseerd is op een modification met een OUTPUT clause. Daar kan vervolgens een INSERT SELECT statement omheen worden gezet. Met de WHERE clause van deze outer SELECT kan vervolgens bepaald worden welk soort OUTPUT teruggegeven moet worden ('INSERT', 'UPDATE', 'DELETE'):

DECLARE @InsertedOrders AS TABLE

(

 orderid INT NOT NULL PRIMARY KEY,

 custid INT NOT NULL,

 empid INT NOT NULL,

 orderdate DATE NOT NULL

);

INSERT INTO @InsertedOrders(orderid, custid, empid, orderdate)

 SELECT orderid, custid, empid, orderdate

 FROM (MERGE INTO Sales.MyOrders AS TGT

              USING (VALUES(1, 70, 1, '20061218'),

                           (2, 70, 7, '20070429'),

                           (3, 70, 7, '20070820'),

                           (4, 70, 3, '20080114'),

                           (5, 70, 1, '20080226'),

                           (6, 70, 2, '20080410'))

                           AS SRC(orderid, custid, empid, orderdate )

             ON SRC.orderid = TGT.orderid

            WHEN MATCHED AND ( TGT.custid <> SRC.custid

                                             OR TGT.empid <> SRC.empid

                                             OR TGT.orderdate <> SRC.orderdate) THEN UPDATE

              SET TGT.custid = SRC.custid,

                      TGT.empid = SRC.empid,

                      TGT.orderdate = SRC.orderdate

           WHEN NOT MATCHED THEN INSERT

              VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate)

           WHEN NOT MATCHED BY SOURCE THEN

               DELETE

           OUTPUT

               $action AS the_action, inserted.*) AS D

WHERE the_action = 'INSERT';

SELECT * FROM @InsertedOrders;