2008.07_SQL Server 2005 2008 _[Aplikacje Biznesowe].pdf

Aplikacje biznesowe

SQL Server 2005/2008

Zalety wykorzystywania widoków w aplikacjach biznesowych

Aplikacje biznesowe to jedne z najbardziej strategicznych aplikacji w

wielu firmach. Efektywne działanie tych aplikacji bardzo często decyduje

o sukcesie firmy. W tym numerze ujawniamy kolejne triki pomagające

tworzyć elastyczne i wydajne aplikacje biznesowe.

Dowiesz się:

• jak efektywnie używać widoków w aplikacjach

biznesowych;

• jak tworzyć widoki zmeterializowane w SQL

Server.

Powinieneś wiedzieć:

• podstawy SQL;

• podstawy tworzenia aplikacji biznesowych;

• podstawową obsługę SQL Server 2005/2008.

ków to wymóg standardu ANSI . Jednak stan-

dard ten nie był przestrzegany w poprzednich

wersjach języka T-SQL . Natomiast w SQL Se-

rver 2005 i SQL Server 2008 stosowanie śred-

nika jest wymagane tylko w niektórych sytu-

acjach np. przed klauzulą WHERE w wyrażeniach

CTE w celu uniknięcia dwuznaczności. W pozo-

stałych przypadkach (tak więc także podczas

tworzenia widoków) stosowanie średnika jest

opcjonalne.

Teraz możemy wykonać polecenie sp_

colums v_pracownicy , żeby sprawdzić z jakich

kolumn składa się stworzona przed chwilą per-

spektywa.

Widoki odgrywają ważną rolę w tworzeniu

rozwiązań biznesowych głównie ze wzglę-

du na mechanizm abstrakcji. Dzieje się tak

przede wszystkim dlatego, że widoku moż-

na użyć do utworzenia mniej lub bardziej

znormalizowanego obrazu danych pocho-

dzącego z różnych tabel i innych perspektyw.

Nie trzeba przy tym zmieniać poziomu nor-

malizacji bazy danych. Widoków można tak-

że użyć do uproszczenia tworzonych rozwią-

zań biznesowych – tzn. perspektyw możemy

Poziom trudności

Po słowach kluczowych DROP VIEW należy po-

dać nazwę widoku, który chcemy usunąć (na-

zwę możemy poprzedzić nazwą schematu).

Czasem istnieje konieczność zmiany per-

spektywy. SQL Server 2005 i SQL Server

2008 udostępniają opcję ALTER VIEW . Tak

więc nie trzeba usuwać za pomocą DROP

VIEW i ponownie tworzyć widoku za pomo-

cą CREATE VIEW – aby zmienić definicję per-

spektywy, wystarczy użyć ALTER VIEW . Nad-

szedł czas, aby stworzyć przykładowy widok

(Listing 1).

Uruchomienie kodu z Listingu 1 na serwe-

rze SQL Server 2000 zakończy się błędem ze

względu na zastosowanie znaku średnika na

końcu instrukcji CREATE VIEW . Aby kod działał

poprawnie na serwerze SQL Server 2000 trzeba

po prostu usunąć średniki. Stosowanie średni-

dużą złożonością. Ponadto, aby nale-

życie spełniać postawioną przed ni-

mi funkcję, muszą być elastyczne, wydajne i za-

pewniać duże bezpieczeństwo przechowywa-

nych danych. Bardzo trudno jest w rozwiązaniu

biznesowym zrealizować wszystkie te aspekty.

Mogą w tym pomóc widoki ( view , perspekty-

wy ). Wielu programistów nie zdaje sobie spra-

wy, jak duże możliwości kryją perspektywy. W

artykule tym postaram się przedstawić zalety

używania widoków w SQL Server 2005/2008

podczas tworzenia rozwiązań biznesowych.

Informacje ogólne

Widok to rodzaj tabeli wirtualnej zdefiniowanej

