Funções definidas pelo usuário

Artigo
05/23/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Ponto de extremidade de análises SQL no Microsoft Fabric Warehouse no Microsoft Fabric

Assim como as funções em linguagens de programação, as funções do SQL Server definidas pelo usuário são rotinas que aceitam parâmetros, executam uma ação como um cálculo complexo e retornam o resultado dessa ação como um valor. O valor de retorno pode ser um único valor escalar ou um conjunto de resultados.

Benefícios de funções definidas pelo usuário

Por que usar UDFs (funções definidas pelo usuário)?

Programação modular. Você pode criar a função uma vez, armazená-la no banco de dados e chamá-la quantas vezes quiser em seu programa. Funções definidas pelo usuário podem ser modificadas independentemente do código-fonte do programa.
Execução mais rápida. Semelhantemente aos procedimentos armazenados, as funções Transact-SQL definidas pelo usuário reduzem o custo de compilação do código Transact-SQL colocando os planos em cache e reusando-os para execuções repetidas. Isso significa que a função definida pelo usuário não precisa ser analisada e otimizada novamente em cada utilização, resultando em tempos de execução mais rápidos.

As funções CLR oferecem uma vantagem de desempenho significativa sobre funções Transact-SQL para tarefas de computação, manipulação de cadeias de caracteres e lógica de negócios. As funções Transact-SQL são mais adequadas à lógica intensiva de acesso a dados.
Reduzir o tráfego de rede. Uma operação que filtra dados com base em alguma restrição complexa que não pode ser expressa em uma única expressão escalar pode ser expressa como uma função. Em seguida, a função pode ser invocada na cláusula WHERE para reduzir o número de linhas enviadas ao cliente.

Importante

As UDFs Transact-SQL em consultas só podem ser executadas em um único thread (plano de execução serial). Portanto, usar UDFs inibe o processamento paralelo de consultas. Para obter mais informações sobre o processamento paralelo de consultas, confira o Guia de arquitetura de processamento de consultas.

Tipos de funções

Funções escalares

As funções escalares definidas pelo usuário retornam um valor único de dados do tipo definido na cláusula RETURNS. Para uma função escalar embutida, o valor escalar retornado é o resultado de uma única instrução. Para uma função escalar de várias instruções, o corpo da função pode conter uma série de instruções Transact-SQL, que retornam o valor único. O tipo de retorno pode ser qualquer tipo de dados, exceto text, ntext, image, cursore timestamp. Para ver exemplos, confira Criar funções definidas pelo usuário (mecanismo de banco de dados).

Funções com valor de tabela

As TVFs (funções com valor de tabela) definidas pelo usuário retornam um tipo de dados de tabela. Para uma função com valor de tabela embutida, não há um corpo de função; a tabela é o conjunto de resultados de uma única instrução SELECT. Para ver exemplos, confira Criar funções definidas pelo usuário (mecanismo de banco de dados).

Funções do sistema

O SQL Server fornece muitas funções de sistema que você pode usar para executar várias operações. Elas não podem ser modificadas. Para obter mais informações, confira Funções internas (Transact-SQL), Funções armazenadas do sistema (Transact-SQL) e Exibições e funções de gerenciamento dinâmico (Transact-SQL).

Diretrizes

Erros de Transact-SQL que levam ao cancelamento de uma instrução e continuam com a instrução seguinte no módulo (como gatilhos ou procedimentos armazenados) tratados de modo diferente em uma função. Nas funções, esses erros fazem com que a execução da função seja interrompida. Em troca, isso faz com que a instrução que chamou a função seja cancelada.

As instruções em um bloco BEGIN...END não podem ter nenhum efeito colateral. Os efeitos colaterais da função são as alterações permanentes realizada no estado de um recurso que tem um escopo fora da função como uma modificação em uma tabela do banco de dados. As únicas alterações que podem ser feitas pelas instruções na função são alterações em objetos locais à função, como cursores ou variáveis locais. As modificações em tabelas de banco de dados, operações em cursores que não são locais à função, envio de email, tentativa de modificação em catálogo e geração de um conjunto de resultados retornados ao usuário são exemplos de ações que não devem ser realizadas em uma função.

