By: Atif Shehzad | Updated: 2011-08-29 | Komentarze (8) | Powiązane: More > T-SQL
Darmowe webinarium MSSQLTips: Development Best Practices for SQL Server
Weź udział w tym webinarium, aby dowiedzieć się o najlepszych praktykach rozwoju dla SQL Server. Andy Warren podzieli się swoim wieloletnim doświadczeniem, aby dać kilka wskazówek na temat tego, co działa najlepiej dla niego i jak można wykorzystać niektóre z tej wiedzy.
Problem
Użyłem kolumn obliczeniowych w prostych scenariuszach i działają one dobrze. Jednak w niektórych przypadkach napotykamy ograniczenia podczas wdrażania logiki biznesowej za pomocą kolumn wyliczanych. Na przykład jesteśmy zobowiązani do posiadania różnych wartości opartych na oddzielnym wyrażeniu w celu określenia wartości obliczeniowych. Ponadto, istnieje możliwość wystąpienia błędu dzielenia przez zero, któremu należy zapobiec. Wymagany jest również dostęp do kolumn spoza tabeli kolumn obliczeniowych w celu użycia ich w wyrażeniu kolumny obliczeniowej. W tym poradniku przyjrzymy się, jak uczynić kolumny wyliczane bardziej elastycznymi.
Rozwiązanie
Kolumny wyliczane mogą dodać dużą elastyczność w projektowaniu bazy danych. Możliwe jest warunkowe zastosowanie wyrażenia obliczeniowego, obsługa błędu dzielenia przez zero oraz dostęp do dowolnej kolumny spoza tabeli kolumny obliczeniowej. Dzięki temu kolumna wyliczana staje się bardziej elastyczna i poręczna w projektowaniu bazy danych. Poniżej znajduje się lista niektórych scenariuszy, które zamierzamy obsłużyć w tym poradniku.
- Warunkowe obliczanie wartości
- Błąd dzielenia przez zero
- Dostęp do kolumny spoza tabeli kolumny obliczeniowej
Użyjemy prostych technik T-SQL do rozwiązania wyżej wymienionych problemów. To zademonstruje elastyczność i zdolność kolumn obliczeniowych do obsługi wszelkich takich problemów.
Obliczanie warunkowe wartości
Mamy prostą tabelę z kilkoma kolumnami związanymi z pracownikami. Jest tam również kolumna obliczeniowa, która oblicza emeryturę w wieku 60 lat. Mamy nowe wymaganie, aby ustawić limit wieku na 65 lat dla menedżerów, ale zachować go na poziomie 60 lat dla wszystkich innych pracowników.
Możemy to osiągnąć za pomocą formuły warunkowej z użyciem instrukcji CASE, jak pokazano w poniższym kodzie. Dla kolumny DORetirement, jeśli oznaczenie to „Manager”, emerytura będzie wynosić 65, w przeciwnym razie będzie to 60.
-- Script# 1: Computed column with conditional formula-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, DATETIME NOT NULL, AS CASE WHEN designation = 'Manager' THEN (DATEADD(YEAR,(65),)) ELSE (DATEADD(YEAR,(60),)) END)GO --Insert sample data INSERT INTO CCTest (empNumb, Designation, DOBirth) SELECT 84, 'DBA', '1985-12-13' UNION ALLSELECT 85, 'DBA', '1980-11-18' UNION ALLSELECT 86, 'Manager', '1978-01-19' UNION ALLSELECT 88, 'Manager', '1985-12-13' UNION ALLSELECT 90, 'Developer', '1975-07-23' GO -- Check the required functionality in resultSELECT Designation, datediff(yy,dobirth,doretirement ) AgeLimit, DOBirth, DORetirement FROM CCTestGO
Jeśli uruchomisz powyższy kod, powinieneś otrzymać wynik jak poniżej. Używając instrukcji CASE, możemy zdefiniować oddzielne wyrażenia w zależności od podanych kryteriów.
Błąd dzielenia przez zero
W wyrażeniu obliczeniowym, jeśli dzielimy, może istnieć szansa, że w mianowniku znajduje się zero. W takich przypadkach ryzykujemy wystąpienie błędu z powodu dzielenia przez zero.
Rozważmy scenariusz, w którym mamy kolumnę obliczeniową z formułą AS (licznik/ mianownik), gdzie kolumny licznika i mianownika są używane dla kolumny obliczeniowej. W tym przypadku błąd dzielenia przez zero byłby napotkany za każdym razem, gdy wyrażenie kolumny obliczeniowej jest obliczane z zerem w kolumnie mianownika.
Błędu tego możemy uniknąć, zastępując zero w mianowniku wartością NULL za pomocą funkcji NULLIF, jak pokazano poniżej. Czwarty rekord, który wstawiamy do tabeli, spowodowałby problem z dzieleniem przez zero, ale funkcja NULLIF konwertuje go na NULL.
-- Script# 2: Avoiding divided by zero error-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( int NOT NULL, int NOT NULL, AS (Numerator/NULLIF(Denominator,0)) )GO--Insert sample dataINSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 3 UNION ALLSELECT 88, 0 UNION ALLSELECT 90, 15GO-- Check the resultSELECT * from CCTestGO
Ponieważ nie można dzielić przez NULL, zwróconą wartością jest NULL dla tej kolumny dla tego jednego rekordu.
Domyślnie kolumna obliczeniowa dopuszcza wartości NULL. Można jednak jawnie określić NOT NULL tylko w przypadku trwałych kolumn obliczeniowych.
Dostęp do kolumny spoza tabeli kolumny obliczeniowej
Kolumna obliczeniowa nie może bezpośrednio uzyskać dostępu do dowolnej kolumny spoza swojej tabeli. Ograniczenie to można pokonać za pomocą funkcji zdefiniowanej przez użytkownika. UDF może być użyty w wyrażeniu, aby uzyskać dostęp do dowolnej kolumny spoza tabeli kolumny obliczeniowej.
W poniższym skrypcie utworzono UDF do obliczenia salda urlopu pracownika, aby pokazać, ile dni urlopu pozostało pracownikowi poza maksymalnym limitem 20 dni. Dane te pochodzą z tabeli pomocniczej o nazwie LeaveBalance.
--Script # 3: Use UDF to access column in other table-- Use sample databaseUSE GO -- Create Table to reference in UDFIF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL DROP TABLE LeaveBalanceGOCREATE TABLE . ( INT NOT NULL, TINYINT NOT NULL, )GO--Insert sample dataINSERT INTO LeaveBalanceSELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 13 UNION ALLSELECT 88, 7 UNION ALLSELECT 90, 15GO-- Create UDF to get leave balanceIF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL DROP FUNCTION UDF_GetLeaveBalanceGO-- Create UDF to use in computed columnCREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)RETURNS TINYINTASBEGIN DECLARE @LeaveBalance TINYINT SELECT @LeaveBalance = (20 - LeavesAvailed) FROM LeaveBalance WHERE EmpNumb = @empnumb RETURN @LeaveBalanceENDGO-- Create Table to use computed columnIF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, AS (.UDF_GetLeaveBalance(EmpNumb)) )GO--Insert sample dataINSERT INTO CCTest (EmpNumb, Designation) SELECT 840, 'DBA' UNION ALLSELECT 805, 'DBA' UNION ALLSELECT 846, 'Manager' UNION ALLSELECT 88, 'Manager' UNION ALLSELECT 90, 'Developer' GO-- Check the resultSELECT * from CCTestGO
Więc poniżej możemy zobaczyć liczbę pozostałych dni dostępnych dla każdego pracownika.
Są pewne uwagi związane z UDF, gdy kolumna obliczeniowa ma być użyta w indeksie. Więcej na ten temat można przeczytać w tym poradniku: Jak tworzyć indeksy na kolumnach obliczeniowych w SQL Server.
Następne kroki
Powyższe problemy i ich rozwiązania działają w ten sam sposób dla trwałych lub nietrwałych kolumn obliczeniowych. Wykorzystanie dostępnej elastyczności kolumn wyliczanych może poprawić Twój projekt bazy danych. Kolumny wyliczeniowe są dobrą opcją do wykorzystania przy implementacji logiki biznesowej poprzez wyrażenia.
- Kliknij tutaj, aby przeczytać poradę na temat podstaw pracy z kolumnami wyliczeniowymi
- Kliknij tutaj, aby przeczytać poradę na temat tworzenia indeksów na kolumnach wyliczeniowych
Ostatnia aktualizacja: 2011-08-29
O autorze
Atif Shehzad jest pasjonatem SQL Server DBA, recenzentem technicznym i autorem artykułów.Zobacz wszystkie moje wskazówki
- Więcej wskazówek dla programistów baz danych…
.