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;

Reacties

Een reactie posten

Populaire posts van deze blog

[SQL Server] varchar vs nvarchar

-
Bij het maken van een tabel in SQL Server 2008 heb je de mogelijkheid om een veld van het type varchar of nvarchar te voorzien. Wat is nu eigenlijk het verschil? Het verschil zit hem in het volgende: varchar Varchar heeft een maximum van 8000 tekens. Er wordt 1 byte per teken gereserveerd en wordt gebruikt om ASCII en ANSI in op te slaan. nvarchar Nvarchar neemt twee keer zoveel ruimte in als varchar. Dit komt omdat per teken twee bytes wordt gereserveerd en daarom kunnen er 4000 tekens worden opgeslagen. Dit type wordt gebruikt bij Unicode. Dus stel dat je een klantensite heb met invoerveleden waar wereldwijd gebruik van wordt gemaakt dan moet je gebruik maken van nvarchar. (n)char En dan is er ook nog (n)char. Deze string wordt opgevuld met spaties totdat de opgegeven lengte is bereikt.
Meer lezen

[C#] Class serialiseren en deserialiseren

-
Enkel even een code dump deze keer: public void Serialize(Configuration c) { XmlSerializer serializer = new XmlSerializer( c.GetType() ); StreamWriter streamwrite = new StreamWriter("config.xml"); serializer.Serialize(streamwrite, this); streamwrite.Close(); } public static Configuration Deserialize() { FileInfo file = new FileInfo("config.xml"); if (file.Exists) { StreamReader reader = new StreamReader("config.xml"); XmlSerializer ser = new XmlSerializer(typeof(Configuration)); Configuration c = (Configuration)ser.Deserialize(reader); reader.Close(); return c; } else return null; }
Meer lezen

Clean Code - The Liskov Substitution Principle

-
Het LSP heeft te maken met sub classes. Om het Liskov Substitution Principle correct toe te passen moeten alle sub classes vervangen kunnen worden door hun base class zonder dat de aanroepende code hier iets van merkt of aanpassingen hier voor hoeft te doen. Een bekend voorbeeld dat vaak als voorbeeld wordt gebruikt om dit principe toe te lichten gaat over een rechthoek en een vierkant. De rechthoek is de base class en de vierkant de sub class. Omdat de vierkant de base class is, betekent dit dat hij o.a. de methodes SetWidth en SetHeight heeft overgenemen. Maar dit is overbodig voor een vierkant. Voor een vierkant hoeft alleen maar de size te worden opgegeven omdat natuurlijk alle kanten gelijk zijn. De oplossing voor dit stuk is om vierkant niet te laten erven van rechthoek. Het worden dus beide aparte classes, zodat geen trucks hoeven te worden uitgehaald om alles logisch te maken en werkend te krijgen. Hiermee voorkom je dus ook ruis in de code.  Met de volgende checks kun ...
Meer lezen