Úvod do funkcí serveru SQL, jejich výhody a nevýhody

Jakákoli dobře napsaná databáze bude mít výběr funkcí, které jsou většinou užitečné, ale při použití ve špatném kontextu mohou ve skutečnosti poškodit výkon

Co je funkce SQL?

Funkce serveru SQL lze použít k vrácení jednotlivých (škálovacích) hodnot nebo tabulek pomocí rutin T-SQL nebo CLR (běh v běžném jazyce) a často provádění složitějších výpočtů, než byste chtěli použít v obecném kódu.

Kdy je vhodné použít funkci spíše než vložený kód?

Dobré využití

Funkce lze použít k nahrazení pohledů (vrácení tabulky), jako vypočítaný sloupec v tabulce, provádění konzistentních vyhledávacích akcí nebo jednoduše jen k modularizaci kódu, což může pomoci při snižování potřebných změn.

Špatné použití

Vidíme to pořád, ale funkce by se neměly používat k vrácení vyhledávacích dat namísto spojení, když máte co do činění s velkými datovými sadami. Každý řádek bude volat stejnou funkci, i když již tuto hodnotu narazil. V těchto případech použijte spojení.

Příklady funkcí scaleru

Scalerové funkce se nejlépe používají k provádění logiky, jako je přeformátování založené na řádcích nebo výpočty, protože se svou povahou nazývají pro každý řádek, lze je použít k vyhledání dat v jiné tabulce, ale obecně získáte lepší výkon pomocí spojení. Za tímto účelem se můžeme podívat na naši funkci získání věku na následujícím odkazu.

Ukládat věk někoho v době, kdy vyplňoval formulář, by nedávalo smysl, protože při pozdějším dotazování dat bude zastaralé. Lepší možností by bylo zachytit datum narození a vypočítat ho za běhu. V naší funkci jsme přidali pole do, které lze použít k antedatování výpočtu, nebo snad ještě temněji, k výpočtu věku a doby smrti (tato funkce byla rozšířena o smlouvu NHS).

Více: Získejte věk

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

Příklady funkcí scaleru

Chcete-li to použít od fiktivního stolu, jednoduše bychom použili toto, které by poskytlo buď aktuální věk, nebo věk smrti.

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]

Příklady funkcí scaleru

Výhody : Konzistentní, modulární, kompaktnější, potenciálně snižuje počet změn

Nevýhody : Chcete-li zobrazit kód, musíte se podívat do funkce

I když je obecně užitečná, tato funkce je také velmi přesná, protože využívá funkci přestupného roku. Je to ze své podstaty nedeterministické, takže by se nikdy nemělo ukládat jako trvalá data.

Příklady sloupců tabulky

Vypočítané sloupce lze přidat buď jako trvalé (změny, když se data vyskytnou), nebo jako trvalé (vypočítané při každém výběru řádku). Můžeme se podívat na dva způsoby, jak jsme je zde použili v rámci našeho systému pro správu obsahu.

Poznámka : Trvalých dat může být obtížnější dosáhnout, protože vyžaduje splnění sady omezení

Trvalé: Věk

Pomocí výše uvedené funkce age můžeme toto přidat do tabulky a předat hodnoty z jiných sloupců. Pak jej jednoduše vybereme jako sloupec.

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]

Trvalé: Věk

Výhody : Konzistentní, modulární

Nevýhody : Pokud není potřeba, zpomalí rychlost dotazu.

Trvalé: Minified CSS

Máme funkci, která zmenšuje prostor potřebný pro CSS až o 30%. Pravidelné volání by zpomalilo rychlost výběru tabulky a protože data jsou zřídka aktualizována, mělo smysl provádět výpočty v době vložení / aktualizace. Vytvořením sloupce jako funkce nemáme potřebu provádět tyto operace jako spouštěč.

Více: CSS Pre Processor v SQL

Add to a Table

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

Trvalé: Minified CSS

Lze jej vybrat jako normální sloupec a data se ukládají do tabulky. Také se vyhne použití masivního příkazu nahrazení, který nadýmá náš kód.

Výhody : Konzistentní, modulární, rychlejší rychlost výběru, není třeba spoušť!

Nevýhody : Zvyšuje prostor potřebný pro stůl, zpomaluje rychlost vkládání

Výměna pohledu

Máme tendenci nepoužívat pohledy, kromě případů, kdy pravidelně používáme stejná spojení na více místech.

I v těchto případech není důvod, proč nelze tabulkovou funkci použít efektivněji. Tabulka, kterou jsme použili, najdete na níže uvedeném odkazu a máme dva příklady použití, jeden prostřednictvím funkce a druhý pomocí pohledu.

více: Použití dat serveru 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

Výměna pohledu

Výhody : Compact to call, vrácené s primárním klíčem (ideální pro další spojení), parametry lze použít dříve v kódu.

Nevýhody : Více kódu k sestavení, méně flexibilní

Použít v Použít spojení

Tabulkové funkce se skvěle používají v programu Apply Joins, protože data lze předávat po řádcích. Pomocí naší funkce TextToRows oddělujeme řetězce na serveru SQL Server. V níže uvedeném příkladu používáme dvojité použití k rozdělení dat dvakrát s různými oddělovači.

více: Funkce serveru SQL Server rozdělující text na datové řádky

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.