Wprowadzenie
Kolumny obliczeniowe to po prostu wirtualne kolumny, których zawartość jest wynikiem wyrażenia. Zazwyczaj są one używane do przechowywania danych opartych na pozostałych kolumnach tabeli. Wyrażenie może zawierać inne nieobliczeniowe kolumny tabeli, stałe, operatory i funkcje, ale nie można określić zapytania jako wyrażenia dla kolumny obliczeniowej.
Ponieważ są to kolumny „wirtualne”, nie są przechowywane na dysku tak jak reszta tabeli. W rzeczywistości, nie są one przechowywane, ale obliczane za każdym razem, gdy kolumna jest dostępna w zapytaniu. Jak zobaczysz, możesz zmusić SQL Server do przechowywania („persist”) kolumny w tabeli z pewnymi ograniczeniami.
Najlepszym sposobem na zrozumienie, jak działają kolumny obliczeniowe, jest użycie próbek. Na końcu znajdziesz plik zawierający wszystkie skrypty użyte w tym artykule, a w tekście pokażemy kilka z nich, aby zilustrować wyjaśnienia. Na początek utworzymy dwie tabele: pierwszą, w której przechowywane będą informacje o fakturach oraz drugą, zawierającą wiersze szczegółów tych faktur. Możesz również znaleźć kilka wstawek w pliku skryptu, aby utworzyć przykładowe dane.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
Sposób tworzenia kolumny obliczeniowej jest taki sam, jak w przypadku tworzenia innych kolumn w tabeli, za pomocą instrukcji CREATE TABLE lub ALTER TABLE. Dla kolumny obliczeniowej, zastępujemy typ danych kolumny wyrażeniem, które zostanie użyte do uzyskania zawartości kolumny. Składnia składa się z nazwy kolumny, po której następuje słowo kluczowe „as”, a następnie wyrażenie. Utwórzmy kolumnę obliczeniową w tabeli detail_line, aby przechowywać całkowitą kwotę linii, którą obliczymy przez pomnożenie ceny jednostkowej i ilości.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Sprawdź, czy kolumna jest kolumną obliczeniową
Istnieje kilka sposobów, aby potwierdzić, że kolumna jest rzeczywiście kolumną obliczeniową. Jednym z nich jest użycie funkcji columnproperty() określającej właściwość „IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Innym sposobem uzyskania informacji o kolumnach obliczeniowych jest widok systemowy, sys.computed_columns. Widok ten jest rozszerzeniem widoku sys.columns. Oznacza to, że sys.computed_columns dziedziczy wszystkie kolumny z widoku sys.columns, a także dodaje inne, które są specyficzne dla tego typu kolumn. W całym artykule zobaczymy niektóre z kolumn w tym widoku, jak widzimy różne cechy kolumn obliczeniowych. Na razie wystarczy wiedzieć, że ten widok pokazuje tylko kolumny obliczeniowe i posiada kolumnę o nazwie is_computed, która mówi, czy kolumna jest obliczeniowa, czy nie. Oczywiście, wszystkie rekordy tego widoku będą miały jedynkę w tej kolumnie.
SELECT name, is_computed FROM sys.computed_columns;
Ponieważ zawartość kolumny jest obliczana za każdym razem, gdy kolumna jest przywoływana w zapytaniu, jej zawartość jest zawsze aktualizowana. Każda zmiana w kolumnach, które są zawarte w wyrażeniu, jest automatycznie odzwierciedlana w wartości kolumny. Możemy się o tym przekonać, zmieniając ilość w rejestrze w przykładowej tabeli detail_lines i sprawdzić wynik.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Krok naprzód
To, co widzieliśmy do tej pory z kolumnami obliczanymi, jest bardzo podstawowe, ponieważ polega jedynie na wykonywaniu obliczeń z innymi kolumnami. Jednak wyrażenie kolumn obliczanych może zawierać funkcje, zarówno standardowe funkcje T-SQL, jak i funkcje definiowane przez użytkownika (UDF). W ten sposób można znacznie bardziej rozszerzyć funkcjonalność tych kolumn.
Zobaczmy przykład takiego działania. Do tabeli Faktury dodamy kolumnę obliczeniową, która oblicza całkowitą kwotę faktury. Aby to zrobić, musimy uzyskać numer faktury i zapytać tabelę detail_lines, aby zsumować całkowitą kwotę z każdego rekordu z tym id faktury. Najlepszym sposobem na zrobienie tego jest użycie funkcji, która otrzymuje id faktury jako parametr i zwraca sumę. Następnie musimy utworzyć kolumnę, która korzysta z tej funkcji.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Możemy sprawdzić, czy kolumna działa poprawnie, dodając nowy rekord w tabeli detail_lines, dzięki czemu suma_rachunku powinna się zmienić.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Zmiana kolumny
Mogą zdarzyć się sytuacje, w których trzeba będzie zmodyfikować kolumnę obliczeniową. Niestety, nie jest to możliwe. Aby dokonać takiej zmiany, konieczne jest usunięcie kolumny i ponowne jej utworzenie z nowym wyrażeniem.
W przypadku, gdy kolumna obliczeniowa korzysta z zewnętrznej funkcji, nie będziemy mogli jej zmodyfikować. Jeśli spróbujemy, otrzymamy błąd informujący, że funkcja ta jest powiązana z tabelą. Aby zmienić funkcję, konieczne jest usunięcie kolumny, wykonanie modyfikacji funkcji, a na koniec ponowne utworzenie kolumny z nową wersją funkcji.
Definicję kolumny możemy uzyskać w kolumnie „definition” z widoku sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Przechowywanie kolumny obliczeniowej
Jak już wspomnieliśmy, kolumny te są „wirtualne”, więc nie są fizycznie przechowywane w tabeli. Istnieje jednak możliwość wymuszenia, aby obliczenia były fizycznie przechowywane w tabeli, co nazywane jest „persist „ing kolumny. Może to poprawić wydajność instrukcji SELECT, ponieważ pozwala uniknąć konieczności wykonywania obliczeń kolumny za każdym razem, gdy się do niej odwołujemy.
Dodatkowo, aby kolumna była trwała, wyrażenie użyte do jej utworzenia musi być wyrażeniem „deterministycznym”. Jak możemy zobaczyć na stronie Microsoftu, „funkcje deterministyczne zawsze zwracają ten sam wynik za każdym razem, gdy są wywoływane z określonym zestawem wartości wejściowych i przy tym samym stanie bazy danych.” (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Jeśli chcemy wiedzieć, czy SQL Server uważa wyrażenie obliczanej kolumny za deterministyczne, czy nie, możemy użyć funkcji columnproperty() z właściwością „IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Jeśli definicją kolumny jest funkcja zdefiniowana przez użytkownika, można również sprawdzić, czy sama ta funkcja jest deterministyczna, czy nie. Aby to zrobić, należy użyć funkcji objectproperty() z właściwością IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Jak widać z zapytań, kolumna z pierwszego przykładu, w której obliczamy całkowitą cenę detalu, jest uznawana za deterministyczną. Natomiast funkcja, która oblicza całkowitą cenę rachunku jest uznawana za niedeterministyczną. W ten sposób tylko kolumna total_price z tabeli detail_table może być przechowywana w tabeli.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Ponownie w widoku sys.computed_columns możesz zobaczyć pole is_persisted, które wskaże, czy kolumna jest persystowana, czy nie w tabeli.
Indeksy z kolumnami obliczonymi
Możliwe jest używanie kolumn obliczonych w indeksach, choć muszą one spełniać kilka wymagań:
- Własność: Wszystkie funkcje, które są używane w definicji kolumny obliczeniowej, muszą być własnością tego samego użytkownika, co tabela.
- Determinizm: Kolumna wyliczana musi być deterministyczna. Ponadto, jeśli kolumna zawiera wyrażenia CLR, oprócz tego, że musi być deterministyczna, kolumna musi być trwała.
- Dokładność: Wyrażenie obliczanej kolumny musi być precyzyjne. Oznacza to, że nie może być ono typu danych „float” lub „real”. Nie można również używać tego typu danych w swojej definicji. Tę właściwość można sprawdzić za pomocą funkcji columnproperty() poprzez określenie właściwości IsPrecise.
- Typ danych: Obliczana kolumna nie może należeć do typów text, ntext lub image. Również, jeżeli wyrażenie zawiera typy danych image, ntext, text, varchar (max), nvarchar (max), varbinary (max) lub xml, może być użyte tylko wtedy, gdy typ danych wynikający z wyrażenia jest dozwolony w indeksie.
Oprócz tych rozważań, połączenia używane do tworzenia kolumny i połączenia używane do tworzenia indeksu muszą mieć określone konfiguracje w celu wykonania tych działań.
Połączenie do tworzenia kolumny obliczeniowej musi mieć aktywną opcję ANSI_NULLS. Można to sprawdzić za pomocą funkcji columnproperty(), podając właściwość IsAnsiNullsOn.
Połączenie do tworzenia indeksu jest, jak również połączenia do wykonywania wstawiania, aktualizacji i usuwania rekordów wpływających na indeks muszą mieć aktywne opcje ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER. Dodatkowo opcja NUMERIC_ROUNDABORT musi być wyłączona.
Ostatnie rozważania
Na zakończenie przejrzymy kilka dodatkowych aspektów, o których należy wiedzieć, aby poprawnie korzystać z kolumn obliczeniowych.
Oczywiście, kolumny obliczeniowe nie mogą być aktualizowane ani włączane do listy wartości akcji INSERT. Chociaż kolumny wyliczane mogą być częścią listy wyników instrukcji select, mogą być również używane w klauzulach WHERE, ORDER BY lub we wszystkich tych, w których można umieścić wyrażenie.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Pomimo powyższego, kolumna wyliczana nie może być używana w definicji ograniczenia DEFAULT lub FOREIGN KEY. Nie może być również z definicją ograniczenia NOT NULL.
Z drugiej strony, kolumny wyliczone mogą być używane jako część ograniczeń PRIMARY KEY lub UNIQUE. Aby to zrobić, definicja kolumny obliczeniowej powinna być wyrażeniem deterministycznym.
Wnioski
Użycie kolumn obliczeniowych może być bardzo przydatne w niektórych sytuacjach. Musisz dokładnie zbadać, gdzie ich używać, ze względu na ograniczenia, które mają, specjalnie do tworzenia indeksu i persist ich. To jest dopiero początek. Nie krępuj się próbować nowych rzeczy i eksperymentować z kolumnami obliczeniowymi, aby znaleźć nowe możliwości.