Funzione e stored procedure in SQL Server

Ho imparato funzioni e stored procedure per un po ‘di tempo, ma non so perché e quando dovrei usare una funzione o una stored procedure. Mi sembrano uguali, forse perché sono un principiante al riguardo.

Qualcuno può dirmi perché?

Le funzioni sono valori calcolati e non possono eseguire modifiche ambientali permanenti a SQL Server (ovvero non sono consentite istruzioni INSERT o UPDATE).

Una funzione può essere utilizzata inline nelle istruzioni SQL se restituisce un valore scalare o può essere aggiunta se restituisce un set di risultati.

Un punto che vale la pena di notare dai commenti, che riassumono la risposta. Grazie a @Sean K Anderson:

Le funzioni seguono la definizione di computer science in quanto DEVONO restituire un valore e non possono alterare i dati che ricevono come parametri (gli argomenti). Le funzioni non possono modificare nulla, devono avere almeno un parametro e devono restituire un valore. I proc stored non devono avere un parametro, possono modificare gli oggetti del database e non devono restituire un valore.

La differenza tra SP e UDF è elencata di seguito:

+---------------------------------+----------------------------------------+ | Stored Procedure (SP) | Function (UDF - User Defined | | | Function) | +---------------------------------+----------------------------------------+ | SP can return zero , single or | Function must return a single value | | multiple values. | (which may be a scalar or a table). | +---------------------------------+----------------------------------------+ | We can use transaction in SP. | We can't use transaction in UDF. | +---------------------------------+----------------------------------------+ | SP can have input/output | Only input parameter. | | parameter. | | +---------------------------------+----------------------------------------+ | We can call function from SP. | We can't call SP from function. | +---------------------------------+----------------------------------------+ | We can't use SP in SELECT/ | We can use UDF in SELECT/ WHERE/ | | WHERE/ HAVING statement. | HAVING statement. | +---------------------------------+----------------------------------------+ | We can use exception handling | We can't use Try-Catch block in UDF. | | using Try-Catch block in SP. | | +---------------------------------+----------------------------------------+ 

Le funzioni e le stored procedure servono a scopi separati. Anche se non è la migliore analogia, le funzioni possono essere viste letteralmente come qualsiasi altra funzione che useresti in qualsiasi linguaggio di programmazione, ma i proc memorizzati sono più come singoli programmi o uno script batch.

Le funzioni normalmente hanno un’uscita e opzionalmente input. L’output può quindi essere utilizzato come input per un’altra funzione (un SQL Server integrato come DATEDIFF, LEN, ecc.) O come predicato per una query SQL, ad esempio SELECT a, b, dbo.MyFunction(c) FROM table o SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

I proc stored vengono utilizzati per associare query SQL insieme in una transazione e interfacciarsi con il mondo esterno. I framework come ADO.NET, ecc. Non possono chiamare direttamente una funzione, ma possono chiamare direttamente un processo memorizzato.

Le funzioni hanno tuttavia un pericolo nascosto: possono essere utilizzate in modo improprio e causare problemi di prestazioni piuttosto brutti: considera questa query:

 SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2) 

Dove MyFunction è dichiarato come:

 CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER AS BEGIN DECLARE @retval INTEGER SELECT localValue FROM dbo.localToNationalMapTable WHERE nationalValue = @someValue RETURN @retval END 

Quello che succede qui è che la funzione MyFunction viene chiamata per ogni riga nella tabella MyTable. Se MyTable ha 1000 righe, allora ci sono altre 1000 query ad-hoc sul database. Allo stesso modo, se la funzione viene chiamata quando specificato nella specifica della colonna, la funzione verrà chiamata per ogni riga restituita da SELECT.

Quindi devi fare attenzione a scrivere le funzioni. Se si seleziona SELECT da una tabella in una funzione, è necessario chiedersi se può essere eseguito meglio con un JOIN nel proc memorizzato padre o qualche altro costrutto SQL (come CASE … WHEN … ELSE … FINE).

