MS Sql 70-461: Chapter 10

Les 1

INSERT VALUES

Met het volgende stament kan een rij worden aangemaakt in de MyOrders tabel

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES(2, 19, '20120620', N'USA', 30.00);

Het opgeven van de kolomnamen na de tabel is optioneel, maar wel een best practice. De naam van het identity veld (id) is niet opgegeven omdat deze automatisch wordt gevuld door Sql. Als toch een id moet worden opgegeven, dan moet gebruik gemaakt worden van de IDENTITY_INSERT optie:

SET IDENTITY_INSERT ON;

Na de INSERT moet de identity weer worden uitgezet:

SET IDENTITY_INSERT OFF;

Om deze optie te gebruiken moet de gebruiker eigenaar van de tabel zijn of ALTER permissies hebben op de tabel.

Als bij het toevoegen van een rij geen waarde wordt opgegeven voor een kolom, dan wordt gebruik gemaakt van de DEFAULT constraint, indien aanwezig. Een andere manier is door gebruik te maken van de DEFAULT keyword:

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

VALUES(3, 17, DEFAULT, N'USA', 30.00);

Als geen waarde wordt opgegeven voor een kolom gaat Sql Server eerst kijken of de kolom zijn waarde automatisch krijgt (IDENTITY property of default constraint). Als dat niet het geval is gaat Sql Server na of de kolom NULLs toestaat, alls dat niet zo is, treedt er een fout op.

Meerdere rijen kunnen als volgt in één statement worden toegevoegd in één transactie:

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

VALUES (2, 11, '20120620', N'USA', 50.00), 

 (5, 13, '20120620', N'USA', 40.00), 

 (7, 17, '20120620', N'USA', 45.00);

INSERT SELECT

Dit statement voegt het resultaat van een query toe een de opgegeven tabel:

SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)

 SELECT orderid, custid, empid, orderdate, shipcountry, freight

 FROM Sales.Orders

 WHERE shipcountry = N'Norway';

SET IDENTITY_INSERT Sales.MyOrders OFF;

INSERT EXEC

Met dit statament kan het resultaat van een dynamische batch of SP worden toegevoegd aan een query:

SET IDENTITY_INSERT Sales.MyOrders ON;

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)

 EXEC Sales.OrdersForCountry @country = N'Portugal';

SET IDENTITY_INSERT Sales.MyOrders OFF;

SELECT INTO

Het SELECT INTO statement maakt een tabel aan op basis van de definitie van de brontabel in voegt het resultaat van de query toe aan de nieuwe tabel. Aspecten die worden gekopieerd van de brontabel zijn namen, types, nullability en IDENTITY property. Aspecten die niet worden gekopieerd zijn indexes, triggers, permissions en meer.

SELECT orderid, custid, orderdate, shipcountry, freight

INTO Sales.MyOrders

FROM Sales.Orders

WHERE shipcountry = N'Norway';

De definitie van de kolommen van de nieuwe tabel kan worden gemanipuleerd:

SELECT

 ISNULL(orderid + 0, -1) AS orderid, -- get rid of IDENTITY property

                                                              -- make column NOT NULL

 ISNULL(custid, -1) AS custid, -- make column NOT NULL

 empid,

 ISNULL(CAST(orderdate AS DATE), '19000101') AS orderdate, -- create DATE column instead DATETIME

 shipcountry, freight

INTO Sales.MyOrders

FROM Sales.Orders

WHERE shipcountry = N'Norway';

Een voordeel van SELECT INTO is als het recovery model niet op 'full' staat, er minimaal gelogd wordt en daardoor de insert sneller wordt uitgevoerd.

Les 2

UPDATE statement

Een standaard UPDATE statement ziet er als volgt uit:

UPDATE Sales.MyOrderDetails

 SET discount += 0.05

WHERE orderid = 10251;

UPDATE based on join

Standaard Sql ondersteund geen joins in UPDATE statements, T-Sql wel:

UPDATE OD

 SET OD.discount += 0.05

FROM Sales.MyCustomers AS C

 INNER JOIN Sales.MyOrders AS O

 ON C.custid = O.custid

 INNER JOIN Sales.MyOrderDetails AS OD

 ON O.orderid = OD.orderid

WHERE C.country = N'Norway';

Nondeterministic UPDATE

Een statement in nondeterministic als meerdere rijen van de bron overeenkomen met één doel rij. Als dit voorkomt, dan voert Sql Server toch de update uit en selecteert een willekeurige rij van de brontabel.

UPDATE en Table Expressions

Het is ook mogelijk om een update uit te voeren middels een table expressie zoals CTEs en derived tables. Het voordeel hiervan is dat in SSMS de SELECT statement geselecteerd kan worden zodat het resultaat getoond wordt van datgaan dat wordt bijgewerkt zonder de update uit te voeren:

WITH C AS

