Criar funções definidas pelo utilizador

Concluído

As funções definidas pelo usuário (UDF) são semelhantes aos procedimentos armazenados, pois são armazenadas separadamente das tabelas no banco de dados. Essas funções aceitam parâmetros, executam uma ação e, em seguida, retornam o resultado da ação como um único valor (escalar) ou um conjunto de resultados (valor de tabela). Em seguida, você pode usar a função no lugar de uma tabela ao escrever uma instrução SELECT. As funções definidas pelo usuário destinam-se a executar cálculos e usar esse resultado em outra instrução. Considerando que os procedimentos armazenados podem encapsular a função e a instrução e até mesmo modificar dados dentro do banco de dados.

Analisaremos três tipos de funções definidas pelo usuário. Para obter mais detalhes sobre as diferentes funções, consulte a documentação de referência do T-SQL.

Funções com valor de tabela integradas

As funções com valor de tabela embutido (TVF) são a função mais simples criada com base em uma instrução SELECT e são a escolha preferida para desempenho.

No exemplo a seguir, uma função com valor de tabela é criada com um parâmetro de entrada para preço unitário.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

Quando a função com valor de tabela é executada com um valor para o parâmetro, todos os produtos com um preço unitário superior a esse valor serão retornados.

O código a seguir usa a função com valor de tabela no lugar de uma tabela.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

Funções valorizadas por tabelas de múltiplas instruções

Ao contrário do TVF embutido, uma função com valor de tabela de várias instruções (MSTVF) pode ter mais de uma instrução e tem requisitos de sintaxe diferentes.

Observe como no código a seguir, usamos um BEGIN/END além de RETURN:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

Uma vez criado, você faz referência ao MSTVF no lugar de uma tabela, assim como com a função embutida anterior acima. Você também pode fazer referência à saída na cláusula FROM e juntá-la a outras tabelas.

SELECT *
FROM Sales.mstvf_OrderStatus();

Considerações sobre desempenho

O Otimizador de Consulta não consegue estimar quantas linhas serão retornadas por uma função valorizada por tabela de múltiplas instruções, mas consegue com a função valorizada por tabela em linha. Portanto, use o TVF em linha quando possível para um melhor desempenho. Se você não precisa unir o MSTVF com outras tabelas e/ou sabe que o resultado será apenas algumas linhas, o impacto no desempenho não é tão preocupante. Se você espera um grande conjunto de resultados e precisa se unir a outras tabelas, considere usar uma tabela temporária para armazenar os resultados e, em seguida, ingressar na tabela temporária.

No SQL Server versões 2017 e superiores, a Microsoft introduziu recursos para processamento inteligente de consultas para melhorar o desempenho do MSTVF. Veja mais detalhes sobre os recursos de Processamento Inteligente de Consultas na Documentação de Referência do T-SQL.

Funções escalares definidas pelo usuário

Uma função escalar definida pelo usuário retorna apenas um valor, ao contrário das funções com valor de tabela e, portanto, é frequentemente usada para instruções simples e frequentes.

Aqui está um exemplo para obter o preço de tabela de produtos para um produto específico em um determinado dia:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

Para esta função, ambos os parâmetros devem ser fornecidos para obter o valor. Dependendo da função, você pode listar a função na instrução SELECT em uma consulta mais complexa.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Vincular função a objetos referenciados

SCHEMABINDING é opcional ao criar a função. Quando você especifica SCHEMABINDING, ele vincula a função aos objetos referenciados e, em seguida, os objetos não podem ser modificados sem também modificar a função. A função deve primeiro ser modificada ou descartada para remover dependências antes de modificar o objeto.

SCHEMABINDING é removido se ocorrer qualquer uma das seguintes situações:

• A função é descartada
• A função é modificada com a instrução ALTER sem especificar SCHEMABINDING