SQL Server 2005/2008
Pełna wersje artykułu z listingami można pobrać tutaj
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.
Aplikacje biznesowe charakteryzują się dużą złożonością. Ponadto, aby należycie spełniać postawioną przed nimi funkcję, muszą być elastyczne, wydajne i zapewniać duże bezpieczeństwo przechowywanych danych. Bardzo trudno jest w rozwiązaniu biznesowym zrealizować wszystkie te aspekty. Mogą w tym pomóc widoki (view, perspektywy). Wielu programistów nie zdaje sobie sprawy, 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 reprezentacji danych, aż do momentu, gdy użytkownik zdefiniuje dla niego indeks. Tak więc, jeśli widok nie posiada indeksu, to zawsze wykonując zapytanie SQL serwer baz danych korzysta z tabel, na których dany widok jest oparty. Ogólna składnia tworzenia widoku została umieszczona w ramce (Ramka Składnia polecenia CREATIVE VIEW).
Usunąć perspektywę możemy za pomocą polecenia DROP VIEW. Ogólna składnia polecenia jest następująca:
DROP VIEW [ nazwa_schematu . ] nazwa_widoku [ ...,n ] [ ; ] Po słowach kluczowych DROP VIEW należy podać nazwę widoku, który chcemy usunąć (nazwę możemy poprzedzić nazwą schematu).
Czasem istnieje konieczność zmiany perspektywy. 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 pomocą CREATE VIEW – aby zmienić definicję perspektywy, wystarczy użyć ALTER VIEW. Nadszedł czas, aby stworzyć przykładowy widok (Listing 1).
Uruchomienie kodu z Listingu 1 na serwerze 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 średników to wymóg standardu ANSI. Jednak standard ten nie był przestrzegany w poprzednich wersjach języka TSQL. Natomiast w SQL Server 2005 i SQL Server 2008 stosowanie średnika jest wymagane tylko w niektórych sytuacjach np. przed klauzulą WHERE w wyrażeniach CTE w celu uniknięcia dwuznaczności. W pozostał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ą perspektywa.
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 pochodzącego z różnych tabel i innych perspektyw. Nie trzeba przy tym zmieniać poziomu normalizacji bazy danych. Widoków można także użyć do uproszczenia tworzonych rozwiązań biznesowych – tzn. perspektyw możemy użyć do rozwiązania skomplikowanego problemu na drodze pojedynczych kroków. Widoków można także używać jako pewnego rodzaju warstwy zabezpieczeń poprzez przyznanie uprawnień do pobierania lub wstawiania danych tylko za pomocą perspektyw.
Kolejną dużą zaletą perspektyw jest wydajność. Jeśli dla perspektywy zostanie utworzony sklasterowany indeks, to dane pobierane 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 perspektywie nie wpływamy ani pozytywnie, ani negatywnie na wydajność rozwiązania – a tworząc indeks zazwyczaj podnosimy wydajność naszego rozwiązania. Szczególnie ważne jest podnoszenie wydajności w przypadku aplikacji biznesowych.
Podobnie jak w przypadku innych obiektów bazy danych, także tworzenie perspektywy podlega pewnym ograniczeniom:
• wszystkie kolumny pobierane przez zapytanie SELECT muszą mieć swoją nazwę (tak więc wyrażenie SELECT 12.3 * Pensja / 12 FROM Pracownicy jest w widoku niedozwolone – zamiast niego trzeba zastosować SELECT 12.3 * Pensja / 12 AS Premia FROM Pracownicy);
• w zapytaniu definiującym widok ograniczone 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.
Sortowanie danych w widokach
W poprzednim punkcie napisałem, że stosowanie klauzuli ORDER BY w widokach jest bardzo ograniczone. Dzieje się tak ze względu na fakt, iż widoki podobnie jak tabele reprezentują jednostkę logiczną, których wiersze nie mają żadnej określonej kolejności (inaczej jest w przypadku kursorów, których rekordy mają pewną kolejność). Jeśli spróbujesz wykonać kod z Listingu 2. to SQL Server wygeneruje błąd następującej treści:
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.
Należy zauważyć, że komunikat z Listingu 2 nie zabrania całkowitego stosowania klauzuli ORDER BY w perspektywach. Komunikat informuje nas o istnieniu kilku wyjątków, w których jest ona dozwolona – mianowicie, jeśli w zapytaniu tworzącym widok użyjemy specyfikatora TOP lub FOR XML, to możemy 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 czasie, to w celu stworzenia posortowanego widoku możemy użyć kodu przedstawionego na Listingu 3.
Natomiast w przypadku zmieniającej się liczby wierszy lepiej jest zastosować specyfikator TOP (100) PERCENT, tak jak to zaprezentowano na Listingu 4. Specyfikator TOP (100) PERCENT wyświetla 100% (czyli wszystkie) danych z tabeli.
W przykładzie zamieszczonym na Listingu 4. użyto nawiasów wewnątrz opcji TOP – taka konstrukcja spowoduje wystąpienie błędu podczas 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 optymalizator SQL Server 2005 lub SQL Server 2008. Tak więc, jeśli istnieje potrzeba zwrócenia klientowi posortowanych danych, to zawsze można użyć klauzuli ORDER BY w zapytaniu skierowanym do widoku.
SELECT Imie, Nazwisko, Pensja
FROM Pracownicy
ORDER BY p.Pensja;
Jednak, jeśli istnieje konieczność przechowywania danych w widoku i dane te muszą być posortowane, to możemy wykorzystać jeszcze inny trik – mianowicie funkcje partycjonujące. Rozwiązanie zostało zaprezentowane na Listingu 5.
Posortowanie danych w widoku jest właściwie efektem ubocznym zastosowania funkcji partycjonującej do stworzenia jednej z kolumn. Funkcja partycjonująca określa porządek wierszy, którym funkcja rankingowa ROW_NUMBER() przyporządkowuje odpowiednie wartości – tak więc sortowanie następuje w sposób naturalny.
Jednak zaprezentowane na Listingu 5. rozwiązanie także ma pewną wadę – kolumna, wg której dane są posortowane musi występować wśród kolumn odczytywanych (w sposób jawny lub też niejawny poprzez gwiazdkę *) z widoku – w innym wypadku SQL Server zaniecha sortowania.
Każde z przedstawionych w tym punkcie rozwiązań sortowania danych w perspektywie posiada dość poważne wady, należy więc stosować je w ostateczności, a we wszystkich innych sytuacjach sortowanie narzucać w zewnętrznym zapytaniu skierowanym do widoku.
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 zapytania 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.
Rysunek 1. Pobranie danych z widoku
Jak widać na Rysunku 1 wszystko jest w porządku. Jednak prawidłowość działania instrukcji pobierającej dane z widoku jest pozorna. Aby to wykazać, wystarczy zmienić strukturę tabeli poprzez dodanie nowej kolumny.
ALTER TABLE dbo.Oddzialy ADD Kraj VARCHAR(50);
GO
UPDATE dbo.Oddzialy SET Kraj = 'Polska'; – uzupełnienie dodanej kolumny
GO
Gdy teraz wykonasz zapytanie SELECT * FROM dbo.VIEW_Oddzialy otrzymany wynik będzie taki sam, jak ten przedstawiony na Rysunku 1. Tak więc dodana kolumna Kraj nie zostanie uwzględniona w widoku. Dzieje się tak dlatego, że zmiana schematu tabeli nie została uwzględniona w metadanych widoku. Aby odświeżyć metadane widoku należy uruchomić procedurę składowaną sp_refreshview. Składnia polecenia w naszym przypadku będzie następująca – EXEC sp_refreshview ‘dbo.VIEW_Oddzialy’.
Tworząc aplikacje biznesowe najczęściej definiujemy wiele widoków. W wielu z nich używamy (mimo, że to nie jest zalecane) symbolu * do wybierania danych. W momencie, gdy następuje konieczność zmiany struktury jednej z tabel, na której bazują większość widoków, pojawia się problem z odświeżeniem wszystkich widoków. Ręczne uruchomienie procedury składowanej sp_refreshview dla każdego widoku może być bardzo pracochłonne, a czasem wręcz niemożliwe. Aby uniknąć tego żmudnego procesu można skonstruować zapytanie (Listing 7), które zbuduje odpowiednie dynamiczne polecenia z procedurą sp_refreshview (Rysunek 2).
Oczywiście przed uruchomieniem wygenerowanych automatycznie poleceń SQL należy dokładnie przejrzeć, czy wśród nich nie znajdują się polecenia zbędne (np. widoki, nie stworzone przez nas). Jeśli tak jest, to należy je usunąć, gdyż mogą zawierać niebezpieczny dla naszej bazy danych kod (np. niszczycielski kod o charakterze administracyjnym, jeśli widok został stworzony przez hakera).
Opcje widoków
Podczas definiowania lub zmieniania widoku możemy określić jego opcje. Służą one do kontrolowania funkcjonalności i sposobu zachowań 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 kluczowe WITH. Używanie opcji perspektyw ma duże znaczenie podczas tworzenia aplikacji biznesowych.
Opcja ENCRYPTION oprócz widoków może być również użyta do procedur, funkcji i wyzwalaczy. Opcja służy do ukrycia tekstu ciała widoku. Tak więc jej użycie jest prostym sposobem ukrycia naszej wartości intelektualnej (co w przypadku aplikacji biznesowych za kilkaset tysięcy złotych ma szczególne znaczenie). Niestety istnieją sposoby na odszyfrowanie tekstu obiektu stworzonego z użyciem opcji ENCRYPTION. Niemniej ja preferuję tworzenie widoków, procedur i wyzwalaczy w aplikacjach biznesowych z tą opcją. Stworzony w 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 kodu. Tak więc tworząc widok używajmy opcji ENCRYPTION w przypadku, gdy inni użytkownicy nie muszą znać szczegółów naszego rozwią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ładowanej ze wszystkimi obiektami bazowymi użytymi 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 tabeli bazowej (kolumna ta może wydawać się nam zbędna) mogą przestać działać ważne algorytmy. Myślę, że nikomu nie trzeba tłumaczyć, iż znalezienie przyczyny błędów w bazie, która zawiera setki obiektów nie jest łatwe. Wykorzystując opcję SCHEMABINDING musimy również używać tzw. dwuczłonowych nazw tabel (dbo.test, a nie test) oraz nie możemy stosować znaku gwiazdki (*) na liście instrukcji SELECT – zamiast tego musimy jawnie wymienić wszystkie kolumny.
CHECK OPTION to jedyna opcja, którą umieszcza się na końcu instrukcji SELECT definiującej widok. Opcja CHECK OPTION uniemożliwia wykonanie instrukcji INSERT i UPDATE, które są w konflikcie z filtrem zapytania SELECT definiującego widok. Opcja ta odgrywa duże znaczenie w aplikacjach biznesowych. Tworząc widoki w aplikacjach biznesowych 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 opcjami został zaprezentowany na Listingu 8.
Widoki indeksowane
Widoki nie mają swojej fizycznej reprezentacji – zamiast tego zawierają tylko informacje w metadanych wskazujące, z których obiektów bazy danych pobierać dane. Dopiero po utworzeniu unikalnego sklasteryzowanego indeksu następuje fizyczna materializacja 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żytkownik nie ma możliwości wykonania takiej synchronizacji na żądanie, następuje ona automatycznie.
Rysunek 2. Wynik zapytania budującego dynamiczne polecenie odświeżające widoki
Widoki indeksowane odgrywają bardzo dużą rolę w procesie optymalizacji rozwiązań biznesowych. Mogą przyczynić się do znacznej 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 aplikacjach biznesowych). Jednak należy pamiętać, że modyfikacja lub dodawanie danych w tabelach bazowych, na których opierają się perspektywy indeksowane wymaga również aktualizacji 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:
• pierwszy tworzony indeks musi być unikalny (UNIQUE) i sklasteryzowany (CLUSTERED), kolejne indeksy tworzone na perspektywie nie muszą być sklasteryzowane;
• widok musi zostać utworzony z opcją SCHEMABINDING, z tego też względu w nazwach 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 dokonuje agregacji danych, to lista po instrukcji SELECT musi zawierać funkcję COUNT_BIG(*). Funkcja COUNT_BIG(*) ma identyczne działanie jak funkcja COUNT(*) z tym, że zwracany wynik jest typu BIGINT. Informacja o ilości elementów jest potrzebna do śledzenia listy wierszy zagregowanych w poszczególnych grupach i oczywiście jest wykorzystywana do obliczania innych agregacji.
Stwórzmy widok indeksowany (Listing 9). Zadaniem widoku będzie przechowywanie danych na temat pracowników pracujących w poszczególnych działach oraz sumie ich zarobkó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 stosowania widoków indeksowanych w aplikacjach biznesowych. Otóż istniejące w języku TSQL ograniczenie UNIQUE traktuje dwie wartoś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 istnieć tylko jedna wartość NULL. W aplikacjach biznesowych istnieje czasem wymóg stosowania unikalności tylko dla znanych wartości (różnych od NULL). W takich sytuacjach należy stworzyć tabelę z kolumnami bez ograniczenia UNIQUE, zbudować odpowiednią perspektywę z opcją SCHEMABINDING oraz stworzyć indeks na kolumnie, do której chcemy wstawiać unikalne wartości lub wartości NULL (Listing 10).
Podsumowanie
Widoki to jedne z najważniejszych obiektów w aplikacjach biznesowych. W SQL Server 2005 i SQL Server 2008 dzięki widokom można uzyskać dużą wydajność aplikacji, rozwiązać lub uprościć skomplikowane zadanie biznesowe, a także zapewnić duży poziom bezpieczeństwa tworzonego rozwiązania.
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 firm. Jest współautorem książek: Oracle 10g i Delphi. Programowanie baz danych oraz Photoshop. Pluginy i efekty specjalne. Oprócz hurtowni i baz danych, jego drugą informatyczną pasją jest fotografia cyfrowa i obróbka zdjęć za pomocą Adobe Photoshop. W wolnych chwilach kibicuje piłkarskiej reprezentacji Argentyny.
Kontakt z autorem: arturmoscicki@op.pl
