MS Sql 70-461: Chapter 8

Les 1

In system tables slaat Sql Server zijn systeem data op. 

Tabellen die data bevatten worden vaak ook wel base tables genoemd. Dit om onderscheid aan te brengen met andere objecten of expressies die afgeleiden zijn van een tabel, zoals views of queries. Variaties van tabellen zijn:
- Temporary tables: base tables die enkel bestaan in tempdb tijdens de huidige sessie

- Table variables: variabelen die data opslaan voor de duur van een batch

- Views: samengesteld op basis van een query. Een view is geen base table.

- Indexed views: Opgeslagen data die gedefinieerd is als een view en bijgewerkt worden alsde base tables worden bijgewerkt

- Derived tables en table expressions: subqueries waarnaar wordt gerefereerd binnen queries.

Een tabel maken

Er zijn twee manieren om een tabel aan te maken: CREATE TABLE statement en het INSERT INTO statement. 

Bij CREATE TABLE:

moet je: 

  - de naam van de tabel opgeven en 

  - de kolommen specificeren.

    * de lengte van de data

    * De precisie van een getal

    * speciale soorten kolommen, zoals bijv IDENTITY

    * De collation van een kolom

kan je ook specificeren:

  - kolommen  

  - contraints

    * nullability (kolom NULL of niet)

    * standaard en check constrains

    * optionele kolom collations

    * FK constrains

   * Unique constrains

  - table storage definitions

    * Filegroup (zoals ON [PRIMARY], betekent de primary file group)

    * partition schema

    * table compression

Database schema

Elke tabel hoort bij een set van gegroepeerde objecten, wat in een database een schema wordt genoemd. Binnen een schema moet een tabel uniek zijn, dezelfde naam van een tabel kan voorkomen in een andere schema.

Er zijn vier built-in database schemas die niet kunnen worden verwijderd:

1) dbo: standaard schema voor nieuwe objecten die geen schema hebben gespecificeerd

2) guest: schema voor objecten die beschikbaar worden gesteld voor gasten. Wordt zelfden gebruikt.

3) INFORMATION_SCHEMA: wordt gebruikt door de Information Schema Views

4) sys: schema dat is gereserveerd door Sql Server voor systeem objecten zoals system tables en views.

Elk database schema moet eigendom zijn van precies één geauthenticeerde gebruiker. Deze gebruiker kan vervolgens op zijn beurt verder rechten uitdelen op dit schema.

CREATE SCHEMA Production AUTHORIZATION user

Ht schema Production is nu eigendom van de gebruiker user. 

Tabellen kunnen naar een ander schema verplaatst worden d.m.v. het ALTER SCHEMA TRANSFER statement: ALTER SCHEMA sales TRANSFER Production.Categories (verplaats de tabel Production.Categories naar het schema sales).

Kiezen van kolom data types

Richtlijnen voor het kiezen van data types voor kolommen van tabellen zijn:

- Probeer het data type te kiezen dat het minste geheugen in gebruik neemt;

- Als de kans kans groot is als tekst in een gevarieerde lengte wordt opgeslagen, kies dan voor NVARCHAR of VARCHAR. Als bij voorbaat bekend is dat de lengte kort is, kies dan voor de fixed length types NCHAR en CHAR

- DATE, TIME en DATETIM2 kunnen data types preciezer opslaan dan DATETIME en SMALLDATETIME

- Gebruik VARCHAR(max), NVARCHAR(MAX) en VARBINARY(MAX) in plaats van de depricated TEXT, NTEXT en IMAGE data types.

- Gebruik ROWVERSION ipv de depricated TIMESTAMP

- DECIMAL en NUMERIC zijn hetzelfde. Gebruik deze types in plaats van de minder precieze FLOAT en REAL welke afrondings issues hebben.

NULL en standaardwaarden

NULL is geen waarde, NULL is een manier om te zeggen dat de waarde volledig onbekend is. Als de waarde in een kolom optioneel is, kan deze kolom gedefinieerd worden als NULL, als de waarde va een kolom verplicht is, gebruik dan NOT NULL. Als een kolom in eerste instantie NULLs accepteert en later veranderd wordt naar NOT NULL, dan moet een default waarde opgegeven worden voor de attributen die NULLs hebben. Dit kan middels de DEFAULT constraint.

Identity property en sequence nummers

De identity property kan toegewezen worden aan een kolom wiens waarde automatisch verhoogt moet worden. Deze property kan maar op 1 kolom binnen de tabel worden toegewezen.

Computed columns

Het is mogelijk om kolommen te definieeren die berekent worden op basis van waarden in andere kolommen of op basis van T-Sql functies:

CREATE TABLE Sales.OrderDetails

(

 orderid INT NOT NULL,

…

 initialcost AS unitprice * qty -- computed column

);

Table compression

De data in in een tabel kan worden gecomprimeerd om opslagruimte te besparen. Dit komt op twee niveaus:

- Row: Voor row-level compressiepast Sql Server een compacter opslagformaat toe voor elke rij in de tabel

- Page: Page-level compressie bevat op rij niveau plus extra compressie alogoritmes die toegepast kunnen worden op page level.

CREATE TABLE Sales.OrderDetails

(

 orderid INT NOT NULL,

…

 )

 WITH (DATA_COMPRESSION = ROW);

Om te kijken of het zin heeft om compressie toe te passen voor een tabel kan gebruik gemaakt worden van de SP sp_estimate_data_compression_savings.

Altering a table

Nadat een tabel is aangemaakt kan deze nog aangepast worden door het ALTER TABLE command. Met dit command kan:

- een kolom worden toegevoegd of verwijderd

- Het data type worden aangepast

- de nullability worden aangepast van een kolom

- een constraint worden toegevoegd of verwijderd: PK, uniek, FK, Check en Default

- een kolomnaam worden aangepast

- een identity property worden toegevoegd

- een identity property worden verwijderd

Les 2

Declarative data integrity is als de manier van data validatie opgeslagen is in de tabel zelf. Data integreteit wordt bewerkstelligd d.m.v. constraints.

Using constraints

De beste manier om data integriteit toe te passen in tabellen in Sql Server is d.m.v. het maken of declareren van constraints op base tables. Dit kan door het CREATE TABLE of ALTER TABLE statement. De eerste versies van Sql maakten gebruik van rules, maar die worden in toekomstige versie van Sql Server niet meerondersteund en zijn dus depricated.

In Sql Server zijn alle constraints database objects, en moeten daarom een unieke naam hebben. Om deze reden is het handig om een conventie te verzinnen voor de naam van een constraint.

Primary key constraints

Elke tabel in een relationele database zou een waarde per rij moeten hebben die uniek is over alle rijen. Een kolom (of een combinatie van kolommen) van een tabel die elke rij identificeert wordt de natural key of business key van een tabel genoemd. Deze key kan gebruikt worden als primary key, maar db-ontwerpers vinden het makkelijker om een nieuwe kolom aan te maken en deze een uniek nummer te geven en geven deze vervolgens de PK. De natural key krijgt dan een unique constraint.

CREATE TABLE Production.Categories

(

 categoryid INT NOT NULL IDENTITY,

 categoryname NVARCHAR(15) NOT NULL,

 description NVARCHAR(200) NOT NULL,

 CONSTRAINT PK_Categories PRIMARY KEY(categoryid)

);

of 

ALTER TABLE Production.Categories

 ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid);

GO

Een primary key:

- kan geen NULLs bevatten

- elke waarde moet uniek zijn

- er kan maar 1 PK in een tabel gedefinieerd zijn

Als een PK wordt aangemaakt, dan wordt er door Sql Server op de achtergrond een unique index voor die kolom aangemaakt.

Alle PK constraints kunnen worden opgevraagd worden de view sys.key_constraints.

Unique constraints

Met een unique constraint kan afgedwongen worden dat een waarde van een kolom uniek moet zijn binnen een tabel. Net als een PK wordt door Sql Server een unique index aangemaakt. De kolom hoeft niet perse NOT NULL te zijn, maar dan kan maar 1 rij NULL hebben. Unique constraints kunnen net als primary keys worden toegepast op computed columns.

Foreign key constraints

Een foreign key (FK) is een kolom in de ene tabel die dient als een link naar een andere tabel. Deze andere tabel wordt vaak de look up table genoemd.

ALTER TABLE Production.Products WITH CHECK

 ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid)

 REFERENCES Production.Categories (categoryid)

GO

WITH CHECK geeft aan dat het aanpassen van de tabel moet falen als er niet voldaan wordt aan de constraint. Bijv. een categoryid komt niet voor in de tabel Categories.

De volgende regels zijn van toepassing bij het maken van FK's

- De kolommen moeten dezelfde data types hebben en collation (in geval van strings)

- De kolommen moeten een unique index hebben, hetzij via een PK of een unique constraint

- Computed columns kunnen ook dienen als een FK.

Een join kan sneller worden gemaakt als de tabel die de FK bevat een nonclustered index heeft op de FK kolom.

Met de query 

SELECT *

FROM sys.foreign_keys

WHERE name = 'FK_Products_Categories';

kan opgehaald worden welke tabel de FK FK_Products_Categories heeft.

Check constraints

Met een check constraints kan een conditie worden opgegeven waareen de waarde van een kolom moet voldoen:

ALTER TABLE Production.Products WITH CHECK

 ADD CONSTRAINT CHK_Products_unitprice

 CHECK (unitprice>=0);

GO

Voordelen van een check constraint zijn:

- de expressies zijn hetzelfde als die van een WHERE clause

- De constraint zit in de tabel, dus wordt hij altijd uitgevoerd

- Ze zijn sneller dan alternatieve manieren zoals bijv triggers

Wel dient rekening te worden gehouden met:

- NULLs, ALs de kolom NULLs toestaat, dan moet de constraints hier rekening mee houden. Als NULL bijv wordt opgeslagen dan passeert deze de conditie unitprice>=0. 

- De foutmeldingen van check constraint kunnen niet aangepast worden.

- Een check constraints kan niet de actie van een update testen. Hiervoor zou een trigger nodig zijn. Bijv. er kan niet getest worden of de nieuwe waarde niet meer is dan 10% dan de oude waarde.

Met onderstaande query kunnen alle constraints van een tabel worden opgevraagd. De parent_object_id is het object_id van de tabel

SELECT *

FROM sys.check_constraints

WHERE parent_object_id = OBJECT_ID('Production.Products');

Default constraints

Een default constraint kan worden gebruikt als bij een INSERT geen waarde is opgegeven voor een kolom. Een lijst met default constraints kan worden opgehaald met de query:

SELECT *

FROM sys.default_constraints