Introduzione alle funzioni di SQL Server, ai loro vantaggi e svantaggi
Che cos'è una funzione SQL?
Le funzioni di SQL Server possono essere usate per restituire valori singoli (scalatori) o tabelle, usando routine T-SQL o CLR (Common Language Runtime) e spesso eseguendo calcoli più complessi di quelli che vorresti usare nel codice generale.
Quando è una buona idea usare una funzione anziché un codice inline?
Buon uso
Le funzioni possono essere utilizzate per sostituire le views (che restituiscono una tabella), come una colonna calcolata in una tabella, eseguire azioni di ricerca coerenti o semplicemente per modularizzare il codice che può aiutare a ridurre le modifiche necessarie.
Cattivo uso
Lo vediamo sempre, ma le funzioni non dovrebbero essere utilizzate per restituire dati di ricerca al posto di un join quando si ha a che fare con set di dati di grandi dimensioni. Ogni riga chiamerà la stessa funzione anche se ha già riscontrato quel valore. In questi casi, utilizzare un join.
Esempi di funzioni scalari
Le funzioni scalari sono utilizzate al meglio per eseguire azioni logiche come la riformattazione o i calcoli basati su riga poiché per loro natura sono chiamate per ogni riga, possono essere utilizzate per cercare dati in un'altra tabella, ma in generale otterrai prestazioni migliori utilizzando JOIN. Per questo, possiamo guardare la nostra funzione get age sul seguente link.
Non avrebbe senso memorizzare l'età di una persona nel momento in cui ha compilato un modulo, poiché quando i dati vengono interrogati in un secondo momento non saranno aggiornati. Un'opzione migliore sarebbe quella di acquisire una data di nascita e calcolarla al volo. Nella nostra funzione abbiamo aggiunto un campo fino a quando, che può essere utilizzato per retrodatare un calcolo, o forse più cupamente, calcolare l'età e l'ora della morte (questa funzione è stata estesa per un contratto SSN).
Esempio
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 @AgeENDEsempi di funzioni scalari
Per usarlo da una tabella immaginaria useremmo semplicemente questo, che fornirebbe l'età attuale o l'età alla morte.
Utilizzatelo in un SELECT
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]Esempi di funzioni scalari
Vantaggi : coerente, modulare, più compatto, riduce potenzialmente il numero di modifiche
Svantaggi : per vedere il codice è necessario guardare nella funzione
Sebbene sia generalmente utile, questa funzione è anche estremamente precisa, poiché utilizza una funzione di anno bisestile. Non è deterministico per natura, quindi non dovrebbe mai essere archiviato come dati persistenti.
Esempi di colonne di tabelle
Le colonne calcolate possono essere aggiunte come persistenti (cambia quando i dati lo fanno) o non persistenti (calcolate ogni volta che viene selezionata la riga). Possiamo esaminare due modi in cui li abbiamo usati qui all'interno del nostro sistema di gestione dei contenuti.
Nota : i dati persistenti possono essere più difficili da ottenere poiché richiedono il rispetto di una serie di vincoli
Non persistente: Età
Usando la funzione età come sopra, possiamo aggiungerlo in una tabella e passare i valori da altre colonne. Quindi lo selezioniamo semplicemente come colonna.
Aggiungi una tabella
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 persistente: Età
Vantaggi : Coerente, modulare
Svantaggi : rallenta la velocità delle query se non necessario.
Persistito: CSS minimizzato
Abbiamo una funzione che riduce lo spazio necessario per CSS fino al 30%. Chiamarlo regolarmente rallenterebbe la velocità di selezione della tabella e poiché i dati vengono aggiornati raramente, aveva senso eseguire calcoli al momento dell'inserimento/aggiornamento. Creando la colonna come funzione, non abbiamo nemmeno bisogno di eseguire queste operazioni come trigger.
Aggiungi una tabella
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)Persistito: CSS minimizzato
Può essere selezionato proprio come una normale colonna e i dati vengono archiviati nella tabella. Evita anche l'uso di una massiccia istruzione di sostituzione che gonfia il nostro codice.
Vantaggi : velocità di selezione coerente, modulare, più veloce, non c'è bisogno di un Trigger!
Svantaggi : Aumenta lo spazio necessario per la tavola, rallenta la velocità di inserimento
Sostituzione di una view
Tendiamo a non utilizzare le view, a parte quando utilizziamo regolarmente gli stessi join in più luoghi.
Anche in questi casi, non c'è motivo per cui una funzione di tabella non possa essere utilizzata in modo più efficace. La tabella che abbiamo utilizzato può essere trovata sul collegamento sottostante e abbiamo due esempi di utilizzo, uno tramite una funzione e l'altro tramite una vista.
Creazione di una funzione
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 RETURNENDGOCreazione di una 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.CalenderFunctionUtilizzo
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=0Sostituzione di una view
Vantaggi : Compatto da chiamare, restituito con chiave primaria (perfetto per ulteriori join), i parametri possono essere utilizzati in precedenza nel codice.
Svantaggi : più codice da costruire, meno flessibile
Utilizzare in Apply join
Le funzioni tabella sono ottime da usare in Apply Join, poiché i dati possono essere passati riga per riga. Usiamo la nostra funzione TextToRows per separare le stringhe in SQL Server. Nell'esempio seguente utilizziamo una doppia applicazione per dividere i dati due volte con diversi delimitatori.
Codice SQL
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) s2Maggiori dettagli
Alcune delle funzioni che abbiamo scritto possono essere trovate di seguito.
