Spis treści:

Kategoria:SQL Server


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:

CREATE TABLE KolumnyZwykle
(
  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:

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
)

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:

--wstaw dane do kolumn, które nie są wyliczane
INSERT INTO KolumnyFunkcyjne VALUES (100, 0.23)
INSERT INTO KolumnyFunkcyjne VALUES (24, 0.08)

--pobierz dane z całej tabeli
SELECTFROM KolumnyFunkcyjne

Po uruchomieniu powyższego skryptu dostaniemy taki oto rezultat:

IDKwotaStawkaVATVATBrutto
1100.000.2323.0000123.0000
220.000.081.920021.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:

--Nie można stosować kolumny funkcyjnej w innej funkcyjnej
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:

Msg 1759, Level 16, State 0, Line 1
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:

--nie można tworzyć podzapytań i umieszczać
--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:

Msg 1046, Level 15, State 1, Line 8
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

, 2013-12-20

Komentarze:

Mateusz (2016-11-15 22:51:54)
Proszę o pomoc. Mam fn dynamiczną i za każdym razem kiedy ją odpalam różna jest ilość kolumn (EXECUTE @SQL). Jak mogę te dane przechwycić i dodać chociażby do tabeli tymczasowej aby dalej można było te dane obrabiać. Nie mogę zadeklarować tabeli tymczasowej i zrobić po prostu INSERT INTO @Tabela ponieważ nie wiem ile jest kolumn. Nie mam pomysłu....
PD (2016-11-17 09:50:56)
Jeżeli nie wiadomo ile jest kolumn i jakich to trudno jest cokolwiek niedynamicznego na tym wykonać. Taka pełna dynamiczność pociąga za sobą konieczność konsekwentnego stosowania dynamicznych zapytań. Jeżeli jednak jest jakaś część wspólna (znana) lub tabela tymczasowa ma czemuś służyć, można wykorzystać instrukcję OPENROWSET z artykułu "SELECT z instrukcji systemowych".
Mateusz (2016-11-22 08:40:50)
Dziękuję za odpowiedź, jednak OPENROWSET nie bardzo mi pomógł.
Trochę niedobrze, że muszę zrobić to jakoś dynamicznie bo nie wiem jak, jestem noga z SQL dynamicznego... ;/
Mateusz (2016-11-23 12:30:30)
Niestety nie działa:
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".
PD (2016-11-28 10:49:33)
Zakładając, że @sql jest zapytaniem do wykonania, można zrobić tak:
DECLARE @sql nvarchar(MAX) = 'SELECT TOP 1 * FROM sys.columns'
EXEC('SELECT * INTO ##tmp FROM ('+@sql+') T')

--robimy coś z tabelą tymczasową
SELECTFROM ##tmp
DROP TABLE ##tmp
Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !