MS Sql 70-461: Chapter 9

Les 1

Met views en  inline functions kan content worden gepresenteerd van één of meerdere base tables en kan hiervan de onderliggende complexe logica (zoals bijv joins) worden verborgen. 

In Sql Server kunnen views gebruikt worden om queries op te slaan en zodoende te herbruiken. Views gedragen zich bijna hetzelfde als tabellen. Het is ook mogelijk om insert, update en delete statements te gebruiken, maar wel met restricties.

Elke view is gedefinieerd door een SELECT statement, welke weer kan refereren naar meerdere base tables en andere views.

Views

Onderstaand een voorbeeld hoe een view gemaakt kan worden:

CREATE VIEW Sales.OrderTotalsByYear

 WITH SCHEMABINDING

AS

SELECT

 YEAR(o.orderdate) AS orderyear,

 SUM(od.qty) AS qty

FROM Sales.Orders AS o

 JOIN Sales.OrderDetails AS od

 ON od.orderid = o.orderid

GROUP BY YEAR(orderdate);

GO

Opmerking over views:

- Het is een best practice om de naam van het schema te noemen;

- SCHEMABINDING garandeert dat de onderliggende tabellen niet aangepast kunnen worden zonder eerst deze view te verwijderen

- De body van de view is een normaal SELECT statement

-Met expressies kunnen extra kolommen worden toegevoegd aan de view

- Door kolommen niet op te namen in de SELECT worden deze kolommen verborgen voor de eindgebruikers

- Door een alias te gebruiken kunnen kolommen een andere naam krijgen.

- CREATE VIEW moet het eerste statement zijn in een batch

Bij het maken van een view kunnen de volgende opties (gecombineerd) gebruikt worden:

- WITH ENCRYPTION: de tekst van de view wordt gehusseld opgeslagen (niet strong encrypted). Dit maakt het moeilijker voor de gebruikers om de SELECT te lezen

- WITH SCHEMABINDING: zie hierboven voor de uitleg

- WITH VIEW_METADATA: geef de metadata terug van de view i.p.v. de base table

De SELECT en UNION statement in een view

Bij het maken van een view is maar één SELECT statement toegestaan. Dit geldt echter niet als een UNION of UNION ALL wordt gebruikt.

WITH CHECK OPTION

Deze optie zorgt er voor dat enkel rijen bij een update statement op een view enkel die rijen kunnen worden bijgewerkt die voldoen aan de WHERE clause.

View names

De naam van een view moet uniek zijn binnen een schema. Een view kan daarom niet dezelfde naam hebben als een andere view, tabel, SP, function of synonym.

Restrictions on Views

Views hebben de volgende beperkingen:

- In de SELECT statement kan geen ORDER BY worden gebruikt.

- Er kunnen geen parameters aan worden meegegeven

- Een view kan geen tabellen aanmaken. SELECT INTO kan dus niet worden gebruikt

- Een view kan niet gerefereerd worden aan een tijdelijke tabel.

Het resultaat van een view is nooit geordend. ORDER BY kan alleen gebruikt worden als een TOP of OFFSET FETCH wordt gebruikt, maar dan is alsnog de uitkomst van de view willekeurig.

Indexed Views

Bij een view wordt enkel de definitie van de view opgeslagen (het SELECT statement) en niet de data zelf. Het is wel mogelijk om een unique clustered index te plaatsen op een view en de data op te slaan. In dit geval wordt de resultaat van de view opgeslagen op de harde schijf.

Querying from views

Als gegueried wordt van een view dan combineert Sql Server de outer query met de query van de view en verwerkt vervolgens deze gecombineerde query.

Altering and dropping a view

Met het ALTER VIEW statement kan de view worden aangepast.

Met DROP VIEW kan een view worden verwijderd.

Modifying data through a view

Het is mogelijk om update, insert of delete statements te gebruiken op een view, maar er zijn beperkingen:

- DML (Data Manipulation Language) statements (INSERT, UPDATE en DELETE) moeten precies één tabel gebaseerd zijn.

- De kolommen moeten overeenkomen met de namen van de kolommen van de onderliggende tabel

- Een view kolom die is computed van een UNION of UNION ALL kan niet worden bijgewerkt

- Waarden die de uitkomst zijn van grouping (DISTINCT, GROUP BY an HAVING) kunnen niet worden aangepast

- Een view kan niet worden aangepast als een TOP of OFFSET FETCH wordt gebruikt samen met de WITH CHECK OPTION clause.

Partitioned views

Sql Server ondersteund het gebruik van views om grote tabellen te verdelen (partitioning) over één server, één of meerdere tabellen over meerdere databasesen over meerdere servers. Als table partitioning niet mogelijk is, dan kan een view gemaakt worden die een UNION statement toepast over alle tabellen. Dit noemt men een partitioned view. Als de tabellen in één database of in dezelfde instance zitten, dan heet dit een partitioned view of local partitioned view. Als de tabellen verspreid zijn over meerder Sql Server instanties dan wordt dit een distributed partitioned view genoemd.

Views en metadata

Views gedragen zich als tabellen. Om er zeker van te zijn dat een gebruiker in de database de metadata kan zien van een view, geef deze gebruiker dan de VIEW DEFINITION permissie.

Inline functies

Een inline table-valued function kan beschouwd worden als een view die parameters accepteert. Een inline table-valued function wordt net als een view aangeroepen via het SELECT FROM statament

CREATE FUNCTION Sales.fn_OrderTotalsByYear ()

RETURNS TABLE

AS

RETURN

(

 SELECT

   YEAR(O.orderdate) AS orderyear,

   SUM(OD.qty) AS qty

 FROM Sales.Orders AS O

   JOIN Sales.OrderDetails AS OD

   ON OD.orderid = O.orderid

 GROUP BY YEAR(orderdate)

);

GO

Om een inline table-valued function te maken:

- kunnen parameters worden opgegeven, deze zijn optioneel.

- voeg de clause RETURN TABLE toe om kenbaar te maken aan Sql Server dat dit een table-valued functie is

- Geef na het AS block een single RETURN statement op

- Geef een SELECT statement op wat de functie moet gaan retourneren

De body van de functie kan enkel een SELECT statement zijn; het is niet mogelijk om bijv variabelen te declareren. Dit in tegenstelling tot scalar UDFs en multistatement table-valued functies.

Inline function options

Inline functions hebben twee opties, welke ook toepasbaar zijn op views

- WITH ENCRYPTION: Maakt het voor de gebruiker moeilijk om de SELECT tekst van de functie te lezen

- WITH SCHEMABINDING: garandeert dat de onderliggende tabellen niet aangepast kunnen worden zonder eerst deze functie te verwijderen.

Les 2

Synonyms kunnen een abstractielaag bieden voor alle schema-scoped database objecten. Synonyms zijn namen die in de database zijn opgeslagen en kunnen worden gebruikt als vervanging van objectnamen. Deze namen zijn ook database-scoped en van toepassing op een schema.

Creating a synonym

CREATE SYNONYM dbo.Categories FOR Production.Categories;

GO

Van deze synonym kan nu als volgt gebruik gemaakt worden:

SELECT categoryid, categoryname, description

FROM Categories;

De volgende regels zijn van toepassing voor synonyms

- Synonyms slaan geen data op in de database

- Synonyms hanteren dezelfde naamgevingregels als tabellen

- Als er geen schema wordt opgegeven, wordt het standaard schema gebruikt van de ingelogdee gebruiker

- Het object dat na FOR gedefinieerd wordt hoeft niet per se te bestaan in de database. Dit komt vanwege het late-bindings gedrag van synonyms

- Pas als de synonym gebruikt wordt in een query wordt door Sql Server nagegaan of het object bestaat.

Synonymen kunnen toegepast worden op:

- Tables (incl tijdelijke tabellen)

- Views

- User defined functions (UDFs)

- SP's

- CLR assemblies

DDL (Data Definition Language) statements (zoals ALTER) kunnen geen gebruik maken van synonyms, statements zoals SELECT, INSERT, UPDATE, DELETE en EXEC wel.

Als een synonym een andere naam moet krijgen, moet de synonym eerst verwijderd (gedropped) worden en vervolgens weer aangemaakt worden.

DROP SYNONYM dbo.Categories

Abstraction Layer

Synoniemen kunnen refereren naar objecten in andere databases. Dit scheel het typen van een hoop referenties. [linked_server].[database].[schema].[mijnTabel] kan met een synomym verkort worden naar mijnTabel.

Synonyms permissions

Om een synonym te maken is de CREATE SYNONYM permissie nodig, welke erft van de CONSTROL SERVER permissie. Nadat de synoniem gemaakt is kunnen andere gebruikers de rechten EXECUTE of SELECT rechten gegeven worden.

Als in SSMS de synonym wordt opengeklapt ziet de gebruiker geen informatie (zoals bijv. kolommen van een tabel) van het gerefereerde object.