Kolumny wyliczane w SQL Server
Tworzenie raportów i zestawień wymaga czasami wykonania pewnych wyliczeń, podczas których na podstawie danych obecnych w tabeli tworzy się dodatkowe pola. Przypuśćmy, że mamy kolumnę określającą kwotę netto dowolnego towaru. Nasza firma jest jednocześnie bardzo miła dla stałych klientów i mamy dla nich przygotowaną specjalną ofertę. Każdy taki klient ma 10% zniżki. Jakie są możliwe rozwiązania? Podstawowe rozwiązanie to oczywiście odpowiednie napisanie instrukcji SELECT. Jest też inne. Przypuśćmy, że chcemy w pewnej kolumnie przechowywać status płatności. Status płatności mógłby zależeć od kwoty należnej, wpłaconej, daty płatności, ewentualnego odroczenia spłaty. Jak zadbać o to, aby taki status aktualizowany był po każdej zmianie dowolnej z kolumn mających wpływ na wyliczaną wartość? Znów przychodzą do głowy różne techniki, między innymi wyzwalacze (ang. trigger). Nie jest to jednak jedyna możliwość. Ośmielę się napisać, że nie jest to nawet dobre rozwiązanie. Oba powyższe przypadki znakomicie nadają się do pokazania pewnej rzadko stosowanej techniki polegającej na użyciu kolumn wyliczanych (ang. computed columns). Przyjrzyjmy się im bliżej.
Przykład kolumny wyliczanej
W zaprezentowanym przykładzie zajmiemy się bardzo uproszczonym systemem finansowym. Tabela będzie zawierać trzy zwykłe kolumny i dwie wyliczane. Te kolumny zwykłe to kolejno: identyfikator, kwota, jaką należy zapłacić za towar oraz stawka podatku VAT naliczana za ten produkt. Tabela mogłaby wyglądać następująco:
(
ID int identity(1,1),
Kwota decimal (9,2),
StawkaVAT decimal (9,2)
)
Teraz przechodzimy do częsci właściwej, a mianowicie do kolumn wyliczanych. Definiuje się je podobnie do zwykłych kolumn. Najpierw podaje się nazwę, po nim pojawia się słowo kluczowe AS, a następnie mniej lub bardziej złożone wyrażenie. Dodajmy zatem dwie kolumny. Jedna będzie zawierała stawkę podatku VAT dla danego towaru, druga natomiast kwotę brutto, czyli podstawową kwotę z doliczonym podatkiem. Przykład takiej tabeli pokazany jest poniżej:
(
ID int identity(1,1),
Kwota decimal (9,2),
StawkaVAT decimal (9,2),
VAT AS Kwota*StawkaVAT,
Brutto AS Kwota+Kwota*StawkaVAT
)
Przetestujmy ten prosty przykład. Wstawmy sobie do niego jakieś dwa rekordy, a następnie spróbujmy wyświetlić zawartość takiej tabeli. Zastosowany skrypt mógłby wyglądać następująco:
INSERT INTO KolumnyFunkcyjne VALUES (100, 0.23)
INSERT INTO KolumnyFunkcyjne VALUES (24, 0.08)
--pobierz dane z całej tabeli
SELECT * FROM KolumnyFunkcyjne
Po uruchomieniu powyższego skryptu dostaniemy taki oto rezultat:
ID | Kwota | StawkaVAT | VAT | Brutto |
1 | 100.00 | 0.23 | 23.0000 | 123.0000 |
2 | 20.00 | 0.08 | 1.9200 | 21.9200 |
Ograniczenia kolumn wyliczanych
Kolumny funkcyjne nie są rozwiązaniem wszystkich problemów. Mają dość mocne ograniczenia. Po pierwsze, nie da się umieszczać w wyrażeniu definiującym kolumnę wyliczaną innej kolumny wyliczanej. W związku z powyższym nieprawidłowa będzie między innymi taka instrukcja:
CREATE TABLE KolumnyFunkcyjne
(
ID int identity(1,1),
Kwota decimal (9,2),
StawkaVAT decimal (9,2),
VAT AS Kwota*StawkaVAT,
Brutto AS Kwota+VAT
)
Próba wykonania skryptu spowoduje pojawienie się następującego komunikatu:
Computed column 'VAT' in table 'KolumnyFunkcyjne' is not allowed to be used in another computed-column definition.
Akurat taki błąd nie jest szczególnie ograniczający. Po prostu instrukcje nam się nieco wydłużają.
Być może komuś przyszła do głowy inna koncepcja, a mianowicie użycie instrukcji SELECT w kolumnie wyliczanej. Niestety mam dla nich złą wiadomość. Póki co takie instrukcje są niedozwolone. Nie da się zatem zrobić czegoś na kształt poniższego skryptu:
--ich w kolumnach funkcyjnych
CREATE TABLE KolumnyFunkcyjne
(
ID int identity(1,1),
Kwota decimal (9,2),
StawkaVAT decimal (9,2),
VAT AS Kwota*StawkaVAT,
Brutto AS Kwota+Kwota*StawkaVAT,
MojaKolumna AS (SELECT TOP 1 Wartosc
FROM InnaTabela T WHERE T.Klucz=ID)
)
Komunikat będzie tym razem następujący:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Dalsze rozważania na temat kolumn funkcyjnych
Kolumny funkcyjne to, podobnie jak cała masa innych zagadnień, temat rzeka. Kolumny takie mogą być indeksowane, dla takich kolumn tworzą się odpowiednie wpisy, do których można się dynamicznie dostać korzystając z widoków systemowych. Postaram się w najbliższym czasie rozszerzyć ten temat, podając odpowiednie przykłady.
Kategoria:SQL Server
Komentarze:
Trochę niedobrze, że muszę zrobić to jakoś dynamicznie bo nie wiem jak, jestem noga z SQL dynamicznego... ;/
SELECT * FROM OPENROWSET('SQLNCLI',
'server=localhost;trusted_connection=yes',
'EXEC (@cmd)')
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@cmd".