Creazione di funzioni definite dall'utente (Motore di database)Create User-defined Functions (Database Engine)

SI APPLICA A: sìSQL Server sìDatabase SQL di Azure noAzure SQL Data Warehouse noParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

In questo argomento viene descritto come creare una funzione definita dall'utente in SQL ServerSQL Server usando Transact-SQLTransact-SQL.This topic describes how to create a user-defined function (UDF) in SQL ServerSQL Server by using Transact-SQLTransact-SQL.

Prima di iniziareBefore You Begin

Limitazioni e restrizioniLimitations and restrictions

  • Non è possibile utilizzare funzioni definite dall'utente per eseguire azioni che modificano lo stato del database.User-defined functions cannot be used to perform actions that modify the database state.

  • Le funzioni definite dall'utente non possono contenere una clausola OUTPUT INTO che ha una tabella come destinazione.User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.

  • Tramite le funzioni definite dall'utente non possono essere restituiti più set di risultati.User-defined functions can not return multiple result sets. Utilizzare una stored procedure se è necessario restituire più set di risultati.Use a stored procedure if you need to return multiple result sets.

  • In una funzione definita dall'utente la gestione degli errori è limitata.Error handling is restricted in a user-defined function. Una funzione definita dall'utente non supporta TRY...CATCH, @ERROR o RAISERROR.A UDF does not support TRY...CATCH, @ERROR or RAISERROR.

  • Tramite le funzioni definite dall'utente non è possibile chiamare una stored procedure normale, bensì una estesa.User-defined functions cannot call a stored procedure, but can call an extended stored procedure.

  • Nelle funzioni definite dall'utente non è possibile utilizzare tabelle temporanee o SQL dinamiche.User-defined functions cannot make use of dynamic SQL or temp tables. Sono consentite le variabili di tabella.Table variables are allowed.

  • In una funzione definita dall'utente non sono consentite istruzioni SET.SET statements are not allowed in a user-defined function.

  • Non è consentita la clausola FOR XML.The FOR XML clause is not allowed.

  • È possibile nidificare le funzioni definite dall'utente, ovvero una funzione definita dall'utente ne può richiamare un'altra.User-defined functions can be nested; that is, one user-defined function can call another. Il livello di nidificazione aumenta all'avvio della funzione richiamata e diminuisce al termine dell'esecuzione della funzione.The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. Le funzioni definite dall'utente possono essere nidificate fino a un massimo di 32 livelli.User-defined functions can be nested up to 32 levels. Se viene superato il livello massimo di nidificazioni, l'intera sequenza di funzioni chiamanti ha esito negativo.Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Qualsiasi riferimento al codice gestito presente in una funzione definita dall'utente Transact-SQL viene considerato come un livello nel contesto del limite di 32 livelli di nidificazione.Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite.Methods invoked from within managed code do not count against this limit.

  • Nella definizione di una funzione definita dall'utentenon è possibile includereTransact-SQLTransact-SQL le istruzioni di Service Broker seguenti:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

    • BEGIN DIALOG CONVERSATION

    • END CONVERSATION

    • GET CONVERSATION GROUP

    • MOVE CONVERSATION

    • RECEIVE

    • SEND

PermissionsPermissions

È necessario disporre dell'autorizzazione CREATE FUNCTION nel database e dell'autorizzazione ALTER per lo schema in cui la funzione è in fase di creazione.Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. Se per la funzione viene specificato un tipo definito dall'utente, è necessario disporre dell'autorizzazione EXECUTE per tale tipo.If the function specifies a user-defined type, requires EXECUTE permission on the type.

Funzioni scalariScalar Functions

Nell'esempio seguente viene creata una funzione scalare con istruzioni multiple nel database AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012AdventureWorks2012 database. La funzione accetta un valore di input, un valore ProductIDe restituisce un singolo valore di dati, la quantità aggregata del prodotto specificato nelle scorte.The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END; 

Nell'esempio seguente viene utilizzata la funzione ufnGetInventoryStock per conoscere la quantità di scorte corrente dei prodotti il cui valore ProductModelID è compreso tra 75 e 80.The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply  
FROM Production.Product  
WHERE ProductModelID BETWEEN 75 and 80;  

Nota

Per altre informazioni ed esempi di funzioni scalari, vedere CREATE FUNCTION (Transact-SQL).For more information and examples of scalar functions, see CREATE FUNCTION (Transact-SQL).

Funzioni con valori di tabellaTable-Valued Functions

Nell'esempio seguente viene creata una funzione inline con valori di tabella nel database AdventureWorks2012AdventureWorks2012.The following example creates an inline table-valued function (TVF) in the AdventureWorks2012AdventureWorks2012 database. La funzione accetta un parametro di input, un ID (punto vendita) cliente e restituisce le colonne ProductID, Namee l'aggregazione delle vendite per l'anno in corso come valore YTD Total per ogni prodotto venduto al punto vendita.The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL  
    DROP FUNCTION Sales.ufn_SalesByStore;  
GO  
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'  
    FROM Production.Product AS P   
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID  
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID  
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID  
    WHERE C.StoreID = @storeid  
    GROUP BY P.ProductID, P.Name  
);  

Nell'esempio seguente viene richiamata la funzione e viene specificato l'ID cliente 602.The following example invokes the function and specifies customer ID 602.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Nell'esempio seguente viene creata una funzione con valori di tabella con istruzioni multiple nel database AdventureWorks2012AdventureWorks2012.The following example creates a multi-statement table-valued function (MSTVF) in the AdventureWorks2012AdventureWorks2012 database. Nella funzione viene accettato un solo parametro di input, un valore EmployeeID , e tramite essa viene restituito un elenco di tutti i dipendenti che fanno riferimento direttamente o indirettamente al dipendente specificato.The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. La funzione viene quindi richiamata specificando l'ID dipendente 109.The function is then invoked specifying employee ID 109.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL  
    DROP FUNCTION dbo.ufn_FindReports;  
GO  
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE   
(  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set that lists all the employees who report to the   
--specific employee directly or indirectly.*/  
AS  
BEGIN  
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
    AS (  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n  
        FROM HumanResources.Employee e   
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        WHERE e.BusinessEntityID = @InEmpID  
        UNION ALL  
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor  
        FROM HumanResources.Employee e   
            INNER JOIN EMP_cte  
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
INNER JOIN Person.Person p   
ON p.BusinessEntityID = e.BusinessEntityID  
        )  
-- copy the required columns to the result of the function   
   INSERT @retFindReports  
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
   FROM EMP_cte   
   RETURN  
END;  
GO  

Nell'esempio seguente viene richiamata la funzione e viene specificato l'ID dipendente 1.The following example invokes the function and specifies employee ID 1.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);  

Nota

Per altre informazioni ed esempi sulle funzioni inline con valori di tabella e sulle funzioni con valori di tabella con istruzioni multiple, vedere CREATE FUNCTION (Transact-SQL).For more information and examples of inline table-valued functions (inline TVFs) and multi-statement table-valued functions (MSTVFs), see CREATE FUNCTION (Transact-SQL).

Procedure consigliateBest Practices

Se una funzione definita dall'utente non viene creata tramite la clausola SCHEMABINDING, le modifiche apportate agli oggetti sottostanti possono influire sulla definizione della funzione e generare risultati imprevisti quando viene richiamata.If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. È consigliabile implementare uno dei metodi seguenti per assicurarsi che la funzione non diventi obsoleta in seguito a modifiche degli oggetti sottostanti:We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

  • Specificare la clausola WITH SCHEMABINDING quando si crea la funzione definita dall'utente.Specify the WITH SCHEMABINDING clause when you are creating the UDF. In questo modo, gli oggetti a cui si fa riferimento nella definizione della funzione possono essere modificati solo se viene modificata anche la funzione.This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

  • Eseguire la stored procedure sp_refreshsqlmodule dopo avere modificato qualsiasi oggetto specificato nella definizione della funzione definita dall'utente.Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

Se si crea una funzione definita dall'utente che non accede ai dati, specificare l'opzione SCHEMABINDING.If creating a UDF that does not access data, specify the SCHEMABINDING option. In questo modo Query Optimizer non genererà operatori di spool inutili per piani di query che interessano queste funzioni.This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs. Per altre informazioni sugli spool, vedere Guida di riferimento a operatori Showplan logici e fisici.For more information on spools, see Showplan Logical and Physical Operators Reference. Per altre informazioni sulla creazione di una funzione associata a uno schema, vedere Funzioni associate a schema.For more information on creating a schema bound function, see Schema-bound functions.

La creazione di un join di una funzione con valori di tabella con istruzioni multiple in una clausola FROM è possibile, ma può compromettere le prestazioni.Joining to an MSTVF in a FROM clause is possible, but can result in poor performance. In SQL ServerSQL Server non è possibile usare tutte le tecniche ottimizzate con alcune istruzioni che possono essere incluse in una funzione con valori di tabella con istruzioni multiple, comportando un piano di query non ottimale.SQL ServerSQL Server is unable to use all the optimized techniques on some statements that can be included in a MSTVF, resulting in a suboptimal query plan. Per ottenere le migliori prestazioni possibili, utilizzare i join tra le tabelle di base anziché tra le funzioni, quando possibile.To obtain the best possible performance, whenever possible use joins between base tables instead of functions.

Importante

Le funzioni con valori di tabella con istruzioni multiple hanno una stima di cardinalità predefinita pari a 100 a partire da SQL Server 2014 (12.x)SQL Server 2014 (12.x) e pari a 1 nelle versioni di SQL ServerSQL Server precedenti.MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions.
A partire da SQL Server 2017 (14.x)SQL Server 2017 (14.x), è possibile ottimizzazione un piano di esecuzione che usa le funzioni con valori di tabella con istruzioni multiple per sfruttare l'esecuzione interleaved. In questo modo si usa la cardinalità effettiva anziché i valori euristici specificati in precedenza.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), optimizing an execution plan that uses MSTVFs can leverage interleaved execution, which results in using actual cardinality instead of the above heuristics.
Per altre informazioni, vedere Esecuzione interleaved per funzioni con valori di tabella a più istruzioni.For more information, see Interleaved execution for multi-statement table valued functions.

Nota

ANSI_WARNINGS non viene applicata quando vengono passati parametri a una stored procedure, una funzione definita dall'utente oppure in caso di dichiarazione e impostazione delle variabili in un'istruzione batch.ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. Se, ad esempio, la variabile viene definita come char(3) e quindi impostata su un valore maggiore di tre caratteri, i dati verranno troncati alle dimensioni definite e l'istruzione INSERT o UPDATE avrà esito positivo.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Vedere ancheSee Also

Funzioni definite dall'utente User-Defined Functions
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER FUNCTION (Transact-SQL) ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL) DROP PARTITION FUNCTION (Transact-SQL)
Altri esempi nella communityMore examples in the community