(

 SELECT 

   TGT.custid,

   TGT.country AS tgt_country, 

   SRC.country AS src_country,

   TGT.postalcode AS tgt_postalcode, 

   SRC.postalcode AS src_postalcode

 FROM Sales.MyCustomers AS TGT

 INNER JOIN Sales.Customers AS SRC

 ON TGT.custid = SRC.custid

)

UPDATE C

 SET tgt_country = src_country,

 tgt_postalcode = src_postalcode;

Ditzelfde kan ook met een derived table:

UPDATE D

 SET tgt_country = src_country,

 tgt_postalcode = src_postalcode

FROM (

 SELECT TGT.custid,

   TGT.country AS tgt_country, 

   SRC.country AS src_country,

   TGT.postalcode AS tgt_postalcode, 

   SRC.postalcode AS src_postalcode

 FROM Sales.MyCustomers AS TGT

 INNER JOIN Sales.Customers AS SRC

   ON TGT.custid = SRC.custid

 ) AS D;

UPDATE based on a variable

Het resultaat van een waarde die wordt bijgewerkt kan opgeslagen worden in een variabele:

DECLARE @newdiscount AS NUMERIC(4, 3) = NULL;

UPDATE Sales.MyOrderDetails

 SET @newdiscount = discount += 0.05

WHERE orderid = 10250

 AND productid = 51;

SELECT @newdiscount;

Deze query update eerst de kolom discount met 5 procent en zet vervolgens de nieuwe waarde in de variabele @newdiscount.

UPDATE all-at-once

Alle kolommen die in de SET worden bijgewerkt, worden conceptueel op hetzelfde moment uitgevoerd. 

DECLARE @add AS INT = 10;

UPDATE dbo.T1

 SET col1 += @add, col2 = col1

WHERE keycol = 1;

SELECT * FROM dbo.T1;

Deze query zal niet de nieuwe waarde van col1 (waarde col1 + 10) in col2 plaatsen, maar de originele waarde van col1.

Les 3

In Sql Server kunnen op twee manieren rijen verwijderd worden uit een tabel, namelijk met het DELETE statement en het TRUNCATE statement.

DELETE statement

DELETE FROM Sales.MyOrderDetails

WHERE productid = 11;

Bovenstaand statement verwijderd alle order details met product i 11. De WHERE clause is optioneel. Zonder deze clause worden alle rijen verwijderd in de tabel.

Een DELETE statement wordt volledig gelogd, dus bij het verwijderen van heel veel data kan het verwijderen lang duren. Bijkomend nadeel is dat hij transaction log erg toeneemt in grootte. Om dit probleem te verhelpen kan gebruik gemaakt worden van een loop:

WHILE 1 = 1

BEGIN

 DELETE TOP (1000) FROM Sales.MyOrderDetails

 WHERE productid = 12;

 IF @@rowcount < 1000 BREAK;

END

TRUNCATE statement

TRUNCATE verwijderd alle rijen van een tabel en ondersteund niet de WHERE clause.

TRUNCATE TABLE Sales.MyOrderDetails;

Belangrijke verschillen tussen DELETE en TRUNCATE zijn:

- TRUNCATE schrijft veel minder weg in het log dan DELETE. TRUNCATE logt enkel welke pages zijn gedealloceerd

- DELETE reset de IDENTITY kolom niet, terwijl TRUNCATE dit wel doet. Als de identity value toch bewaard moet blijven bij een truncate, dan kan dit met een variabele icm de IDENT_CURRENT function.

- Het DELETE statement kan rijen verwijderen met een FK. TRUNCATE kan dit niet, ook al verwijzen de rijen niet naar rijen in de base table. 

- Het DELETE statement kan worden toegepast op een indexed view, TRUNCATE niet.

- Het DELETE statement heeft DELETE permissies nodig om te worden uitgevoerd en TRUNCATE ALTER permissies.

DELETE based in a join

Het DELETE statement ondersteund het gebruik van joins:

DELETE FROM O

FROM Sales.MyOrders AS O

 INNER JOIN Sales.MyCustomers AS C

 ON O.custid = C.custid

WHERE C.country = N'USA';

Een subquery is ook toegestaan:

DELETE FROM Sales.MyOrders

WHERE EXISTS

 (SELECT *

 FROM Sales.MyCustomers

 WHERE MyCustomers.custid = MyOrders.custid

 AND MyCustomers.country = N'USA');

Beide statements kennen geen verschil que performance omdat ze beide op dezelfde manier worden geoptimaliseerd. De subquery wordt wel als standaard beschouwd.

DELETE using Table Expression

Ook worden CTEs en derived tables ondersteund om rijen te verwijderen:

WITH OldestOrders AS

(

 SELECT TOP (100) *

 FROM Sales.MyOrders

 ORDER BY orderdate, orderid

)

DELETE FROM OldestOrders;