Funzioni vs stored procedure

Diciamo che devo implementare un pezzo di codice T-SQL che deve restituire una tabella come risultato. Posso implementare una funzione con valori di tabella o una stored procedure che restituisce un insieme di righe. Cosa dovrei usare?

In breve, quello che voglio sapere è:

Quali sono le principali differenze tra funzioni e stored procedure? Quali considerazioni devo tenere in considerazione per usare l’una o l’altra?

Se è probabile che si desideri combinare il risultato di questo pezzo di codice con altre tabelle, ovviamente una funzione valutata a livello di tabella consente di comporre i risultati in un’unica istruzione SELECT.

Generalmente, esiste una gerarchia (Visualizza

Quindi usa quello che ti consente in minima parte di esprimere il risultato desiderato.

Le funzioni devono essere deterministiche e non possono essere utilizzate per apportare modifiche al database, mentre le stored procedure consentono di eseguire inserimenti e aggiornamenti, ecc.

Dovresti limitare il tuo uso delle funzioni, poiché rappresentano un enorme problema di scalabilità per query complesse e complesse. Diventano una specie di “scatola nera” per Query Optimizer e vedrai enormi differenze nelle prestazioni tra l’uso di funzioni e semplicemente l’inserimento del codice in una query.

Ma sono sicuramente utili per i ritorni a valori di tabella in casi molto specifici.

Se è necessario analizzare un elenco delimitato da virgole, per simulare il passaggio di una matrice a una procedura, una funzione può trasformare l’elenco in una tabella. Questa è una pratica comune con Sql Server 2005, dato che non possiamo ancora passare le tabelle alle stored procedure (possiamo farlo con il 2008).

Dai documenti :

Se una stored procedure soddisfa i seguenti criteri, è un buon candidato per essere riscritta come funzione valutata a livello di tabella:

  • La logica è espressa in una singola istruzione SELECT ma è una procedura memorizzata, piuttosto che una vista, solo a causa della necessità di parametri.

  • La stored procedure non esegue le operazioni di aggiornamento, ad eccezione delle variabili di tabella.

  • Non sono necessarie istruzioni EXECUTE dinamiche.

  • La stored procedure restituisce un set di risultati.

  • Lo scopo principale della stored procedure è creare risultati intermedi che devono essere caricati in una tabella temporanea, che viene quindi interrogata in un’istruzione SELECT.

Ho intenzione di scrivere alcune interessanti differenze tra stored procedure e funzioni.

  • Possiamo utilizzare le funzioni nelle query selezionate ma non possiamo utilizzare le stored procedure nelle query selezionate.
  • Non possiamo usare funzioni non deterministiche nelle Funzioni ma possiamo usare funzioni non deterministiche nelle stored procedure. Ora sorge una domanda, qual è la funzione non deterministica. Ans è: –

    Una funzione non deterministica è quella funzione che restituisce output diversi per gli stessi valori di input in tempi diversi, come getdate (). Restituisce sempre un valore diverso ogni volta che viene eseguito.

    Eccezione:-

    Le versioni precedenti di sql server precedenti a sql 2000 non consentono di utilizzare la funzione getdate () nelle funzioni definite dall’utente, ma la versione 2005 e successive consente di utilizzare la funzione getdate () all’interno di una funzione definita dall’utente.

    Newid () è un altro esempio di funzione non deterministica ma non può essere utilizzato in funzioni definite dall’utente, ma è ansible utilizzarlo in stored procedure.

  • Possiamo utilizzare istruzioni DML (insert, update, delete) all’interno di una stored procedure ma non possiamo utilizzare istruzioni DML in funzioni su tabelle fisiche o tabelle permanenti. Se vogliamo eseguire l’operazione DML in funzioni, possiamo farlo su variabili di tabella non su tabelle permanenti.

  • Non possiamo usare la gestione degli errori all’interno della funzione, ma possiamo fare la gestione degli errori nelle stored procedure.

  1. La procedura può restituire zero o n valori, mentre la funzione può restituire un valore che è obbligatorio.

  2. Le procedure possono avere parametri di input / output per esso mentre le funzioni possono avere solo parametri di input.

  3. La procedura consente di selezionare sia l’istruzione DML che la funzione, mentre la funzione consente solo l’istruzione select in essa.

  4. Le funzioni possono essere richiamate dalla procedura mentre le procedure non possono essere richiamate dalla funzione.

  5. L’eccezione può essere gestita dal blocco try-catch in una procedura, mentre il blocco try-catch non può essere utilizzato in una funzione.

  6. Possiamo andare per la gestione delle transazioni in procedura, mentre non possiamo andare in funzione.

  7. Le procedure non possono essere utilizzate in un’istruzione select, mentre la funzione può essere incorporata in un’istruzione select.

  8. UDF (funzione definita dall’utente) può essere utilizzato nelle istruzioni SQL in qualsiasi punto della sezione WHERE / HAVING / SELECT mentre le stored procedure non possono essere.

  9. Le UDF che restituiscono le tabelle possono essere trattate come un altro set di righe. Questo può essere usato in JOIN con altre tabelle.

  10. Le UDF inline possono essere viste come viste che accettano parametri e possono essere utilizzate nelle operazioni JOIN e in altri set di righe.

Ad esempio, se si dispone di una funzione, è ansible utilizzarla come parte dell’istruzione SQL

 SELECT function_name(field1) FROM table 

Non funziona in questo modo per le stored procedure.

Ho eseguito alcuni test con un lungo bit di logica in esecuzione, con lo stesso bit di codice (una lunga istruzione SELECT) in esecuzione in una funzione con valori di tabella e una stored procedure, un EXEC / SELECT lineare e ciascuno eseguito in modo identico.

A mio parere, utilizzare sempre una funzione con valori di tabella anziché una stored procedure per restituire un set di risultati, poiché rende la logica molto più semplice e leggibile nelle query che successivamente si uniscono a esse e consente di riutilizzare la stessa logica. Per evitare un eccessivo impatto sulle prestazioni, utilizzo spesso i parametri “opzionali” (ad esempio, è ansible passare NULL) per consentire alla funzione di restituire il set di risultati in modo più rapido, ad esempio:

 CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int) AS RETURN SELECT DISTINCT SiteID, PersonID FROM dbo.SiteViewPermissions WHERE (@optPersonID IS NULL OR @optPersonID = PersonID) AND (@optSiteID IS NULL OR @optSiteID = SiteID) AND @RegionID = RegionID 

