Introduction aux fonctions SQL Server, leurs avantages et leurs inconvénients
Qu'est-ce qu'une fonction SQL?
Les fonctions SQL Server peuvent être utilisées pour renvoyer des valeurs uniques (scalers) ou des tables, à l'aide de routines T-SQL ou CLR (Common Language Runtime) et souvent en effectuant des calculs plus complexes que vous ne le souhaiteriez dans le code général.
Quand est-ce une bonne idée d'utiliser une fonction plutôt que du code en ligne?
Bon usage
Les fonctions peuvent être utilisées pour remplacer des vues (renvoyer une table), en tant que colonne calculée dans une table, effectuer des actions de recherche cohérentes ou simplement pour modulariser votre code, ce qui peut aider à réduire les changements nécessaires.
Mauvaise utilisation
Nous le voyons tout le temps, mais les fonctions ne doivent pas être utilisées pour renvoyer des données de recherche à la place d'une jointure lorsque vous traitez de grands ensembles de données. Chaque ligne appellera la même fonction même si elle a déjà rencontré cette valeur. Dans ces cas, utilisez une jointure.
Exemples de fonctions de mise à l'échelle
Les fonctions de mise à l'échelle sont mieux utilisées pour effectuer une logique telle que le reformatage ou les calculs basés sur les lignes, car de par leur nature, elles sont appelées pour chaque ligne, elles peuvent être utilisées pour rechercher des données dans une autre table, mais en général, vous obtiendrez de meilleures performances en utilisant une jointure. Pour cela, nous pouvons consulter notre fonction d'obtention de l'âge sur le lien suivant.
Stocker l'âge d'une personne au moment où elle a rempli un formulaire n'aurait aucun sens, car lorsque les données seront interrogées plus tard, elles seront obsolètes. Une meilleure option serait de saisir une date de naissance et de la calculer à la volée. Dans notre fonction, nous avons ajouté un champ jusqu'à, qui peut être utilisé pour antidater un calcul, ou peut-être plus sombrement, calculer l'âge au moment du décès (cette fonction a été étendue pour un contrat NHS).
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 @AgeENDExemples de fonctions de mise à l'échelle
Pour utiliser cela à partir d'une table fictive, nous utiliserions simplement ceci, qui fournirait soit l'âge actuel, soit l'âge au décès.
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]Exemples de fonctions de mise à l'échelle
Avantages : cohérent, modulaire, plus compact, réduit potentiellement le nombre de changements
Inconvénients : pour voir le code, vous devez regarder dans la fonction
Bien qu'elle soit généralement utile, cette fonction est également extrêmement précise, car elle utilise une fonction d'année bissextile. Il est par nature non déterministe et ne doit donc jamais être stocké en tant que données persistantes.
Exemples de colonnes de tableau
Les colonnes calculées peuvent être ajoutées comme persistantes (change lorsque les données le font) ou non persistantes (calculées chaque fois que la ligne est sélectionnée). Nous pouvons examiner deux façons dont nous les avons utilisés ici dans notre système de gestion de contenu.
Remarque : les données persistantes peuvent être plus difficiles à obtenir car elles nécessitent un ensemble de contraintes à respecter
Non persistant: Âge
En utilisant la fonction age comme ci-dessus, nous pouvons l'ajouter dans une table et transmettre les valeurs d'autres colonnes. Nous la sélectionnons ensuite simplement comme colonne.
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]Non persistant: Âge
Avantages : cohérent, modulaire
Inconvénients : ralentit la vitesse des requêtes si cela n'est pas nécessaire.
Persistant: CSS minifié
Nous avons une fonction qui réduit l'espace nécessaire pour CSS jusqu'à 30%. L'appeler régulièrement ralentirait la vitesse de sélection de la table, et comme les données sont rarement mises à jour, il était logique d'effectuer des calculs au moment de l'insertion / de la mise à jour. En créant la colonne en tant que fonction, nous n'avons pas non plus besoin d'effectuer ces opérations en tant que déclencheur.
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)Persistant: CSS minifié
Elle peut être sélectionnée comme une colonne normale et les données sont stockées dans la table. Cela évite également l'utilisation d'une instruction de remplacement massive qui gonfle notre code.
Avantages : vitesse de sélection constante, modulaire, plus rapide, pas besoin de déclencheur!
Inconvénients : augmente l'espace nécessaire pour la table, ralentit la vitesse d'insertion
Remplacement d'une vue
Nous avons tendance à ne pas utiliser de vues, sauf lorsque nous utilisons régulièrement les mêmes jointures à plusieurs endroits.
Même dans ces cas, il n'y a aucune raison pour laquelle une fonction de table ne peut pas être utilisée plus efficacement. Le tableau que nous avons utilisé se trouve sur le lien ci-dessous, et nous avons deux exemples d'utilisation, l'un via une fonction et l'autre via une vue.
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 RETURNENDGOCreate 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.CalenderFunctionUsage
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=0Remplacement d'une vue
Avantages : Compact à appeler, renvoyé avec la clé primaire (parfait pour une jointure ultérieure), les paramètres pourraient être utilisés plus tôt dans le code.
Inconvénients : plus de code à construire, moins flexible
Utilisation dans Apply Joins
Les fonctions de table sont idéales à utiliser dans Apply Joins, car les données peuvent être transmises ligne par ligne. Nous utilisons notre fonction TextToRows pour séparer les chaînes dans SQL Server. Dans l'exemple ci-dessous, nous utilisons une double application pour diviser les données deux fois avec des délimiteurs différents.
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) s2Further detail
Some of the functions we have written can be found below.
