Używając widoków i funkcji można przedstawić użytkownikowi zawartość jednej lub więcej tabeli bazy danych ujmując skomplikowaną logikę jak złączenia i filtracja. W SQL Serwerze można używać widoków do przechowywania i ponownego użycia zapytań. Widoki używa się bardzo podobnie do tabel. Można pobierać z nich dane, filtrować wynik, łączyć z innymi obiektami oraz nawet wstawiać, aktualizować i usuwać dane w widokach choć z ograniczeniami. Każdy widok jest zdefiniowany jako zapytanie SELECT, które może odnosić się do różnych tabel i widoków bazy danych.

Widoki

Aby utworzyć widok trzeba go nazwać i zdefiniować zapytanie SELECT. Przykładowo stwórzmy widok Sales.OrderTotalsByYear:

USE TSQL2012;
GO
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

Odczytywanie danych z widoku odbywa się tak samo jak z tabeli np:

SELECT orderyear, qty
FROM Sales.OrderTotalsByYear;

Kilka ważnych informacji o widokach:

  • Tak samo jak dla innych deklaracji CREATE jak CREATE TABLE, dla widoków można określić schemat bazy danych. Najlepszą praktyką jest odnoszenie się do obiektów takich jak widok stosując dwuczłonową nazwę zawierającą schemat bazy danych. 
  • Opcja SCHEMABINDING przy tworzeniu widoku gwarantuje, że użyte w widoku tabele nie mogą być zmienione bez usunięcia tego widoku.
  • Ciałem widoku jest deklaracja SELECT dla której stosowane są wszystkie zasady zapytania SELECT.
  • Widok może służyć jako ograniczony zbiór widocznych kolumn dla użytkownika poprzez wyszczególnienie ich w zapytaniu SELECT.
  • Można używać aliasów w widokach do zmiany nazw wyświetlanych kolumn.

Składnia tworzenia widoku

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

Kolejne kroki tworzenia widoku:

  • CREATE VIEW musi być pierwszą klauzulą w zapytaniu. 
  • Widoki nazywa się tak jak tabele i inne obiekty w bazie danych.
  • Można określić zestaw kolumn wyjściowych  w nawiasie zaraz po nazwie widoku.
CREATE VIEW Sales.OrderTotalsByYear(orderyear, qty)
WITH SCHEMABINDING
AS
SELECT
YEAR(O.orderdate),
SUM(OD.qty)
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
GO

Opcje widoków:

  • WITH ENCRYPTION - powoduje, że tekst widoku przechowywany w bazie danych jest zaszyfrowany.
  • WITH SCHEMABINDING - chroni widok przed zmianami w strukturze tabel których używa.
  • WITH VIEW_METADATA - gdy jest użyty, zwraca metadane widoku zamiast tabeli bazowej.

SELECT i UNION w widokach

W składni tworzenia widoku jest tylko jedna deklaracja zapytania SELECT. Jest tak ponieważ widok zwraca tylko jeden zbiór danych. Istnieje możliwość użycia kombinacji zapytań SELECT z użyciem operatorów UNION i UNION ALL. Są to tak zwane widoki partycjonowane.

WITH CHECK OPTION

Można zadeklarować tę opcję na końcu deklaracji widoku. Jest to bardzo ważna opcja. Stosowane tylko w modyfikowalnych widokach, powoduje sprawdzanie czy instrukcje UPDATE lub INSERT nie wstawiają do zasadniczej tabeli wierszy, które powinny zostać włączone do widoku.

Nazwy widoków

Każdy widok jest obiektem bazy danych i jego nazwa jest przechowywana w bazie danych. Każda nazw widoku wraz ze schematem musi stanowić unikalną parę dla bazy danych. Nazwa widoku musi być identyfikatorem T-SQL.

Ograniczenia widoków

Widoki mają wiele ograniczeń i restrykcji:

  • Nie można dać ORDER BY do zapytania SELECT w widoku. 
  • Nie można używać parametrów w widokach.
  • Widok nie może tworzyć tabeli (trwałej ani tymczasowej).
  • Widok może odwoływać się tylko do trwałych tabel.

Widoki indeksowane

Normalnie widok jest tylko definicją deklaracji zapytania SELECT. Innymi słowami tylko definicja zapytania SELECT jest przechowywana w bazie danych lecz widok nie zawiera żadnych danych. Istnieje możliwość tworzenia unikalnego klajstrowanego indeksu na widokach i materializacja danych. Aktualny wynik  takiego widoku jest przechowywany na dysku w strukturze indeksu klajstrowanego. Widok indeksowany musi spełniać wiele założeń i restrykcji.

Użycie widoków w zapytaniach

Kiedy używamy normalnych nieindeksowanych widoków to SQL Serwer Optymizer łączy kwerendę osadzoną w widoku z resztą tabel w zapytaniu. Gdy przeglądamy plan zapytania to widać, że zamiast widoku ujęte są tabele używane przez widok. Widok nie jest obiektem w planie zapytania.

Zmiana widoków

Definicję widoku można zmieniać za pomocą komendy ALTER VIEW.  Przykładowo można zmienić widok Sales.OrderTotalsByYear tak aby zawierał nową kolumnę dla regionu wysyłki zamówienia:

ALTER VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS
SELECT
O.shipregion,
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), O.shipregion;
GO

Usuwanie widoków

Widoki usuwa się tak jak tabele za pomocą komendy DROP VIEW np:

IF OBJECT_ID('Sales.OrderTotalsByYear', 'V') IS NOT NULL
DROP VIEW Sales.OrderTotalsByYear;

Parametr 'V' w funkcji OBJECT_ID() wskazuje, że wyszukujemy identyfikator widoku w aktualnej bazie danych.

Modyfikacja danych w widokach

Widoki pozwalają na wstawianie, aktualizację i usuwanie danych do tabel z których korzystają. Istnieją jednak pewne restrykcje:

  • Wyrażenie DML (INSERT, UPDATE, DELETE), musi odnosić się dokładnie do jednej tabeli niezależnie ile tabel jest używanych w widoku.
  • Kolumny widoku muszą odnosić się bezpośrednio do kolumn tabeli, nie być wyrażeniami lub funkcjami na wartościach tych kolumn np nie można modyfikować kolumn które są wynikiem funkcji agregującej jak SUM().
  • Nie można modyfikować widoków łączących zbiory wynikowe za pomocą UNION/UNION ALL, CROSS JOIN, EXCEPT lub INTERSECT.
  • Nie można modyfikować widoków których zbiór wynikowy pochodzi z zapytania grupującego używającego DISTINCT, GROUP BY lub HAVING.
  • Nie można modyfikować widoku używającego operatora TOP lub OFFSETFETCH wraz z klauzulą WITH CHECK OPTION.

Jeżeli chcesz zaktualizować widok który nie spełnia tych warunków to możesz stworzyć wyzwalacz INSTEAD OF TRIGGER który będzie aktualizował tabele.

Widoki partycjonowane

SQL Serwer pozwala na użycie widoków do partycjonowania dużych tabeli na jednym serwerze dla jednej lub wielu tabel dla wielu baz danych i wielu serwerów. Jeżeli nie możesz użyć partycjonowania na tabelach to możesz manualnie podzielić tabelę i użyć operatora UNION na tych tabelach w widoku. Rezultat takiej operacji zwany jest widokiem partycjonowanym. Aby SQL Serwer Query Optymizer skorzystał z tego podziału taki widok musi spełniać specjalne wymogi. Więcej informacji na ten temat znaleźć można na stronie: http://msdn.microsoft.com/en-us/library/ms190019.aspx.

Widoki i metadane

Aby upewnić się, że użytkownik bazy danych może zobaczyć metadane widoków, dodaj mu uprawnienia VIEW DEFINITION. Przegląd metadanych widoków w T-SQL możliwy jest poprzez odpytanie widoku systemowego sys.views:

USE TSQL2012;
GO
SELECT name, object_id, principal_id, schema_id, type
FROM sys.views;

Można również odpytać widok INFORMATION_SCHEMA.TABLES lecz jest to bardziej złożone:

SELECT SCHEMA_NAME, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW';

Funkcje inline

W T-SQL jedynym sposobem na filtrowanie widoków to dodanie wyrażenia w klauzuli WHERE gdy pobieramy dane z widoku za pomocą instrukcji SELECT. Nie ma możliwości do użycia parametrów w widokach. Można jednak użyć funkcji tabelarycznej inline która zachowuje się jak sparametryzowany widok. Funkcja tabelaryczna inline zwraca zbiór wierszy bazujący na zapytaniu SELECT zawartym w ciele funkcji. W efekcie funkcję taką używa się w zapytaniach tak samo jak tabelę czy widok używając konstrukcji SELECT FROM. Przykładowo można stworzyć funkcję  Sales.rderTotalsByYear bez parametrów wejściowych która działa tak jak widok:

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

Aby stworzyć funkcję tablicową trzeba:

  • Wyznaczyć parametry. Parametry są opcjonalne ale nawias w którym są umieszczane nie.
  • Dodać klauzulę RETURN TABLE aby SQL Serwer wiedział, że deklarujemy funkcję tablicową.
  • Po bloku AS wprowadzić pojedynczą konstrukcję RETURN.
  • Zdefiniować zapytanie SELECT które będzie definiowało zwracany zbiór.

W funkcji tablicowej inline ciałem może być tylko zapytanie SELECT. Nie można deklarować zmiennych i używać innych komend T-SQL. Rozpatrzmy sytuację gdybyśmy chcieli pobrać tylko sumę zamówień za rok 2007 to używając widoku musielibyśmy użyć zapytania:

DECLARE @orderyear int = 2007;
SELECT orderyear, qty
FROM Sales.OrderTotalsByYear
WHERE orderyear = @orderyear;

Za pomocą funkcji tablicowej inline zamiast deklarowania zmiennej @orderyear możemy zdefiniować parametr @orderyear:

USE TSQL2012;
GO
IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL
DROP FUNCTION Sales.fn_OrderTotalsByYear;
GO
CREATE FUNCTION Sales.fn_OrderTotalsByYear (@orderyear int)
RETURNS TABLE
AS
RETURN
(
SELECT orderyear, qty FROM Sales.OrderTotalsByYear
WHERE orderyear = @orderyear
);
GO

Pobranie wyniku z wykorzystaniem funkcji wygląda tak:

SELECT orderyear, qty FROM Sales.fn_OrderTotalsByYear(2007);

Opcje funkcji inline

Funkcje inline mają dwie dodatkowe opcje takie jak w widokach:

  • WITH ENCRYPTION.
  • WITH SCHEMABINDING.

Ćwiczenia

I. Budowa widoków dla raportów

W tym ćwiczeniu trzeba zaprojektować interfejs do raportowania sprzedaży w bazie TSQL2012. Aplikacja potrzebuje widoków pokazujących ilość sprzedaży, jej łączną wartość dla lat, klientów i dostawców.

  1. Zacznijmy od widoku Sales.OrderTotalsByYear który był używany w tej lekcji. Napisz zapytanie SELECT dla tego widoku (bez definicji widoku). 
    USE TSQL2012;
GO
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);
  2. Dodaj kolumnę do zapytania która wylicza kwotę sprzedaży. 
    SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
  3. Dodaj kolumny identyfikatorów klienta i dostawcy. 
    SELECT
O.custid,
O.shipperid,
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(O.orderdate), O.custid, O.shipperid;
  4. Połącz widok z Salec.Customers aby dodać nazwy klienta i dostawcy. 
    SELECT
C.companyname AS customercompany,
S.companyname AS shippercompany,
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
JOIN Sales.Customers AS C
ON O.custid = C.custid
JOIN Sales.Shippers AS S
ON O.shipperid = S.shipperid
GROUP BY YEAR(O.orderdate), C.companyname, S.companyname;
  5. Zadeklaruj widok z powyższego zapytania. 
    IF OBJECT_ID (N'Sales.OrderTotalsByYearCustShip', N'V') IS NOT NULL
DROP VIEW Sales.OrderTotalsByYearCustShip;
GO
CREATE VIEW Sales.OrderTotalsByYearCustShip
WITH SCHEMABINDING
AS
SELECT
C.companyname AS customercompany,
S.companyname AS shippercompany,
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
JOIN Sales.Customers AS C
ON O.custid = C.custid
JOIN Sales.Shippers AS S
ON O.shipperid = S.shipperid
GROUP BY YEAR(O.orderdate), C.companyname, S.companyname;
GO
  6. Sprawdź działanie widoku. 
    SELECT customercompany, shippercompany, orderyear, qty, val
FROM Sales.OrderTotalsByYearCustShip
ORDER BY customercompany, shippercompany, orderyear;

II. Zamiana widoku na funkcję inline

  1. Zamień widok na funkcję która filtruje wynik po wartości najmniejszej i największej ilości. Dodaj dwa parametry liczbowe @highqty i @lowqty. 
    IF OBJECT_ID (N'Sales.fn_OrderTotalsByYearCustShip', N'IF') IS NOT NULL
DROP FUNCTION Sales.fn_OrderTotalsByYearCustShip;
GO
CREATE FUNCTION Sales.fn_OrderTotalsByYearCustShip (@lowqty int, @highqty int)
RETURNS TABLE
AS
RETURN
(
SELECT
C.companyname AS customercompany,
S.companyname AS shippercompany,
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty,
CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount))
AS NUMERIC(12, 2)) AS val
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
JOIN Sales.Customers AS C
ON O.custid = C.custid
JOIN Sales.Shippers AS S
ON O.shipperid = S.shipperid
GROUP BY YEAR(O.orderdate), C.companyname, S.companyname
HAVING SUM(OD.qty) >= @lowqty AND SUM(OD.qty) < = @highqty
);
GO
  2. Przetestuj funkcję. 
    SELECT customercompany, shippercompany, orderyear, qty, val
FROM Sales.fn_OrderTotalsByYearCustShip (100, 200)
ORDER BY customercompany, shippercompany, orderyear;

Podsumowanie

  1. Widoki są deklaracjami T-SQL które mogą być traktowane jak tabele.
  2. Normalnie w widoku istnieje tylko jedno zapytanie SELECT. Można jednak stosować poprzez kombinację zbiorów wynikowych z użyciem UNION i UNION ALL.
  3. Widoki mogą odwoływać się do wielu tabel i upraszczać złączenia tabel użytkownikom.
  4. Domyślnie widoki nie zawierają żadnych danych. Mogą je zawierać tylko jeżeli zostanie na nich utworzony indeks klajstrowany.
  5. Gdy używamy widoku to optymalizator nie traktuje go jako obiektu tylko używa jego zapytania SELECT do kombinacji z resztą zapytania.
  6. Można modyfikować dane w widokach ale tylko z jednej tabeli jednocześnie i tylko kolumny o określonym typie.
  7. Można dodać opcję WITH CHECK OPTION do widoku, tak aby zapobiec aktualizacji widoku na wierszach które nie będą dalej spełniały warunku WHERE zapytania zawartego w widoku.
  8. Widoki mogą odnośić się do tabel i widoków w innej bazie danych i w innych powiązanych serwerach.
  9. Funkcje inline mogą być używane aby zasymulować widoki sparametryzowane.