Scrivere una funzione definita dall’utente quando si desidera calcolare e restituire un valore da utilizzare in altre istruzioni SQL; scrivere una stored procedure quando si desidera invece raggruppare un insieme eventualmente complesso di istruzioni SQL. Questi sono due casi d’uso piuttosto diversi, dopo tutto!

Differenze tra stored procedure e funzioni definite dall’utente:

  • Le stored procedure non possono essere utilizzate nelle istruzioni Select.
  • Le procedure memorizzate supportano la risoluzione dei nomi differiti.
  • Le stored procedure sono generalmente utilizzate per l’esecuzione della logica di business.
  • Le stored procedure possono restituire qualsiasi tipo di dati.
  • Le stored procedure possono accettare un numero maggiore di parametri di input rispetto alle funzioni definite dall’utente. Le stored procedure possono contenere fino a 21.000 parametri di input.
  • Le stored procedure possono eseguire Dynamic SQL.
  • Le procedure memorizzate supportano la gestione degli errori.
  • Le funzioni non deterministiche possono essere utilizzate nelle stored procedure.

  • Le funzioni definite dall’utente possono essere utilizzate nelle istruzioni Select.
  • Le funzioni definite dall’utente non supportano la risoluzione dei nomi differiti.
  • Le funzioni definite dall’utente sono generalmente utilizzate per i calcoli.
  • Le funzioni definite dall’utente dovrebbero restituire un valore.
  • Le funzioni definite dall’utente non possono restituire Immagini.
  • Le funzioni definite dall’utente accettano un numero minore di parametri di input rispetto alle stored procedure. Le UDF possono avere fino a 1.023 parametri di input.
  • Le tabelle temporanee non possono essere utilizzate in funzioni definite dall’utente.
  • Le funzioni definite dall’utente non possono eseguire Dynamic SQL.
  • Le funzioni definite dall’utente non supportano la gestione degli errori. RAISEERROR OR @@ERROR non sono consentiti nelle UDF.
  • Le funzioni non deterministiche non possono essere utilizzate nelle UDF. Ad esempio, GETDATE() non può essere utilizzato nelle UDF.

Differenza di base

La funzione deve restituire un valore ma in Stored procedure è facoltativo (la procedura può restituire zero o n valori).

Le funzioni possono avere solo parametri di input, mentre Procedure possono avere parametri di input / output.

La funzione accetta un parametro di input che è obbligatorio ma Stored Procedure potrebbe richiedere o n parametri di input.

Le funzioni possono essere richiamate da Procedure, mentre le procedure non possono essere richiamate dalla funzione.

Differenza Advance

La procedura consente di selezionare SELECT e DML (INSERT / UPDATE / DELETE) mentre Function consente solo l’istruzione SELECT in essa.

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

Le stored procedure non possono essere utilizzate nelle istruzioni SQL in nessuna parte della sezione WHERE / HAVING / SELECT mentre Function può essere.

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

La funzione inline può essere considerata come viste che accettano parametri e possono essere utilizzate in JOIN e altre operazioni del set di righe.

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

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

fonte

una funzione definita dall’utente è uno strumento importante disponibile per un programmatore server SQL. Puoi usarlo in linea in un’istruzione SQL in questo modo

 SELECT a, lookupValue(b), c FROM customers 

dove lookupValue sarà un UDF. Questo tipo di funzionalità non è ansible quando si utilizza una stored procedure. Allo stesso tempo non puoi fare certe cose all’interno di una UDF. La cosa fondamentale da ricordare qui è che UDF:

  • non è ansible creare modifiche permanenti
  • non può cambiare i dati

una procedura memorizzata può fare quelle cose.

Per me l’uso in linea di una UDF è l’uso più importante di una UDF.

Le stored procedure sono usate come script . Eseguono serie di comandi per te e puoi programmarli per l’esecuzione in determinati momentjs.