Se uma instrução CREATE FUNCTION produzir efeitos colaterais com base nos recursos que não existem quando a instrução CREATE FUNCTION for emitida, o SQL Server executará a instrução. Porém, o SQL Server não executa a função quando é chamada.

O número de vezes em que uma função especificada em uma consulta é executada pode variar entre os planos de execução desenvolvidos pelo otimizador. Um exemplo é a função chamada por uma subconsulta em uma cláusula WHERE. O número de vezes em que a subconsulta e sua função são executadas pode variar com os caminhos de acesso diferentes escolhidos pelo otimizador.

As funções determinísticas devem estar associadas ao esquema. Use a cláusula SCHEMABINDING ao criar uma função determinística.

Para obter mais considerações de desempenho e informações sobre funções definidas pelo usuário, confira Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Instruções válidas em uma função

Os tipos de instruções que são válidos em uma função incluem:

As instruções DECLARE podem ser usadas para definir variáveis de dados e cursores que são locais à função.
A atribuição de valores a objetos locais à função, como o uso de SET para atribuir valores para escalar e para as variáveis locais à tabela.
As operações de cursor que referenciam cursores locais são declaradas, abertas, fechadas e desalocadas na função. As instruções FETCH que retornam os dados aos clientes não são permitidas. Somente instruções FETCH que atribuem valores a variáveis locais usando a cláusula INTO são permitidas.
Instruções de controle de fluxo, exceto instruções TRY...CATCH.
Instruções SELECT com listas de seleção com expressões que atribuem valores às variáveis que são locais à função.
Instruções UPDATE, INSERT e DELETE que modificam variáveis de tabela locais à função.
Instruções EXECUTE que chamam um procedimento armazenado estendido.

Funções do sistema internas

As funções não determinísticas internas a seguir podem ser usadas nas funções definidas por usuário Transact-SQL.

CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@CONNECTIONS
@@CPU_BUSY
@@DBTS
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE

As funções não determinísticas internas a seguir não podem ser usadas nas funções Transact-SQL definidas pelo usuário.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Para obter uma lista das funções internas do sistema determinísticas e não determinísticas, consulte Funções determinísticas e não determinísticas.

Funções associadas a esquema

CREATE FUNCTION dá suporte a uma cláusula SCHEMABINDING que associa a função ao esquema de qualquer objeto que ela referencia, como tabelas, exibições e demais funções definidas pelo usuário. Uma tentativa para alterar ou descartar qualquer objeto referenciado por uma função associada a esquema falhará.

Essas condições devem ser cumpridas antes de especificar SCHEMABINDING em CREATE FUNCTION:

Todas as exibições e as funções definidas pelo usuário referenciadas pela função devem ser associadas a esquema.
Todos os objetos referenciados pela função devem estar no mesmo banco de dados da função. Os objetos devem ser referenciados usando nomes de uma única parte ou nomes de duas partes.
Você deve ter permissão REFERENCES em todos os objetos (tabelas, exibições e funções definidas pelo usuário) referenciados na função.

Você pode usar ALTER FUNCTION para remover a associação de esquema. A instrução ALTER FUNCTION deve redefinir a função sem especificar WITH SCHEMABINDING.

Especificar parâmetros

Uma função definida pelo usuário usa parâmetros de entrada zero ou mais e retorna um valor escalar ou uma tabela. A função pode ter um máximo de 1024 parâmetros de entrada. Quando um parâmetro da função tiver um valor padrão, a palavra-chave DEFAULT deve ser especificada quando a função for chamada para obter o valor padrão. Esse comportamento é diferente dos parâmetros com valores padrão nos procedimentos armazenados definidos pelo usuário nos quais a omissão de parâmetro também implica o valor padrão. Funções definidas pelo usuário não dão suporte aos parâmetros de saída.