In questo modo puoi usare questa funzione per molte situazioni diverse e non fare un grande successo in termini di prestazioni. Credo che questo sia più efficiente del filtraggio successivo:

 SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1 

Ho usato questa tecnica in diverse funzioni, a volte con una lunga lista di parametri “opzionali” di questo tipo.

Io personalmente uso le funzioni di valore di tabella quando tutto ciò che sto restituendo è una singola tabella senza alcun effetto. Fondamentalmente li tratto come viste parametrizzate.

Se ho bisogno di più recordset restituiti o se ci saranno valori aggiornati nelle tabelle, io uso una stored procedure.

I miei 2 centesimi

Come accennato in precedenza, le funzioni sono più leggibili / componibili / autodocumentanti, ma sono meno performanti in generale e possono essere seriamente meno performanti se vieni portato con loro in join come

 SELECT * FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1 INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2 ON (tvf1.JoinId = tvf2.JoinId) 

Spesso, devi solo accettare la ridondanza del codice che un tvf potrebbe eliminare (a costi di prestazioni inaccettabili).

Un altro punto che non ho ancora visto menzionato è che non è ansible utilizzare le tabelle temporanee che cambiano lo stato del database all’interno di un multi-statement tvf. Il meccanismo più funzionalmente equivalente a una tabella temporanea è la modifica non di stato, nella variabile della tabella di memoria e per i set di dati di grandi dimensioni, una tabella temporanea sarà probabilmente più performante di una variabile di tabella. (Altre alternative includono le tabelle dinamiche e le espressioni con valori di tabella comuni, ma a un certo livello di complessità, queste cessano di essere una buona opzione IMO.)

Vorrei provare entrambi i test. È probabile che l’approccio sp o una tabella derivata siano significativamente più veloci di una funzione e, in tal caso, dovrebbe essere utilizzato tale approccio. In generale, evito le funzioni perché possono essere delle prestazioni.

Dipende 🙂 Se si desidera utilizzare il risultato con valori di tabella in un’altra procedura, è preferibile utilizzare una funzione TableValued. Se i risultati sono per un cliente, il proc memorizzato è solitamente il modo migliore per andare.