Le funzioni sono usate come metodi. Tu passi qualcosa e restituisce un risultato. Dovrebbe essere piccolo e veloce – lo fa al volo.

Procedura memorizzata:

  • È come un programma in miniatura in SQL Server.
  • Può essere semplice come un’istruzione select o complessa come uno script lungo che aggiunge, elimina, aggiorna e / o legge i dati da più tabelle in un database.
  • (Può implementare loop e cursori, che consentono entrambi di lavorare con risultati più piccoli o operazioni riga per riga sui dati).
  • Dovrebbe essere chiamato usando l’istruzione EXEC o EXECUTE .
  • Restituisce le variabili di tabella, ma non possiamo usare il parametro OUT .
  • Supporta le transazioni.

Funzione:

  • Non può essere utilizzato per aggiornare, eliminare o aggiungere record al database.
  • Restituisce semplicemente un singolo valore o un valore di tabella.
  • Può essere utilizzato solo per selezionare i record. Tuttavia, può essere chiamato molto facilmente da SQL standard, come ad esempio:

     SELECT dbo.functionname('Parameter1') 

    o

     SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects 
  • Per semplici operazioni di selezione riutilizzabili, le funzioni possono semplificare il codice. Fai attenzione a usare le clausole JOIN nelle tue funzioni. Se la tua funzione ha una clausola JOIN e la chiami da un’altra istruzione select che restituisce più risultati, quella chiamata di funzione JOIN quelle tabelle insieme per ogni riga restituita nel set di risultati. Quindi, anche se possono essere utili per semplificare alcune logiche, possono anche rappresentare un collo di bottiglia per le prestazioni se non vengono utilizzate correttamente.

  • Restituisce i valori usando il parametro OUT .
  • Non supporta le transazioni.

Decidere su quando utilizzare ciò che i seguenti punti possono aiutare-

  1. Le stored procedure non possono restituire una variabile di tabella in cui la funzione può farlo.

  2. È ansible utilizzare stored procedure per modificare i parametri dell’ambiente del server in cui l’utilizzo di funzioni non è ansible.

Saluti

Le funzioni di SQL Server, come i cursori, sono pensate per essere utilizzate come ultima arma! Hanno problemi di prestazioni e quindi l’utilizzo di una funzione valutata a livello di tabella dovrebbe essere evitato il più ansible. Parlare di prestazioni sta parlando di una tabella con oltre 1.000.000 di record ospitati su un server su un hardware di class media; in caso contrario non è necessario preoccuparsi del risultato di prestazioni causato dalle funzioni.

  1. Non utilizzare mai una funzione per restituire un set di risultati a un codice esterno (come ADO.Net)
  2. Utilizza la combinazione views / stored procs il più ansible. è ansible recuperare da futuri problemi di rendimento-crescita usando i suggerimenti DTA (Database Tuning Adviser) che vi darebbe (come viste indicizzate e statistiche) – a volte!

per ulteriori informazioni consultare: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

Inizia con funzioni che restituiscono un singolo valore. Il bello è che puoi inserire il codice utilizzato di frequente in una funzione e restituirli come una colonna in un set di risultati.

Quindi, potresti usare una funzione per un elenco parametrico di città. dbo.GetCitiesIn (“NY”) che restituisce una tabella che può essere utilizzata come join.

È un modo di organizzare il codice. Sapere quando qualcosa è riutilizzabile e quando è una perdita di tempo è qualcosa che si ottiene solo attraverso prove ed errori ed esperienze.

