Einführung in SQL Server-Funktionen, ihre Vor- und Nachteile

Jede gut geschriebene Datenbank hat eine Auswahl an Funktionen, die meistens nützlich sind, aber wenn sie im falschen Kontext verwendet werden, können sie die Leistung beeinträchtigen

Was ist eine SQL-Funktion?

SQL Server-Funktionen können verwendet werden, um entweder einzelne (Skalierungs-)Werte oder Tabellen zurückzugeben, wobei T-SQL- oder CLR-Routinen (Common Language Runtime) verwendet werden und häufig komplexere Berechnungen durchgeführt werden, als Sie in allgemeinem Code verwenden möchten.

Wann ist es eine gute Idee, eine Funktion anstelle von Inline-Code zu verwenden?

Gute Verwendung

Funktionen können verwendet werden, um Ansichten zu ersetzen (eine Tabelle zurückzugeben), als berechnete Spalte in einer Tabelle, konsistente Suchaktionen durchzuführen oder einfach nur Ihren Code zu modularisieren, was dazu beitragen kann, die erforderlichen Änderungen zu reduzieren.

Schlechte Verwendung

Wir sehen es ständig, aber Funktionen sollten nicht verwendet werden, um Lookup-Daten anstelle eines Joins zurückzugeben, wenn Sie es mit großen Datensätzen zu tun haben. Jede Zeile ruft dieselbe Funktion auf, auch wenn sie diesen Wert bereits gefunden hat. Verwenden Sie in diesen Fällen einen Join.

Beispiele für Scaler-Funktionen

Scaler-Funktionen werden am besten verwendet, um Logik wie zeilenbasierte Neuformatierung oder Berechnungen durchzuführen, da sie naturgemäß für jede Zeile aufgerufen werden, sie können zum Nachschlagen von Daten in einer anderen Tabelle verwendet werden, aber im Allgemeinen erhalten Sie durch die Verwendung eine bessere Leistung ein Beitritt. Dazu können wir uns unsere Get-Age-Funktion unter folgendem Link ansehen.

Das Alter zum Zeitpunkt des Ausfüllens eines Formulars zu speichern, wäre nicht sinnvoll, da die Daten bei einer späteren Abfrage nicht mehr aktuell sind. Eine bessere Option wäre, ein Geburtsdatum zu erfassen und es spontan zu berechnen. In unserer Funktion haben wir ein Feld bis hinzugefügt, das verwendet werden kann, um eine Berechnung zurückzudatieren, oder vielleicht etwas düsterer, das Alter und den Zeitpunkt des Todes zu berechnen (diese Funktion wurde für einen NHS-Vertrag erweitert).

Mehr: Alter erhalten

Example

CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeEND

Beispiele für Scaler-Funktionen

Um dies aus einer fiktiven Tabelle zu verwenden, würden wir einfach this verwenden, was entweder das aktuelle Alter oder das Alter beim Tod liefern würde.

Use in a select statement

SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]

Beispiele für Scaler-Funktionen

Vorteile : Konsistent, modular, kompakter, potenziell weniger Änderungen

Nachteile : Um den Code zu sehen, müssen Sie in die Funktion schauen

Obwohl diese Funktion im Allgemeinen nützlich ist, ist sie auch äußerst genau, da sie eine Schaltjahrfunktion verwendet. Es ist von Natur aus nicht deterministisch und sollte daher niemals als persistente Daten gespeichert werden.

Beispiele für Tabellenspalten

Berechnete Spalten können entweder als persistent (ändert sich, wenn die Daten dies tun) oder als nicht persistent (wird jedes Mal berechnet, wenn die Zeile ausgewählt wird) hinzugefügt werden. Wir können uns zwei Möglichkeiten ansehen, wie wir sie hier in unserem Content Management System verwendet haben.

Hinweis : Persistente Daten können schwieriger zu erreichen sein, da eine Reihe von Einschränkungen erfüllt werden müssen

Nicht bestanden: Alter

Mit der obigen Altersfunktion können wir dies in eine Tabelle einfügen und Werte aus anderen Spalten übergeben. Wir wählen es dann einfach als Spalte aus.

Add to a table

CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)

Select Statement

SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]

Nicht bestanden: Alter

Vorteile : Einheitlich, modular

Nachteile : Verlangsamt die Abfragegeschwindigkeit, wenn sie nicht benötigt wird.

Beständig: Minimiertes CSS

Wir haben eine Funktion, die den Platzbedarf für CSS um bis zu 30 % reduziert. Ein regelmäßiger Aufruf würde die Auswahlgeschwindigkeit der Tabelle verlangsamen, und da die Daten selten aktualisiert werden, war es sinnvoll, Berechnungen zum Zeitpunkt des Einfügens/Aktualisierens durchzuführen. Indem wir die Spalte als Funktion erstellen, müssen wir diese Operationen auch nicht als Trigger ausführen.

Mehr: CSS-Präprozessor in SQL

Add to a Table

CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)

Beständig: Minimiertes CSS

Sie kann wie eine normale Spalte ausgewählt werden, und die Daten werden in der Tabelle gespeichert. Es vermeidet auch die Verwendung einer massiven Ersetzungsanweisung, die unseren Code aufbläht.

Vorteile : Konstante, modulare, schnellere Auswahlgeschwindigkeit, kein Trigger erforderlich!

Nachteile : Erhöht den Platzbedarf für den Tisch, verlangsamt die Einfügegeschwindigkeit

Ersetzen einer Ansicht

Wir neigen dazu, Ansichten nicht zu verwenden, außer wenn wir regelmäßig dieselben Joins an mehreren Stellen verwenden.

Auch in diesen Fällen spricht nichts dagegen, eine Tabellenfunktion effektiver einzusetzen. Die von uns verwendete Tabelle finden Sie unter dem folgenden Link, und wir haben zwei Verwendungsbeispiele, eines über eine Funktion und das andere über eine Ansicht.

mehr: Verwenden von SQL Server-Daten

Create a function

CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGO

Create a view

CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunction

Usage

SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0

Ersetzen einer Ansicht

Vorteile : Kompakt zum Aufrufen, zurückgegeben mit Primärschlüssel (perfekt für weitere Verknüpfungen), Parameter könnten früher im Code verwendet werden.

Nachteile : Mehr zu erstellender Code, weniger flexibel

Verwendung in Joins anwenden

Tabellenfunktionen eignen sich hervorragend für Joins anwenden, da Daten zeilenweise übergeben werden können. Wir verwenden unsere TextToRows-Funktion, um Zeichenfolgen in SQL Server auseinanderzuziehen. Im folgenden Beispiel verwenden wir eine doppelte Anwendung, um die Daten zweimal mit unterschiedlichen Trennzeichen aufzuteilen.

mehr: SQL Server-Funktion, die Text in Datenzeilen aufteilt

SQL Code

DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2

Further detail

Some of the functions we have written can be found below.