Von: Atif Shehzad | Updated: 2011-08-29 | Kommentare (8) | Verwandt: Mehr > T-SQL
Kostenloses MSSQLTips Webinar: Best Practices für die Entwicklung von SQL Server
In diesem Webinar erfahren Sie mehr über Best Practices für die Entwicklung von SQL Server. Andy Warren wird seine langjährige Erfahrung weitergeben, um einige Hinweise darauf zu geben, was für ihn am besten funktioniert hat und wie Sie etwas von diesem Wissen nutzen können.
Problem
Ich habe berechnete Spalten in einfachen Szenarien verwendet und diese funktionieren gut. In einigen Fällen stoßen wir jedoch bei der Implementierung von Geschäftslogik durch berechnete Spalten an Grenzen. Zum Beispiel müssen wir verschiedene Werte auf der Grundlage eines separaten Ausdrucks haben, um die berechneten Werte zu bestimmen. Außerdem besteht die Möglichkeit, dass ein Fehler bei der Teilung durch Null auftritt, der verhindert werden muss. Es ist auch erforderlich, auf Spalten außerhalb der Tabelle mit den berechneten Spalten zuzugreifen, um sie im Ausdruck für die berechneten Spalten zu verwenden. In diesem Tipp sehen wir uns an, wie man berechnete Spalten flexibler gestalten kann.
Lösung
Berechnete Spalten können eine große Flexibilität im Datenbankdesign bieten. Es ist möglich, den berechneten Ausdruck bedingt anzuwenden, Fehler bei der Teilung durch Null zu behandeln und auf jede Spalte außerhalb der Tabelle der berechneten Spalte zuzugreifen. Dadurch wird die berechnete Spalte flexibler und praktischer für Ihren Datenbankentwurf. Es folgt eine Liste einiger Szenarien, die wir in diesem Tipp behandeln werden.
- Bedingte Berechnung von Werten
- Fehler geteilt durch Null
- Zugriff auf eine Spalte außerhalb der Tabelle der berechneten Spalte
Wir werden einfache T-SQL-Techniken verwenden, um die oben genannten Probleme zu lösen. Dies wird die Flexibilität und die Fähigkeit von berechneten Spalten demonstrieren, mit solchen Problemen umzugehen.
Bedingte Berechnung von Werten
Wir haben eine einfache Tabelle mit einigen Spalten, die sich auf Mitarbeiter beziehen. Es gibt auch eine berechnete Spalte, die die Pensionierung mit 60 Jahren berechnet. Wir haben eine neue Anforderung, die Altersgrenze für Manager auf 65 Jahre festzulegen, sie aber für alle anderen Mitarbeiter bei 60 Jahren zu belassen.
Dies können wir mit einer bedingten Formel unter Verwendung einer CASE-Anweisung erreichen, wie im folgenden Code gezeigt. Für die Spalte DORetirement, wenn die Bezeichnung „Manager“ ist, beträgt der Ruhestand 65, sonst 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
Wenn Sie den obigen Code ausführen, sollten Sie die folgende Ausgabe erhalten. Durch die Verwendung einer CASE-Anweisung können wir separate Ausdrücke in Abhängigkeit von den angegebenen Kriterien definieren.
Divided by Zero Error
Wenn wir in einem rechnerischen Ausdruck dividieren, besteht die Möglichkeit, dass eine Null im Nenner steht. In solchen Fällen besteht die Gefahr, dass ein Fehler auftritt, weil durch Null geteilt wird.
Betrachten wir ein Szenario, in dem wir eine berechnete Spalte mit der Formel AS (Zähler/Nenner) haben, in der die Spalten Zähler und Nenner für die berechnete Spalte verwendet werden. In diesem Fall würde ein Fehler beim Dividieren durch Null auftreten, wenn der Ausdruck der berechneten Spalte mit einer Null in der Nenner-Spalte berechnet wird.
Wir können diesen Fehler vermeiden, indem wir die Null im Nenner durch einen NULL-Wert ersetzen, indem wir die Funktion NULLIF wie unten gezeigt verwenden. Der vierte Datensatz, den wir in die Tabelle einfügen, würde ein Problem beim Teilen durch Null verursachen, aber die NULLIF-Funktion wandelt diesen in einen NULL-Wert um.
-- 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
Da man nicht durch NULL dividieren kann, ist der zurückgegebene Wert NULL für diese Spalte für diesen einen Datensatz.
Standardmäßig erlaubt eine berechnete Spalte NULL-Werte. Sie können jedoch nur bei persistierten berechneten Spalten explizit NOT NULL angeben.
Zugriff auf eine Spalte außerhalb der Tabelle der berechneten Spalte
Eine berechnete Spalte kann nicht direkt auf eine Spalte außerhalb ihrer Tabelle zugreifen. Diese Einschränkung kann durch die Verwendung einer benutzerdefinierten Funktion umgangen werden. Eine UDF kann in einem Ausdruck verwendet werden, um auf eine beliebige Spalte außerhalb der Tabelle der berechneten Spalte zuzugreifen.
Im folgenden Skript wird eine UDF erstellt, um den Urlaubssaldo eines Mitarbeiters zu berechnen, um zu zeigen, wie viele Urlaubstage ein Mitarbeiter über die maximale Anzahl von 20 Tagen hinaus noch hat. Diese Daten stammen aus einer sekundären Tabelle namens 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
Unten sehen wir also die Anzahl der verbleibenden Tage, die für jeden Angestellten verfügbar sind.
Es gibt einige Überlegungen zu UDFs, wenn eine berechnete Spalte in einem Index verwendet werden soll. Mehr dazu lesen Sie in diesem Tipp: Wie man Indizes auf berechneten Spalten in SQL Server erstellt.
Nächste Schritte
Die oben genannten Probleme und ihre Lösungen funktionieren für persistierte und nicht persistierte berechnete Spalten auf die gleiche Weise. Die Nutzung der verfügbaren Flexibilität von berechneten Spalten kann Ihr Datenbankdesign verbessern. Berechnete Spalten sind eine gute Option, um Geschäftslogik durch Ausdrücke zu implementieren.
- Klicken Sie hier, um einen Tipp über die Grundlagen der Arbeit mit berechneten Spalten zu lesen
- Klicken Sie hier, um einen Tipp über das Erstellen von Indizes für berechnete Spalten zu lesen
Letzte Aktualisierung: 2011-08-29
Über den Autor
Atif Shehzad ist ein leidenschaftlicher SQL Server DBA, technischer Prüfer und Artikelautor.Alle meine Tipps ansehen
- Weitere Tipps für Datenbankentwickler…