Inoltre, le funzioni sono una buona idea in SQL Server. Sono più veloci e possono essere abbastanza potenti. Selezione diretta e diretta. Attento a non abusare.

  STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION) * Procedure can return 0, single or | * Function can return only single value multiple values. | | * Procedure can have input, output | * Function can have only input parameters. | parameters. | * Procedure cannot be called from | * Functions can be called from function. | procedure. | * Procedure allows select as well as | * Function allows only select statement DML statement in it. | in it. | * Exception can be handled by | * Try-catch block cannot be used in a try-catch block in a procedure. | function. | * We can go for transaction management| * We can't go for transaction in procedure. | management in function. | * Procedure cannot be utilized in a | * Function can be embedded in a select select statement | statement. | * Procedure can not affect the state | * Function can not affect the state of of database means it can not perform| database means it can not perform CRUD operation on database. | CRUD operation on database. | * Procedure can use temporary tables. | * Function can not use temporary tables | temporary tables. | * Procedure can alter the server | * Function can not alter the environment parameters. | environment parameters. | * Procedure can use when we want | * Function can use when we want instead is to group a possibly- | to compute and return a value complex set of SQL statements. | for use in other SQL statements. 
  • È obbligatorio che Function restituisca un valore mentre non lo è per la stored procedure.
  • Seleziona istruzioni accettate solo in UDF mentre le istruzioni DML non sono richieste.
  • La stored procedure accetta tutte le istruzioni e le istruzioni DML.
  • UDF consente solo ingressi e non uscite.
  • La stored procedure consente sia input che output.
  • I blocchi di cattura non possono essere utilizzati in UDF ma possono essere utilizzati nella procedura memorizzata.
  • Nessuna transazione consentita nelle funzioni in UDF ma nella stored procedure è consentita.
  • Solo le variabili di tabella possono essere utilizzate in UDF e non in tabelle temporanee.
  • La stored procedure consente sia variabili di tabella che tabelle temporanee.
  • UDF non consente di richiamare le stored procedure dalle funzioni mentre le stored procedure consentono di richiamare le funzioni.
  • UDF viene utilizzato nella clausola join mentre le stored procedure non possono essere utilizzate nella clausola join.
  • La stored procedure consente sempre il ritorno a zero. Al contrario, l’UDF ha valori che devono tornare a un punto predeterminato.

Ecco un motivo pratico per preferire le funzioni rispetto alle stored procedure. Se si dispone di una stored procedure che richiede i risultati di un’altra stored procedure, è necessario utilizzare un’istruzione insert-exec. Ciò significa che è necessario creare una tabella temporanea e utilizzare un’istruzione exec per inserire i risultati della stored procedure nella tabella temporanea. È disordinato. Un problema con questo è che insert-execs non può essere annidato .

Se sei bloccato con procedure memorizzate che chiamano altre stored procedure, potresti incorrere in questo. Se la stored procedure nidificata restituisce semplicemente un set di dati, può essere sostituita con una funzione con valori di tabella e non riceverai più questo errore.

( questo è un altro motivo per cui dovremmo mantenere la business logic fuori dal database )

  • Le funzioni possono essere utilizzate in un’istruzione select dove non è ansible.

  • La stored procedure accetta sia i parametri di input che di output, ma le funzioni accettano solo i parametri di input.

  • Le funzioni non possono restituire valori di tipo text, ntext, image & timestamps come possono le procedure.

  • Le funzioni possono essere utilizzate come tipi di dati definiti dall’utente nella tabella create ma le procedure non possono.

*** Esempio: -create table (name varchar(10),salary getsal(name))

Qui getsal è una funzione definita dall’utente che restituisce un tipo di stipendio, quando la tabella viene creata non è assegnata alcuna memoria per il tipo di stipendio e anche la funzione getsal non viene eseguita, ma quando stiamo recuperando alcuni valori da questa tabella, la funzione getal viene eseguita e il return Type viene restituito come set di risultati.

In SQL Server, le funzioni e la stored procedure sono due diversi tipi di quadro.

Funzione: nel database SQL Server, le funzioni vengono utilizzate per eseguire alcune azioni e l’azione restituisce immediatamente un risultato. Le funzioni sono di due tipi:

  1. Sistema definito

  2. Definito dall’utente

Stored procedure: in SQL Server, le stored procedure sono memorizzate nel server e può essere zero, valori singoli e multipli. Le stored procedure sono di due tipi:

  1. Sistema stored procedure
  2. Procedure definite dall’utente