przez zapytanie SQL. Jednak w przeciwieństwie

do zwykłych tabel, view nie posiada fizycznej re-

prezentacji danych, aż do momentu, gdy użytkow-

nik zdefiniuje dla niego indeks. Tak więc, jeśli wi-

dok nie posiada indeksu, to zawsze wykonując za-

pytanie SQL serwer baz danych korzysta z tabel,

na których dany widok jest oparty. Ogólna skład-

nia tworzenia widoku została umieszczona w ram-

ce (Ramka Składnia polecenia CREATIVE VIEW ).

Usunąć perspektywę możemy za pomocą

polecenia DROP VIEW . Ogólna składnia polece-

nia jest następująca:

Składnia polecenia CREATE VIEW

-- deinicja perspektywy

CREATE VIEW [ nazwa_schematu . ] nazwa_perspektywy [ (kolumna [ ,...n ] ) ]

[ WITH <atrybut_perspektywy> [ ,...n ] ]

AS zapytanie_SELECT

[ WITH CHECK OPTION ] [ ; ]

-- atrybut_perspektywy można zdeioniować jako

<atrybut_perspektywy> ::=

{ [ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

Podczas tworzenia widoku należy umieścić słowa kluczowe CREATE VIEW, następnie podać na-

zwę widoku (nazwa view może być poprzedzona nazwą schematu). Kolejnym opcjonalnym

krokiem jest podanie atrybutów widoku. Następnie należy umieścić słowo kluczowe AS i zdei-

niować zapytanie typu SELECT . Klauzula WITH CHECK OPTION występująca na końcu zapytania

SELECT jest opcjonalna.

DROP VIEW [ nazwa_schematu . ] nazwa_widoku

[ ...,n ] [ ; ]

07/2008

A plikacje biznesowe charakteryzują się

SQL Server 2005/2008

użyć do rozwiązania skomplikowanego pro-

blemu na drodze pojedynczych kroków. Wi-

doków można także używać jako pewnego

rodzaju warstwy zabezpieczeń poprzez przy-

znanie uprawnień do pobierania lub wsta-

wiania danych tylko za pomocą perspektyw.

Kolejną dużą zaletą perspektyw jest wydaj-

ność. Jeśli dla perspektywy zostanie utwo-

rzony sklasterowany indeks, to dane pobiera-

ne przez widok zostaną zapisane fizycznie na

dysku (następuje tzw. materializacja danych

perspektywy, a sam widok nazywa się wtedy

zmaterializowanym). Indeksowanie widoków

zostało opisane w dalszej części artykułu. W

tym momencie najważniejszą informacją jest

to, że bez utworzenia indeksu na perspekty-

wie nie wpływamy ani pozytywnie, ani ne-

gatywnie na wydajność rozwiązania – a two-

rząc indeks zazwyczaj podnosimy wydajność

naszego rozwiązania. Szczególnie ważne jest

podnoszenie wydajności w przypadku aplika-

cji biznesowych.

Podobnie jak w przypadku innych obiek-

tów bazy danych, także tworzenie perspekty-

wy podlega pewnym ograniczeniom:

których jest ona dozwolona – mianowicie,

jeśli w zapytaniu tworzącym widok użyje-

my specyfikatora TOP lub FOR XML , to może-

my również użyć klauzuli ORDER BY . Jeśli w

tabeli znajduje się znana nam liczba wierszy

(np. 2000) i liczba ta nie zmienia się w cza-

sie, to w celu stworzenia posortowanego wi-

doku możemy użyć kodu przedstawionego

na Listingu 3.

Natomiast w przypadku zmieniającej się

liczby wierszy lepiej jest zastosować specyfika-

tor TOP (100) PERCENT , tak jak to zaprezen-

towano na Listingu 4. Specyfikator TOP (100)

PERCENT wyświetla 100% (czyli wszystkie) da-

nych z tabeli.

W przykładzie zamieszczonym na Listingu

4. użyto nawiasów wewnątrz opcji TOP – taka

konstrukcja spowoduje wystąpienie błędu pod-

czas uruchomienia tego kodu na SQL Server

2000 . Tak więc, aby powyższy fragment działał

prawidłowo na SQL Server 2000 należy usunąć

średniki i nawiasy.

Przykłady zaprezentowane do tej pory mają

jedną wadę. Klauzula ORDER BY może (ale nie

musi) zostać zignorowana przez optymaliza-

tor SQL Server 2005 lub SQL Server 2008 . Tak

więc, jeśli istnieje potrzeba zwrócenia kliento-

wi posortowanych danych, to zawsze można

użyć klauzuli ORDER BY w zapytaniu skierowa-

nym do widoku.

SELECT Imie, Nazwisko, Pensja

FROM Pracownicy

ORDER BY p.Pensja;

Jednak, jeśli istnieje konieczność przechowy-

wania danych w widoku i dane te muszą być

posortowane, to możemy wykorzystać jesz-

cze inny trik – mianowicie funkcje partycjo-

nujące. Rozwiązanie zostało zaprezentowane

na Listingu 5.

Posortowanie danych w widoku jest właści-

wie efektem ubocznym zastosowania funk-

cji partycjonującej do stworzenia jednej z ko-

lumn. Funkcja partycjonująca określa po-

rządek wierszy, którym funkcja rankingowa

ROW_NUMBER() przyporządkowuje odpowied-

nie wartości – tak więc sortowanie następuje

w sposób naturalny.

Jednak zaprezentowane na Listingu 5. roz-

wiązanie także ma pewną wadę – kolumna,

wg której dane są posortowane musi występo-

wać wśród kolumn odczytywanych (w sposób

jawny lub też niejawny poprzez gwiazdkę * ) z

widoku – w innym wypadku SQL Server zanie-

cha sortowania.

• wszystkie kolumny pobierane przez zapy-

tanie SELECT muszą mieć swoją nazwę (tak

więc wyrażenie SELECT 12.3 * Pensja /

12 FROM Pracownicy jest w widoku nie-

dozwolone – zamiast niego trzeba zasto-

sować SELECT 12.3 * Pensja / 12 AS

Premia FROM Pracownicy );

• w zapytaniu definiującym widok ograni-

czone jest stosowanie klauzuli ORDER BY

(na temat sortowania danych w widokach

w dalszej części artykułu);

• wszystkie nazwy kolumn w wyrażeniu

SELECT definiującym widok muszą być

unikalne.

Listing 1. Przykładowy widok

USE test; -- użyj bazy test

-- jeśli istnieje perspektywa

IF OBJECT_ID ( 'dbo.VIEW_pracownicy' ) IS NOT NULL

DROP VIEW dbo. VIEW _pracownicy; -- to ją usuwamy

-- tworzenie widoku

CREATE VIEW dbo. VIEW _pracownicy

SELECT

pr.Imie,

pr.Nazwisko,

d.Nazwa AS Dzial

FROM Pracownicy pr, Dzialy d

WHERE

d.Id_dzialu = pr.Id_dzialu;

Sortowanie danych w widokach

W poprzednim punkcie napisałem, że stoso-

wanie klauzuli ORDER BY w widokach jest bar-

dzo ograniczone. Dzieje się tak ze względu na

fakt, iż widoki podobnie jak tabele reprezen-

tują jednostkę logiczną, których wiersze nie

mają żadnej określonej kolejności (inaczej jest

w przypadku kursorów, których rekordy ma-

ją pewną kolejność). Jeśli spróbujesz wykonać

kod z Listingu 2. to SQL Server wygeneruje

błąd następującej treści:

Listing 2. Przykład posortowania danych w widoku, który zakończy się błędem

Msg 1033, Level 15, State 1, Procedure

v_pracownicy, Line 10

The ORDER BY clause is invalid in

views, inline functions, derived tables,

subqueries, and common table expressions,

unless TOP or FOR XML is also specified.

USE test;

ALTER VIEW dbo.VIEW_pracownicy AS

SELECT

Imie,

Nazwisko,

Pensja

FROM Pracownicy

WHERE

ORDER BY Pensja;

Należy zauważyć, że komunikat z Listingu

2 nie zabrania całkowitego stosowania klau-

zuli ORDER BY w perspektywach. Komunikat

informuje nas o istnieniu kilku wyjątków, w

www.sdjournal.org

Aplikacje biznesowe

Listing 3. Sortowanie danych w widoku – użycie specyikatora TOP

USE test;

ALTER VIEW dbo.VIEW_pracownicy AS

SELECT TOP 1000

Imie,

Nazwisko,

Pensja

FROM Pracownicy

ORDER BY Pensja;

Listing 7. Sposób na odświeżenie wszystkich widoków

SELECT N 'EXEC sp_refreshview '

+ QUOTENAME ( Nawa_Perspektywy, '''' ) + ';' AS command

FROM ( SELECT QUOTENAME ( TABLE_SCHEMA )

+ N '.' + QUOTENAME ( TABLE_NAME ) AS Nawa_Perspektywy

FROM INFORMATION_SCHEMA.VIEWS ) AS V

WHERE OBJECTPROPERTY ( OBJECT_ID ( Nawa_Perspektywy ) ,

'IsschemaBound' ) = 0;

Listing 8. Widok z ustawionymi opcjami

Listing 4. Sortowanie danych – użycie specyikatora TOP (100) PERCENT

USE test;

ALTER VIEW dbo.VIEW_pracownicy AS

SELECT TOP ( 100 ) PERCENT

Imie,

Nazwisko,

Pensja

FROM Pracownicy

ORDER BY Pensja;

USE test;

IF OBJECT_ID ( 'dbo.VIEW_pracownicy' ) IS NOT NULL

DROP VIEW dbo.VIEW_pracownicy;

CREATE VIEW dbo.VIEW_pracownicy

WITH SCHEMABINDING, ENCRYPTION

SELECT

Imie,

Nazwisko,

Pensja,

Id_dzialu

FROM dbo.Pracownicy

WHERE Pensja > 3000

WITH CHECK OPTION;

Listing 5. Sortowanie danych w widoku – użycie funkcji partycjonującej

USE test;

ALTER VIEW dbo.VIEW_pracownicy AS

SELECT

Imie,

Nazwisko,

Pensja,

ROW_NUMBER () OVER ( ORDER BY Pensja ) AS Wg_zarobkow

FROM Pracownicy;

Listing 9. Przykład widoku indeksowanego

USE test;

Listing 6. Przykład złej konstrukcji widoków

USE test;

-- jeśli istnieje widok w bazie danych, to usuwamy go

IF OBJECT_ID ( 'dbo.VIEW_Oddzialy' ) IS NOT NULL

DROP VIEW dbo.VIEW_Oddzialy;

-- jeśli istnieje tabela w bazie danych, to usuwamy ją

IF OBJECT_ID ( 'dbo.Oddzialy' ) IS NOT NULL

DROP TABLE dbo.Oddzialy;

-- tworzenie tabeli

CREATE TABLE dbo.Oddzialy

(

ID_odzialu INT PRIMARY KEY IDENTITY ( 1,1 ) NOT NULL ,

Nazwa VARCHAR ( 50 )

) ;

-- wstawianie danych do tabeli

INSERT INTO Oddzialy ( Nazwa ) VALUES ( 'Podlasie' ) ;

INSERT INTO Oddzialy ( Nazwa ) VALUES ( 'Mazowsze' ) ;

INSERT INTO Oddzialy ( Nazwa ) VALUES ( 'Karpaty' ) ;

INSERT INTO Oddzialy ( Nazwa ) VALUES ( 'Pomorze' ) ;

-- tworzenie perspektywy

CREATE VIEW dbo. VIEW_Oddzialy

SELECT * FROM dbo.Oddzialy; GO

-- jeśli istnieje widok w bazie, to jest usuwany

IF OBJECT_ID ( 'dbo.VIEW_pensja_wg_dzialu' ) IS NOT NULL

DROP VIEW dbo.VIEW_pensja_wg_dzialu;

-- tworzenie widoku

CREATE VIEW dbo.VIEW_pensja_wg_dzialu

WITH SCHEMABINDING

SELECT

d.Id_dzialu,

d.Nazwa,

COUNT_BIG ( * ) AS Ilosc_pracownikow,

SUM ( p.Pensja ) AS Suma

FROM dbo.Pracownicy p, dbo.Dzialy d

WHERE

p.Id_dzialu = d.Id_dzialu

GROUP BY d.Id_dzialu, d.Nazwa;

-- tworzenie sklasteryzowanego indeksu

CREATE UNIQUE CLUSTERED INDEX idx_id_dzialu

ON dbo.VIEW_pensja_wg_dzialu ( Id_dzialu ) ;

07/2008

SQL Server 2005/2008

Każde z przedstawionych w tym punkcie

rozwiązań sortowania danych w perspekty-

wie posiada dość poważne wady, należy więc

stosować je w ostateczności, a we wszystkich

innych sytuacjach sortowanie narzucać w ze-

wnętrznym zapytaniu skierowanym do wi-

doku.

widoków. Ręczne uruchomienie procedury

składowanej sp_refreshview dla każdego wi-

doku może być bardzo pracochłonne, a czasem

wręcz niemożliwe. Aby uniknąć tego żmudne-

go procesu można skonstruować zapytanie (Li-

sting 7), które zbuduje odpowiednie dynamicz-

ne polecenia z procedurą sp_refreshview (Ry-

sunek 2).

Oczywiście przed uruchomieniem wy-

generowanych automatycznie poleceń SQL

należy dokładnie przejrzeć, czy wśród nich

nie znajdują się polecenia zbędne (np. wido-

ki, nie stworzone przez nas). Jeśli tak jest, to

należy je usunąć, gdyż mogą zawierać niebez-

pieczny dla naszej bazy danych kod (np. nisz-

czycielski kod o charakterze administracyj-

nym, jeśli widok został stworzony przez ha-

kera).

trolowania funkcjonalności i sposobu zacho-

wań widoku. W SQL Server 2005 i SQL Server

2008 mamy dostępne cztery opcje. Trzy z nich

( ENCRYPTION , SCHEMABINDING i VIEW_METADATA )

umieszczamy przed słowem AS , a jedną ( CHECK

OPTION ) na końcu instrukcji SELECT . Przed

nazwą opcji należy umieścić słowo kluczo-

we WITH . Używanie opcji perspektyw ma du-

że znaczenie podczas tworzenia aplikacji biz-

nesowych.

Opcja ENCRYPTION oprócz widoków może

być również użyta do procedur, funkcji i wy-

zwalaczy. Opcja służy do ukrycia tekstu cia-

ła widoku. Tak więc jej użycie jest prostym

sposobem ukrycia naszej wartości intelektu-

alnej (co w przypadku aplikacji biznesowych

za kilkaset tysięcy złotych ma szczególne zna-

czenie). Niestety istnieją sposoby na odszyfro-

wanie tekstu obiektu stworzonego z użyciem

opcji ENCRYPTION . Niemniej ja preferuję two-

rzenie widoków, procedur i wyzwalaczy w apli-

kacjach biznesowych z tą opcją. Stworzony w

Odświeżanie widoków

Zła konstrukcja widoków bardzo często w

aplikacjach biznesowych powoduje różnego

rodzaju błędy. Co oznacza stwierdzenie zła

konstrukcja widoków ? Najczęściej związane

jest to z użyciem gwiazdki ( * ) wewnątrz zapy-

tania SELECT . Przyjrzyj się fragmentowi kodu

zaprezentowanego na Listingu 6.

Widok został stworzony poprzez użycie

gwiazdki ( * ). Po wykonaniu zapytania SELECT

* FROM dbo.VIEW_Oddzialy otrzymamy wynik

przedstawiony na Rysunku 1.

Jak widać na Rysunku 1 wszystko jest w po-

rządku. Jednak prawidłowość działania in-

strukcji pobierającej dane z widoku jest po-

zorna. Aby to wykazać, wystarczy zmienić

strukturę tabeli poprzez dodanie nowej ko-

lumny.

Opcje widoków

Podczas definiowania lub zmieniania widoku

możemy określić jego opcje. Służą one do kon-

Listing 10. Zastosowanie widoku indeksowanego do zapewnienia unikalności tylko dla znanych

wartości

ALTER TABLE dbo.Oddzialy ADD Kraj VARCHAR(50);

UPDATE dbo.Oddzialy SET Kraj = 'Polska';

– uzupełnienie dodanej kolumny

USE test;

-- usunięcie obiektów z bazy danych, jeśli istnieją

IF OBJECT_ID ( 'dbo.id_view_kolumna1' ) IS NOT NULL

DROP INDEX dbo.id_view_kolumna1; -- najpierw usuwamy indeks

IF OBJECT_ID ( 'dbo.view_perspektywa' ) IS NOT NULL

DROP VIEW dbo.view_perspektywa; -- potem perspektywę

IF OBJECT_ID ( 'dbo.tabela' ) IS NOT NULL

Gdy teraz wykonasz zapytanie SELECT *

FROM dbo.VIEW _ Oddzialy otrzymany wy-

nik będzie taki sam, jak ten przedstawio-

ny na Rysunku 1. Tak więc dodana kolum-

na Kraj nie zostanie uwzględniona w wi-

doku. Dzieje się tak dlatego, że zmiana sche-

matu tabeli nie została uwzględniona w me-

tadanych widoku. Aby odświeżyć metadane

widoku należy uruchomić procedurę skła-

dowaną sp _ refreshview . Składnia polece-

nia w naszym przypadku będzie następują-

ca – EXEC sp _ refreshview ‘dbo.VIEW _

Oddzialy’ .

Tworząc aplikacje biznesowe najczęściej de-

finiujemy wiele widoków. W wielu z nich uży-

wamy (mimo, że to nie jest zalecane) symbolu

* do wybierania danych. W momencie, gdy na-

stępuje konieczność zmiany struktury jednej z

tabel, na której bazują większość widoków, po-

jawia się problem z odświeżeniem wszystkich

DROP TABLE dbo.tabela; -- a na końcu tabelę

-- tworzymy tabelę bez ograniczenia UNIQUE

CREATE TABLE dbo.tabela

(

kolumna1 INT NULL ,

kolumna2 VARCHAR ( 10 ) NOT NULL

) ;

-- tworzymy perspektywę z opcją SCHEMABINDING

CREATE VIEW dbo.view_perspektywa

WITH SCHEMABINDING

SELECT

kolumna1,

kolumna2

FROM dbo.tabela

WHERE

kolumna1 IS NOT NULL ;

-- tworzymy indeks na kolumnie

CREATE UNIQUE CLUSTERED INDEX id_view_kolumna1

ON view_perspektywa ( kolumna1 ) ;

Rysunek 1. Pobranie danych z widoku

www.sdjournal.org

Aplikacje biznesowe

ten sposób kod będzie niewidoczny dla osoby

chcącej podejrzeć nasze rozwiązanie, a nie bę-

dącej specjalistą. Swoją drogą, specjaliści raczej

nie będą potrzebowali zaglądać do naszego ko-

du. Tak więc tworząc widok używajmy opcji

ENCRYPTION w przypadku, gdy inni użytkow-

nicy nie muszą znać szczegółów naszego roz-

wiązania.

Pamiętasz zapewne problem związany z od-

świeżaniem widoków. Problem pojawiał się,

gdy do obiektu bazowego, na którym oparty

był widok (zdefiniowana z użyciem * ) dodane

zostaną inne kolumny. Opcja SCHEMABINDING

rozwiązuje inny problem – modyfikacji lub

usunięcia kolumn z obiektów bazowych. Opcja

ta wiąże schemat widoku lub funkcji składowa-

nej ze wszystkimi obiektami bazowymi użyty-

mi do ich zdefiniowania. Dzięki temu usunię-

cie lub modyfikacja obiektów bazowych będzie

niemożliwa. Opcja SCHEMABINDING ma bardzo

duże znaczenie podczas tworzenia rozwiązań

biznesowych, które zawierają setki widoków i

tabel. Jeśli widoki nie zostaną stworzone z tą

opcją, to po usunięciu jednej z kolumn w ta-

beli bazowej (kolumna ta może wydawać się

nam zbędna) mogą przestać działać ważne al-

gorytmy. Myślę, że nikomu nie trzeba tłuma-

czyć, iż znalezienie przyczyny błędów w bazie,

która zawiera setki obiektów nie jest łatwe. Wy-

korzystując opcję SCHEMABINDING musimy rów-

nież używać tzw. dwuczłonowych nazw tabel

( dbo.test , a nie test ) oraz nie możemy sto-

sować znaku gwiazdki ( * ) na liście instrukcji

SELECT – zamiast tego musimy jawnie wymie-

nić wszystkie kolumny.

CHECK OPTION to jedyna opcja, którą

umieszcza się na końcu instrukcji SELECT de-

finiującej widok. Opcja CHECK OPTION unie-

możliwia wykonanie instrukcji INSERT i

UPDATE , które są w konflikcie z filtrem zapy-

tania SELECT definiującego widok. Opcja ta

odgrywa duże znaczenie w aplikacjach biz-

nesowych. Tworząc widoki w aplikacjach biz-

nesowych proponuję wykorzystywać tę opcję

– zapewnia ona poprawną logikę biznesową

i może być mechanizmem zabezpieczającym

przed dodaniem nieodpowiednich danych

do tabel bazowych. Przykład widoku z opcja-

mi został zaprezentowany na Listingu 8.

Widoki indeksowane

Widoki nie mają swojej fizycznej reprezen-

tacji – zamiast tego zawierają tylko infor-

macje w metadanych wskazujące, z których

obiektów bazy danych pobierać dane. Dopie-

ro po utworzeniu unikalnego sklasteryzowa-

nego indeksu następuje fizyczna materializa-

cja danych. Po utworzeniu takiego indeksu

SQL Server 2005 i SQL Server 2008 sam bę-

dzie synchronizował dane przechowywane w

widoku z danymi tabel bazowych. Użytkow-

nik nie ma możliwości wykonania takiej syn-

chronizacji na żądanie, następuje ona auto-

matycznie.

Widoki indeksowane odgrywają bardzo du-

żą rolę w procesie optymalizacji rozwiązań

biznesowych. Mogą przyczynić się do znacz-

nej poprawy wydajności pobieranych danych,

w znaczący sposób ograniczać ilość operacji I/

O wymaganych do pobrania danych lub skró-

cić czas wykonywania kosztownych kalkulacji.

Duży wzrost wydajności można osiągnąć w

zapytaniach agregujących dane (czyli takich,

które bardzo często wykonywane są w aplika-

cjach biznesowych). Jednak należy pamiętać,

że modyfikacja lub dodawanie danych w ta-

belach bazowych, na których opierają się per-

spektywy indeksowane wymaga również aktu-

alizacji indeksowanego widoku, a więc nastę-

puje spadek wydajności operacji modyfikują-

cych dane.

Tworząc widoki indeksowane należy pamię-

tać o kilku ograniczeniach:

BIG(* ). Funkcja COUNT _ BIG(*) ma iden-

tyczne działanie jak funkcja COUNT(*) z

tym, że zwracany wynik jest typu BIGINT .

Informacja o ilości elementów jest po-

trzebna do śledzenia listy wierszy zagrego-

wanych w poszczególnych grupach i oczy-

wiście jest wykorzystywana do obliczania

innych agregacji.

Stwórzmy widok indeksowany (Listing 9). Za-

daniem widoku będzie przechowywanie da-

nych na temat pracowników pracujących w

poszczególnych działach oraz sumie ich za-

robków (kosztach ponoszonych przez firmę z

tytułu wypłacania wynagrodzenia w podziale

na poszczególne działy).

Jak zapewne zauważyłeś, widok z Listingu

9 został utworzony z opcją SCHEMABINDING , a

więc wymagane było posługiwanie się dwuczę-

ściowymi nazwami tabel ( dbo.Pracownicy ).

Istnieje jeszcze jedna przyczyna stosowa-

nia widoków indeksowanych w aplikacjach

biznesowych. Otóż istniejące w języku T-

SQL ograniczenie UNIQUE traktuje dwie war-

tości NULL jak wartości równe (co jest nieco

dziwne).

Tak więc jeśli utworzymy ograniczenie

UNIQUE dla kolumny dopuszczających warto-

ści NULL , to w kolumnie tej będzie mogła ist-

nieć tylko jedna wartość NULL . W aplikacjach

biznesowych istnieje czasem wymóg stoso-

wania unikalności tylko dla znanych warto-

ści (różnych od NULL ). W takich sytuacjach

należy stworzyć tabelę z kolumnami bez

ograniczenia UNIQUE , zbudować odpowied-

nią perspektywę z opcją SCHEMABINDING oraz

stworzyć indeks na kolumnie, do której chce-

my wstawiać unikalne wartości lub wartości

NULL (Listing 10).

• pierwszy tworzony indeks musi być uni-

kalny ( UNIQUE ) i sklasteryzowany ( CLU-

STERED ), kolejne indeksy tworzone na

perspektywie nie muszą być sklasteryzo-

wane;

• widok musi zostać utworzony z opcją

SCHEMABINDING , z tego też względu w na-

zwach obiektów należy stosować dwuczę-

ściową konwencję nazewniczą, a na liście

instrukcji SELECT muszą być wymienione

nazwy wszystkich wymaganych kolumn

(nie można stosować * );

• jeśli zapytanie perspektywy dokonu-

je agregacji danych, to lista po instrukcji

SELECT musi zawierać funkcję COUNT _

Podsumowanie

Widoki to jedne z najważniejszych obiek-

tów w aplikacjach biznesowych. W SQL Se-

rver 2005 i SQL Server 2008 dzięki wido-

kom można uzyskać dużą wydajność apli-

kacji, rozwiązać lub uprościć skomplikowa-

ne zadanie biznesowe, a także zapewnić du-

ży poziom bezpieczeństwa tworzonego roz-

wiązania.

Rysunek 2. Wynik zapytania budującego dynamiczne polecenie odświeżające widoki

ARTUR MOŚCICKI

Artur Mościcki jest z wykształcenia informatykiem.

Obecnie pracuje jako programista baz danych i

hurtowni danych. Ma również doświadczenie w

tworzeniu aplikacji BI dla dużych i średnich irm.

Jest współautorem książek: Oracle 10g i Delphi.

Programowanie baz danych oraz Photoshop. Plu-

giny i efekty specjalne. Oprócz hurtowni i baz da-

nych, jego drugą informatyczną pasją jest fotogra-

ia cyfrowa i obróbka zdjęć za pomocą Adobe Pho-

toshop. W wolnych chwilach kibicuje piłkarskiej re-

prezentacji Argentyny.

Kontakt z autorem: arturmoscicki@op.pl

07/2008

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: