MS Sql 70-461: Chapter 2

Les 1

De Select clause heeft twee hoofdverantwoordelijkheden:

1) Het evalueert de expressies die de attributen definieren in het resultaat van de query, het toewijzen van aliasses indien nodig;

2) Toepassen van een Distinct clause om dubbele rijen te elimineren.

In de Select clause kan op drie manieren een alias worden aangemaakt. De eerste variant heeft de algemene voorkeur.

1) AS : e.Id as EmployeeId;

2) : e.Id EmployeeId;

3) : EmployeeId = e.Id.

Een Select zonder From wordt uitgevoerd op een denkbeeldige tabel: Select 10 as Cijfer, 'abc' as Tekst.

Een irregular identifier is een identifier die niet voldoet aan de standaard toegestane naamgeving. Toegestande naamgevingen moeten beginnen met letter [a-z], een underscore, apenstaartje of hekje. Opvolgende karakters mogen een (decimaal)nummer, apenstaartje, dollarteken, hekje of underscore zijn. Als een identifier van deze standaard afwijkt moet het worden omringd door een dubbele quote of blokhaken. Bijvoorbeeld: "2006" of [2006], maar y2006 hoeft niet omringd te worden omdat deze begint met een letter.

Les 2

Data Types

Physical data indepence: de formattering zoals deze is opgeslagen in de database (bijv een datum) is niet onze zorg. Onze zorg is het correct formatteren van de data op het moment dat deze wordt gepresenteerd aan de gebruiker.

Het kiezen van het juiste data type is belangrijk:

1) functies en operatoren zijn er afhankelijk van. Bijv het plusteken voegt bij tekstuele data typen, de waarden samen, maar bij numerieke waarden telt hij de waarden bij elkaar op.

2) Opslagruimte op de HDD. Vaak heeft de performance van een query te maken met het aantal I/O reads. Een query die minder leest, wordt sneller uitgevoerd. Stel dat een attribuut (kolom) altijd een score bijhoud in een range van 0-100, dan is het zonde om hiervoor een INT te gebruiken omdat deze 4 bytes per value nodig heeft, terwijl TINYINTmaar 1 byte per value nodig heeft. Ditzelfde gaat ook op als enkel een datum moet worden opgeslagen: DATETIME neemt 8 bytes in beslag, terwijl DATE 3 bytes in beslag neemt. Kortom gebruik altijd het kleinste type.

FLOAT (8 bytes) en REAL (4 bytes) zijn onnauwkeurige data types. Deze zijn enkel nuttig als erg grote of juist erg kleine getallen moeten worden opgeslagen, maar de precisie niet erg belangrijk is.

Fixed types zijn CHAR, NCHAR en BINARY. Bij bijv CHAR(30) worden altijd 30 karakters opgeslagen, ook al vul je er maar 10.

Bij dynamische of variabele types worden enkel de benodigde karakters opgeslagen.

VOor strings kan gekozen worden voor CHAR, VARCHAR of NCHAR, NVARCHAR. De eerste neemt 1 byte in beslag en de laatste 2 bytes per karakter. Dit heeft te maken met de ondersteuning voor meerdere talen. Gebruik de N-variant als data internationaal is of als de applicatie werkt met Unicode.

Een karakter in Unicode wordt altijd voorafgegaan met een hoofdletter N. Bijvoorbeeld: N'abcdefg'. Bij expressies dient er rekening te worden gehouden dat de waarden van hetzelfde type zijn, anders vind er impliciete conversie plaats, wat weer vertraging oplevert.

Bij een expressie van waarden met dezelfde data type is het resultaat ook van datzelfde type. Voorbeeld: 5/2 = 2 omdat 5 en 2 beide als INT worden beschouwd.

Een data type kiezen voor keys

Een surrogate key is een key die als enkel doel heeft om gebruikt te worden als key. Men gebruikt deze keys doorgaans:

- voor een identity colomn: een property die automatisch numerieke keys genereert in een attribuut.

- sequence object: Een onafhankelijk object in de DB van waar een nieuw opvolgende waarde kan worden gebaseerd. Is niet gebonden aan een kolom, maar is DB breed.

- Nonsequential GUIDs - om een GUID in een attribuut op te slaan. De T-Sql functie NEWID kan worden gebruikt om een nieuw GUID te genereren.

- Sequential GUIDs - opvolgende GUIDs kunnen gegenereert worden door de T-Sql functie NEWSEQUENTIALID.

- Eigen oplossingen - als bovenstaande niet geschikt zijn, kan een eigen oplossing gekozen worden.

GUIDs nemen 16 bytes in beslag en zijn daardoor dus best groot.

De clustered index kolommen worden gebruikt door alle nonclustered indexes intern om rijen in de tabel te lokaliseren. Dus als een clustered index wordt gedefinieerd op kolom X, een nonclustered index op kolom A, B en C, dan wordne de nonclustered indexes intern gecreeerd op kolom (A, X), (B, X) en (C, X).

Bij sequential index keys komen alle rijen aan de rechterkant van de index. Als een page vol is, dan maaktSQL Server een nieuwe page aan en vult deze. Dit resulteert in minder fragmentatie in de index, wat de read performance ten goede komt.

Als data geladen wordt van meerdere sessions kan er page latch contention optreden. Latches zijn objecten die gebruikt worden om toegang tot database pages te synchroniseren.

