Wprowadzenie do funkcji SQL Server, ich zalety i wady

Każda dobrze napisana baza danych będzie miała wybór funkcji, przez większość czasu są one przydatne, ale użyte w niewłaściwym kontekście mogą w rzeczywistości zaszkodzić wydajności

Co to jest funkcja SQL?

Korzystanie z funkcji programu SQL Server

Funkcje programu SQL Server mogą służyć do zwracania pojedynczych (skalujących) wartości lub tabel przy użyciu procedur T-SQL lub CLR (czasu wykonywania języka wspólnego) i często wykonywania bardziej złożonych obliczeń, niż chciałbyś używać w kodzie ogólnym.

Kiedy lepiej jest używać funkcji zamiast kodu wbudowanego?

Dobre użycie

Funkcje mogą służyć do zastępowania widoków (zwracania tabeli), jako kolumny obliczeniowej w tabeli, wykonywania spójnych działań wyszukiwania lub po prostu do modularyzacji kodu, co może pomóc w zmniejszeniu potrzebnych zmian.

Złe użycie

Widzimy to cały czas, ale nie należy używać funkcji do zwracania danych wyszukiwania zamiast sprzężenia, gdy mamy do czynienia z dużymi zbiorami danych. Każdy wiersz wywoła tę samą funkcję, nawet jeśli napotkał już tę wartość. W takich przypadkach użyj złączenia.

Przykłady funkcji skalera

Funkcji skalera najlepiej używać do wykonywania operacji logicznych, takich jak ponowne formatowanie oparte na wierszach lub obliczenia, ponieważ z natury są one wywoływane dla każdego wiersza, można ich używać do wyszukiwania danych w innej tabeli, ale ogólnie lepszą wydajność uzyskasz, używając połączenie. W tym celu możemy spojrzeć na naszą funkcję get age pod poniższym linkiem.

Przechowywanie czyjegoś wieku w momencie wypełniania formularza nie miałoby sensu, ponieważ przy późniejszym odpytywaniu danych będą one nieaktualne. Lepszym rozwiązaniem byłoby uchwycenie daty urodzenia i obliczenie jej w locie. W naszej funkcji dodaliśmy pole do, które może służyć do datowania wstecznego obliczeń, a może bardziej ponuro, do obliczenia wieku i czasu zgonu (funkcja ta została rozszerzona dla kontraktu NHS).

Więcej: Get Age

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

Przykłady funkcji skalera

Aby użyć tego z fikcyjnej tabeli, użylibyśmy po prostu tego, co zapewniłoby albo obecny wiek, albo wiek w chwili śmierci.

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]

Przykłady funkcji skalera

Zalety : spójna, modułowa, bardziej kompaktowa, potencjalnie zmniejsza liczbę zmian

Wady : Aby zobaczyć kod, musisz zajrzeć do funkcji

Ta funkcja jest ogólnie użyteczna, ale jest również niezwykle dokładna, ponieważ wykorzystuje funkcję roku przestępnego. Z natury nie jest deterministyczny, więc nigdy nie powinien być przechowywany jako trwałe dane.

Przykłady kolumn tabeli

Kolumny obliczeniowe można dodać jako utrwalone (zmieniają się, gdy dane ulegną zmianie) lub nietrwałe (obliczane za każdym razem, gdy wiersz jest zaznaczony). Możemy przyjrzeć się dwóm sposobom ich wykorzystania w naszym systemie zarządzania treścią.

Uwaga : Utrwalone dane mogą być trudniejsze do osiągnięcia, ponieważ wymagają spełnienia zestawu ograniczeń

Nietrwałe: wiek

Korzystając z funkcji wieku, jak powyżej, możemy dodać to do tabeli i przekazać wartości z innych kolumn. Następnie po prostu wybieramy go jako kolumnę.

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]

Nietrwałe: wiek

Zalety : Konsekwentna, modułowa

Wady : spowalnia szybkość zapytań, jeśli nie jest to potrzebne.

Utrwalony: zminimalizowany CSS

Mamy funkcję, która zmniejsza przestrzeń potrzebną na CSS nawet o 30%. Regularne wywoływanie tego spowolniłoby prędkość wybierania tabeli, a ponieważ dane są rzadko aktualizowane, sensowne było wykonywanie obliczeń w czasie wstawiania / aktualizowania. Tworząc kolumnę jako funkcję, nie musimy również wykonywać tych operacji jako wyzwalacza.

Więcej: CSS Pre Processor w SQL

Add to a Table

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

Utrwalony: zminimalizowany CSS

Można ją wybrać tak jak zwykłą kolumnę, a dane są przechowywane w tabeli. Pozwala to również uniknąć użycia masowej instrukcji zamiany, która nadyma nasz kod.

Zalety : Spójna, modułowa, szybsza prędkość wybierania, bez spustu!

Wady : Zwiększa przestrzeń potrzebną na stół, zmniejsza prędkość wkładania

Zastępowanie widoku

Zwykle nie używamy widoków, z wyjątkiem sytuacji, gdy regularnie używamy tych samych połączeń w wielu miejscach.

Nawet w takich przypadkach nie ma powodu, dla którego funkcja tabeli nie mogłaby być używana efektywniej. Tabelę, której użyliśmy, można znaleźć pod linkiem poniżej i mamy dwa przykłady użycia, jeden za pomocą funkcji, a drugi za pomocą widoku.

więcej: Korzystanie z dat SQL Server

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

Zastępowanie widoku

Korzyści : Kompaktowy do wywołania, zwracany z kluczem podstawowym (idealny do dalszego łączenia), parametry mogą być użyte wcześniej w kodzie.

Wady : więcej kodu do zbudowania, mniej elastyczna

Użyj w Zastosuj połączenia

Funkcje tabel świetnie nadają się do stosowania w stosowaniu połączeń, ponieważ dane mogą być przekazywane wiersz po wierszu. Używamy naszej funkcji TextToRows, aby rozdzielić ciągi znaków w SQL Server. W poniższym przykładzie używamy podwójnego zastosowania, aby dwukrotnie podzielić dane z różnymi ogranicznikami.

więcej: Funkcja SQL Server dzieląca tekst na wiersze danych

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.