Bij non sequential keys (zoals NEWID) wordt een classic page split toegepast als een rij wordt toegevoegd aan een volle page. Bij een classic split wordt een nieuwe page aangemaakt en wordt de helft van de volle page verplaatst naar de nieuwe page. Het nadeel hiervan is index fragmentatie.Splitsen index fragmentatie kan verholpen worden door periodiek index rebuilds uit te voeren.

Datetime functies

Met de volgende functies kan de huidige datum/tijd worden opgehaald:

- GETDATE - T-Sql specifiek. Geeft huidige datum + tijd o.b.v. Sql Server Instance. 

- CURRENT_TIMESTAMP - Standaard Sql. Zelfde als GETDATE.

- GETUTCDATE - geeft de UTC datum + tijd als DATETIME waarde.

- SYSDATETIME - retourneert de waarde als de meer preciesere DATETIME2 

- SYSUTCDATETIME - zelfde als GETUTCDATE maar retourneert een DATETIME2 waarde

- SYSDATETIMEOFFSET - zelfde als SYSDATETIME maar met offset

Met de volgende functies kunnen delen van een datum worden gehaald of kan een datum worden samengesteld:

- DATEPART - heel een gedeelte van een datum/tijd uit een datum: DATEPART(month, '20160323') = 3 (maart)

- DATENAME - zelfde als DATEPART maar geeft de naam terug ipv het getal. Dus "march" i.p.v.3. Text is afhenkelijk van de taal tijdens de session.

Met de volgende functies kan een datum worden samengesteld:

- DATEFROMPARTS

- DATETIME2FROMPARTS

- DATEFROMPARTS

-DATETIMEOFFSETFROMPARTS

- SMALLDATETIMEFROMPARTS

- TIMEFROMPARTS

EOMONTH geeft de laatste dag van de maand terug op basis van de opgegeven datum.

Verder zijn er nog de overige functies:

- DATEADD - optellen of aftrekken van datum eenheden. Bijv een jaar optellen of dagen aftrekken van een bepaalde datum

- DATEDIFF - geeft het verschil terug tussen twee datums in het opgegeven datumdeel (bijv dagen).

- SWITCHOFFSET - verandert de huidige offset van de tijd naar de opgegeven offset

- TODATETIMEOFFSET - wordt gebruikt om een DATETIMEOFFSET te maken op basis van 2 waarden

String functies

Twee manieren om strings samen te voegen:

1) + teken: als een van de NULL is, dan is het samengevoegde resultaat ook NULL

2) CONCAT methode: als een waarde NULL is, dan wordt een lege string gebruikt i.p.v. NULL.

Substring en posities functies

Met onderstaande functies kunnen gedeeltes uit een string worden gehaald:

- SUBSTRING - kan een gedeelte uit een string halen: SUBSTRING('abcde', 1, 3) = 'abc'

- LEFT

- RIGHT

- CHARINDEX - geeft de index terug van het eerste voorkomen waarnaar gezocht moet worden: CHARINDEX(' ', 'Hello World') = 6

- PATINDEX - zelfde als CHARINDEX maar dan op basis van een patroon: PATINDEX('%[0-9]%', 'abcd 12 efgh') = 6

- LEN - geeft de lengte van de string terug

- DATALENGTH - geeft grootte van de string in bytes terug

String manipulatie functies

Met onderstaande functies kunnen strings worde gemanipuleerd:

- REPLACE - Vervang bepaalde karakters in een string met een ander karakter: REPLACE('1.2.3', '.', '/') = '1/2/3'

- REPLICATE - Herhaald de opgegeven string een aantal keer: REPLICATE('a', 5) = 'aaaaa'

- STUFF - vervang een gedeelte van een string: STUFF('x,y,z', 1, 3, '') = ',z'. Vervang in de string 'x,y,z,' vanaf positie 1, 3 positie lang met ''.

String formatting functies

String kunnen worden opgemaakt m.b.v. onderstaande functies:

- UPPER

- LOWER

- LTRIM

- RTRIM

- FORMAT - format een string op basis van een format string en eventueel een culture: FORMAT(1759, '000000') = 001759

Case expressie

Vaak wordt naar CASE gerefereert als een statement, maar dat is het niet. Een statement voert een soort van actie uit of bepaalde de flow, en dat is niet wat CASE doet. CASE retourneert een waarde en is daarom een expressie.

CASE kent twee vormen: simple en searched. Een simple CASE vergelijkt de input expression met meerdere mogelijke scalar when expressies en geeft de result expression terug.

De searched vorm is flexibeler. In plaats van vaste waarden in de WHEN clause, worden er predicates gebruikt, bijv: CASE WHEN unitprice < 20 THEN 'low' ELS 'high'.

In plaats van CASE kunnen soms ook onderstaande functies gebruikt worden:

- COALESCE -  accepteert een lijst met expressies en geeft de eerste waarde terug die niet NULL is of NULL als alle waarden NULL zijn: COALESCE(NULL, 'y', 'z') = 'y'

- NULLIF - zelfde als COALESCE alleen accepteert 2 inputs: ISNULL(region, '') = waarden van region indien gevuld anders ''.

- ISNULL (T-Sql)

- IIF (T-Sql)

- CHOOSE (T-Sql) - Bepaalt op basis van een positie de waarde die moet worden teruggegeven: CHOOSE(2, 'x','y','z') = 'y'