CREATE FUNCTION (Transact-SQL)

Artigo
04/27/2024

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Cria uma função definida pelo usuário (UDF), que é uma rotina Transact-SQL ou CLR (Common Language Runtime). Uma função definida pelo usuário aceita parâmetros, executa uma ação como um cálculo complexo e retorna o resultado dessa ação como um valor. O valor de retorno pode ser um valor escalar (único) ou uma tabela. Use essa instrução para criar uma rotina reutilizável que possa ser usada destas maneiras:

Em instruções Transact-SQL, como SELECT
Em aplicativos que chamam a função
Na definição de outra função definida pelo usuário
Para parametrizar uma exibição ou aprimorar a funcionalidade de uma exibição indexada
Para definir uma coluna em uma tabela
Para definir uma CHECK restrição em uma coluna
Para substituir um procedimento armazenado
Usar uma função embutida como um predicado de filtro para uma política de segurança

A integração do CLR do .NET Framework ao SQL Server é discutida neste artigo. A integração CLR não se aplica ao Banco de Dados SQL do Azure.

No Azure Synapse Analytics ou Microsoft Fabric, confira CREATE FUNCTION (Azure Synapse Analytics e Microsoft Fabric).

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para funções escalares Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Sintaxe para funções com valor de tabela embutida Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Sintaxe para funções com valor de tabela de várias instruções Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Sintaxe para cláusulas de função Transact-SQL.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

Sintaxe para funções escalares CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintaxe para funções com valor de tabela CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintaxe para cláusulas de função CLR.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

Sintaxe OLTP na memória para funções escalares definidas pelo usuário compiladas nativamente.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

OR ALTER

Aplica-se a: SQL Server 2016 (13.x) SP 1 e versões posteriores e Banco de Dados SQL do Azure.

Altera condicionalmente a função somente se ela já existe.

A sintaxe opcional OR ALTER está disponível para CLR, começando com o SQL Server 2016 (13.x) SP 1 1.

schema_name

O nome do esquema ao qual a função definida pelo usuário pertence.

function_name

O nome da função definida pelo usuário. Os nomes de funções devem obedecer às regras de identificadores e devem ser exclusivos dentro do banco de dados e em seu esquema.

Parênteses são necessários após o nome da função, mesmo se um parâmetro não for especificado.

@parameter_name

Um parâmetro na função definida pelo usuário. Podem ser declarados um ou mais parâmetros.

Uma função pode ter no máximo 2.100 parâmetros. O valor de cada parâmetro declarado deve ser fornecido pelo usuário quando a função é executada, a menos que seja definido um padrão para o parâmetro.

Especifique um nome de parâmetro usando um sinal de arroba (@) como o primeiro caractere. O nome do parâmetro deve estar em conformidade com as regras de identificadores. Os parâmetros são locais para a função. Os mesmos nomes de parâmetro podem ser usados em outras funções. Os parâmetros podem tomar o lugar apenas de constantes; eles não podem ser usados em vez de nomes de tabela, nomes de coluna ou nomes de outros objetos de banco de dados.

ANSI_WARNINGS não é respeitado quando você passa parâmetros em um procedimento armazenado, função definida pelo usuário ou quando declara e define variáveis em uma instrução em lote. Por exemplo, se a variável for definida como char(3) e, em seguida, configurada com um valor maior que três caracteres, os dados serão truncados até o tamanho definido e a instrução INSERT ou UPDATE terá êxito.

[ type_schema_name. ] parameter_data_type

O tipo de dados do parâmetro e, opcionalmente, o esquema ao qual ele pertence. Para funções Transact-SQL, todos os tipos de dados são permitidos, incluindo tipos CLR e tipos de tabela definidos pelo usuário, com exceção do tipo de dados timestamp. Para funções CLR, todos os tipos de dados, incluindo tipos definidos pelo usuário CLR, são permitidos, exceto texto, ntext, imagem, tipos de tabela definidos pelo usuário e tipos de dados de carimbo de data/hora. Os tipos não escalares, cursor e tabela, não podem ser especificados como um tipo de dados de parâmetro nas funções Transact-SQL ou CLR.

Se type_schema_name não for especificado, o Mecanismo de Banco de Dados procurará o scalar_parameter_data_type na seguinte ordem:

O esquema que contém os nomes dos tipos de dados do sistema SQL Server.
O esquema padrão do usuário atual no banco de dados atual.
O esquema dbo no banco de dados atual.

[ = padrão ]

Um valor padrão para o parâmetro. Se um valor default for definido, a função poderá ser executada sem a necessidade de especificar um valor para esse parâmetro.

Os valores de parâmetro padrão podem ser especificados para funções CLR, exceto para os tipos de dados varchar(max) e varbinary(max).

Quando um parâmetro da função tem um valor padrão, a palavra-chave DEFAULT deve ser especificada quando a função é chamada para recuperar o valor padrão. Esse comportamento é diferente do uso de parâmetros com valores padrão em procedimentos armazenados nos quais a omissão do parâmetro também indica o valor padrão. No entanto, a DEFAULT palavra-chave não é necessária ao invocar uma função escalar usando a EXECUTE instrução.

READONLY

Indica que o parâmetro não pode ser atualizado ou modificado dentro da definição da função. READONLY é necessário para parâmetros de tipo de tabela definidos pelo usuário (TVPs) e não pode ser usado para qualquer outro tipo de parâmetro.

return_data_type

O valor de retorno de uma função escalar definida pelo usuário. Para funções Transact-SQL, todos os tipos de dados são permitidos, incluindo tipos CLR definidos pelo usuário, com exceção do tipo de dados timestamp. Para funções CLR, todos os tipos de dados são permitidos, incluindo tipos CLR definidos pelo usuário, com exceção dos tipos de dados text, ntext, image e timestamp. Os tipos não escalares, cursor e tabela, não podem ser especificados como um tipo de dados de retorno nas funções Transact-SQL ou CLR.

function_body

Especifica que uma série de instruções Transact-SQL, que juntas não produzem um efeito colateral, como modificar uma tabela, definem o valor da função. function_body é usado somente em funções escalares e funções com valor de tabela de várias instruções (MSTVFs).

Em funções escalares, function_body é uma série de instruções Transact-SQL que juntas são avaliadas para um valor escalar.

Em MSTVFs, function_body é uma série de instruções Transact-SQL que preenchem uma TABLE variável de retorno.

scalar_expression

Especifica o valor escalar que a função escalar retorna.

TABLE

Especifica que o valor retornado da TVF (função com valor de tabela) é uma tabela. Somente constantes e @local_variables podem ser passadas para TVFs.

Em TVFs embutidos, o valor de TABLE retorno é definido por meio de uma única SELECT instrução. As funções embutidas não têm variáveis de retorno associadas.

Em MSTVFs, @return_variable é uma TABLE variável, usada para armazenar e acumular as linhas que devem ser retornadas como o valor da função. @return_variable pode ser especificado somente para funções Transact-SQL e não para funções CLR.

select_stmt

A instrução única SELECT que define o valor de retorno de uma função com valor de tabela embutida (TVF).

ORDER (<order_clause>)

Especifica a ordem na qual os resultados estão sendo retornados da função com valor de tabela. Para obter mais informações, consulte a seção Usar ordem de classificação em funções com valor de tabela CLR, mais adiante neste artigo.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

Aplica-se a: SQL Server 2008 (10.0.x) SP 1 e versões posteriores.

Especifica o assembly e o método ao qual o nome da função criado deve referir-se.

assembly_name – deve corresponder a um valor na coluna name de SELECT * FROM sys.assemblies;.

O nome que foi usado na CREATE ASSEMBLY declaração.
class_name – deve corresponder a um valor na coluna assembly_name de SELECT * FROM sys.assembly_modules;.

Muitas vezes o valor contém um ponto incorporado ou ponto. Nesses casos, a sintaxe Transact-SQL requer que o valor seja limitado com um par de colchetes ([]) ou com um par de aspas duplas ("").
method_name – deve corresponder a um valor na coluna method_name de SELECT * FROM sys.assembly_modules;.

O método deve ser estático.

Em um exemplo típico de MyFood.dll, no qual todos os tipos estão no MyFood namespace, o EXTERNAL NAME valor pode ser MyFood.[MyFood.MyClass].MyStaticMethod.

Por padrão, o SQL Server não pode executar código CLR. Você pode criar, modificar e descartar objetos de banco de dados que fazem referência a módulos de tempo de execução de linguagem comum. No entanto, você não pode executar essas referências no SQL Server até habilitar a opção clr enabled. Para habilitar essa opção, use sp_configure. Essa opção não está disponível em um banco de dados independente.

<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

Define o tipo de dados de tabela para uma função do Transact-SQL. A declaração da tabela inclui definições de coluna e restrições de coluna ou tabela. A tabela sempre é colocada no grupo de arquivos primário.

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

Aplica-se a: SQL Server 2008 (10.0.x) SP 1 e versões posteriores e Banco de Dados SQL do Azure (Visualização em algumas regiões).

Define os tipos de dados de tabela para uma função CLR. A declaração de tabela inclui somente nomes de colunas e tipos de dados. A tabela sempre é colocada no grupo de arquivos primário.

NULL | NOT NULL

Compatível apenas com funções escalares definidas pelo usuário compiladas nativamente. Para obter mais informações, consulte Funções escalares definidas pelo usuário para OLTP in-memory.

NATIVE_COMPILATION

Indica se uma função definida pelo usuário é compilada nativamente. Esse argumento é obrigatório para funções escalares definidas pelo usuário compiladas nativamente.

BEGIN ATOMIC WITH

Necessário e suportado apenas para funções escalares definidas pelo usuário compiladas nativamente. Para obter mais informações, consulte Blocos atômicos em procedimentos nativos.

SCHEMABINDING

O SCHEMABINDING argumento é necessário para funções escalares definidas pelo usuário compiladas nativamente.

EXECUTE AS

EXECUTE AS é necessário para funções escalares definidas pelo usuário compiladas nativamente.

<> function_option ::= e <clr_function_option> ::=

Especifica que a função tem uma ou mais das seguintes opções.

ENCRYPTION

Aplica-se a: SQL Server 2008 (10.0.x) SP 1 e versões posteriores.

Indica que o Mecanismo de Banco de Dados converte o CREATE FUNCTION texto original da instrução em um formato ofuscado. A saída da ofuscação não é diretamente visível em nenhuma exibição de catálogo. Os usuários que não têm acesso a tabelas do sistema ou arquivos de banco de dados não podem recuperar o texto ofuscado. No entanto, o texto está disponível para usuários privilegiados que podem acessar tabelas do sistema pela conexão de diagnóstico para administradores de banco de dados ou acessar diretamente os arquivos de banco de dados. Além disso, os usuários que podem anexar um depurador ao processo de servidor também podem recuperar o procedimento original da memória em runtime. Para obter mais informações sobre como acessar metadados do sistema, consulte Configuração de visibilidade de metadados.

O uso dessa opção impede que a função seja publicada como parte da replicação do SQL Server. Essa opção não pode ser especificada para funções CLR.

SCHEMABINDING

Especifica que a função está associada aos objetos de banco de dados referenciados por ela. Quando SCHEMABINDING especificado, os objetos base não podem ser modificados de uma forma que afete a definição da função. A própria definição da função deve ser primeiro modificada ou descartada para remover as dependências no objeto a ser modificado.

A associação da função aos objetos referenciados por ela será removida somente quando ocorrer uma das seguintes ações:

A função for descartada.
A função é modificada usando a instrução ALTER com a opção SCHEMABINDING não especificada.

Uma função poderá ser associada a esquemas apenas se as condições a seguir forem verdadeiras:

A função é uma função Transact-SQL.
As exibições e funções definidas pelo usuário referenciadas pela função também são associadas a esquema.
Os objetos referenciados pela função são referenciados com um nome de duas partes.
A função e os objetos aos quais ela faz referência pertencem ao mesmo banco de dados.
O usuário que executou a instrução CREATE FUNCTION tem permissão REFERENCES nos objetos de banco de dados aos quais a função faz referência.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Especifica o OnNULLCall atributo de uma função escalar. Se não for especificado, CALLED ON NULL INPUT está implícito por padrão. Em outras palavras, o corpo da função é executado mesmo que NULL seja passado como um argumento.

Se RETURNS NULL ON NULL INPUT for especificado em uma função CLR, isso indica que o SQL Server pode retornar NULL quando qualquer um dos argumentos que ele recebe é NULL, sem realmente invocar o corpo da função. Se o método de uma função CLR especificada em <method_specifier> já tiver um atributo personalizado que indica RETURNS NULL ON NULL INPUT, mas a CREATE FUNCTION instrução indica CALLED ON NULL INPUT, a CREATE FUNCTION instrução terá precedência. O OnNULLCall atributo não pode ser especificado para funções com valor de tabela CLR.

EXECUTE AS

Especifica o contexto de segurança sob o qual a função definida pelo usuário é executada. Portanto, é possível controlar qual conta de usuário o SQL Server usa para validar permissões em quaisquer objetos do banco de dados referidos pela função.

EXECUTE AS não pode ser especificado para funções com valor de tabela embutida.

Para obter mais informações, confira Cláusula EXECUTE AS (Transact-SQL).

INLINE = { ON | OFF }

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores e Banco de Dados SQL do Azure.

Especifica se este UDF escalar deve ser embutido ou não. Essa cláusula só se aplica a funções escalares definidas pelo usuário. A cláusula INLINE não é obrigatória. Se a INLINE cláusula não for especificada, ela será definida automaticamente como ON ou OFF com base em se o UDF é inlineable. Se INLINE = ON for especificado, mas o UDF for encontrado como não inlineable, um erro será lançado. Para saber mais, confira Scalar UDF Inlining (Embutimento de UDF escalar).

<> column_definition ::=

Define o tipo de dados da tabela. A declaração da tabela inclui definições de coluna e restrições. Para funções CLR, apenas column_name e data_type podem ser especificados.

column_name

O nome de uma coluna na tabela. Os nomes de coluna devem estar em conformidade com as regras de identificadores e devem ser exclusivos na tabela. column_name pode consistir em 1 a 128 caracteres.

data_type

Especifica o tipo de dados da coluna. Para funções Transact-SQL, todos os tipos de dados são permitidos, incluindo tipos de dados CLR definidos pelo usuário, com exceção de timestamp. Para funções CLR, todos os tipos de dados, incluindo tipos definidos pelo usuário CLR, são permitidos, exceto text, ntext, image, char, varchar, varchar(max) e timestamp. O cursor de tipo não escalar não pode ser especificado como um tipo de dados de coluna nas funções Transact-SQL ou CLR.

DEFAULT constant_expression

Especifica o valor fornecido para a coluna quando um valor não for fornecido explicitamente durante uma inserção. constant_expression é uma constante, NULLou um valor de função do sistema. DEFAULT As definições podem ser aplicadas a qualquer coluna, exceto aquelas que têm a IDENTITY propriedade. DEFAULT não pode ser especificado para funções com valor de tabela CLR.

COLLATE collation_name

Especifica a ordenação da coluna. Se não for especificado, à coluna será atribuída a ordenação padrão do banco de dados. O nome da ordenação pode ser um nome de ordenação do Windows ou um nome de ordenação SQL. Para obter uma lista e mais informações sobre ordenações, confira Nome de ordenação do Windows (Transact-SQL) e Nome de ordenação do SQL Server (Transact-SQL).

A COLLATE cláusula pode ser usada para alterar os agrupamentos somente de colunas dos tipos de dados char, varchar, nchar e nvarchar . COLLATE não pode ser especificado para funções com valor de tabela CLR.

ROWGUIDCOL

Indica que a nova coluna é uma de coluna de identificador globalmente exclusivo de linha. Apenas uma coluna uniqueidentifier por tabela pode ser designada como coluna ROWGUIDCOL . A ROWGUIDCOL propriedade pode ser atribuída somente a uma coluna uniqueidentifier .

A ROWGUIDCOL propriedade não impõe a exclusividade dos valores armazenados na coluna. Ele também não gera automaticamente valores para novas linhas inseridas na tabela. Para gerar valores exclusivos para cada coluna, use a NEWID função em INSERT instruções. Um valor padrão pode ser especificado; no entanto, NEWID não pode ser especificado como padrão.

IDENTITY

Indica que a nova coluna é uma coluna de identidade. Quando uma nova linha é adicionada à tabela, o SQL Server fornece um valor incremental exclusivo para a coluna. As colunas de identidade são normalmente usadas junto com PRIMARY KEY restrições para servir como o identificador de linha exclusivo para a tabela. A propriedade IDENTITY pode ser atribuída às colunas tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Apenas uma coluna de identidade pode ser criada por tabela. Padrões e DEFAULT restrições vinculados não podem ser usados com uma coluna de identidade. Você deve especificar seed e increment ou nenhum dos dois. Se nenhum for especificado, o padrão será (1,1).

IDENTITY não pode ser especificado para funções com valor de tabela CLR.

seed

O valor inteiro a ser atribuído à primeira linha da tabela.

increment

O valor inteiro a ser adicionado ao valor de propagação para linhas sucessivas na tabela.

<> column_constraint ::= e <table_constraint> ::=

Define a restrição para uma coluna ou tabela especificada. Para funções CLR, o único tipo de restrição permitido é NULL. Restrições nomeadas não são permitidas.

NULL | NOT NULL

Determina se são permitidos valores nulos na coluna. NULL não é estritamente uma restrição, mas pode ser especificado como NOT NULL. NOT NULL não pode ser especificado para funções com valor de tabela CLR.

PRIMARY KEY

Uma restrição que impõe a integridade da entidade para uma coluna especificada por meio de um índice exclusivo. Em funções definidas pelo usuário com valor de tabela, a PRIMARY KEY restrição pode ser criada em apenas uma coluna por tabela. PRIMARY KEY não pode ser especificado para funções com valor de tabela CLR.

UNIQUE

É uma restrição que fornece integridade de entidade a uma coluna ou a colunas especificadas por meio de um índice exclusivo. Uma tabela pode ter várias UNIQUE restrições. UNIQUE não pode ser especificado para funções com valor de tabela CLR.

CLUSTERED | NONCLUSTERED

Indica que um índice clusterizado ou não clusterizado é criado para a PRIMARY KEY restrição ou UNIQUE . PRIMARY KEY uso de CLUSTEREDrestrições e UNIQUE uso de NONCLUSTEREDrestrições .

CLUSTERED pode ser especificado para apenas uma restrição. Se CLUSTERED for especificado para uma UNIQUE restrição e uma PRIMARY KEY restrição também for especificada, o PRIMARY KEY usa NONCLUSTERED.

CLUSTERED e NONCLUSTERED não pode ser especificado para funções com valor de tabela CLR.

CHECK

Uma restrição que impõe integridade de domínio limitando os possíveis valores que podem ser inseridos em uma ou mais colunas. CHECK restrições não podem ser especificadas para funções com valor de tabela CLR.

logical_expression

Uma expressão lógica que retorna TRUE ou FALSE.

<> computed_column_definition ::=

Especifica uma coluna computada. Para obter mais informações sobre colunas computadas, confira CREATE TABLE (Transact-SQL).

column_name

O nome da coluna computada.

computed_column_expression

É uma expressão que define o valor de uma coluna computada.

<index_option> ::=

Especifica as opções de índice para o PRIMARY KEY índice ou UNIQUE . Para obter mais informações sobre opções de índice, confira CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

Especifica o preenchimento do índice. O padrão é OFF.

FILLFACTOR = fillfactor

Especifica uma porcentagem que indica quanto o Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou alteração do índice. fillfactor deve ser um valor inteiro de 1 a 100. O padrão é 0.

IGNORE_DUP_KEY = { ON | OFF }

Especifica a resposta de erro quando uma operação de inserção tenta inserir valores da chave duplicada em um índice exclusivo. A opção IGNORE_DUP_KEY aplica-se apenas a operações de inserção depois que o índice é criado ou recompilado. O padrão é OFF.

STATISTICS_NORECOMPUTE = { ON | OFF}

Especifica se as estatísticas de distribuição são recomputadas. O padrão é OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

Especifica se bloqueios de linha são permitidos. O padrão é ON.

ALLOW_PAGE_LOCKS = { ON | OFF }

Especifica se bloqueios de página são permitidos. O padrão é ON.

Práticas recomendadas

Se uma função definida pelo usuário não for criada com a cláusula, as SCHEMABINDING alterações feitas nos objetos subjacentes poderão afetar a definição da função e produzir resultados inesperados quando ela for invocada. É recomendável que você implemente um dos seguintes métodos para garantir que a função não se torne desatualizada devido a alterações em seus objetos subjacentes:

Especifique a WITH SCHEMABINDING cláusula ao criar a função. Essa opção garante que os objetos referenciados na definição de função não possam ser modificados, a menos que a função também seja modificada.
Execute o procedimento armazenado sp_refreshsqlmodule depois de modificar um objeto especificado na definição da função.

Para obter mais informações e considerações de desempenho sobre funções com valor de tabela embutida (TVFs embutidas) e funções com valor de tabela de várias instruções (MSTVFs), consulte Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Tipos de dados

Se forem especificados parâmetros em uma função CLR, eles deverão ser tipos SQL Server, conforme definido previamente para scalar_parameter_data_type. Para obter mais informações comparando tipos de dados do sistema do SQL Server com tipos de dados de integração CLR ou tipos de dados do .NET Framework common language runtime, consulte Mapeando dados de parâmetro CLR.

Para que o SQL Server faça referência ao método correto quando ele está sobrecarregado em uma classe, o método indicado em <method_specifier> deve ter as seguintes características:

Receba o mesmo número de parâmetros especificado em [ , ...n ].
Receber todos os parâmetros por valor, não por referência.
Use tipos de parâmetro compatíveis com tipos especificados na função SQL Server.

Se o tipo de dados de retorno da função CLR especificar um tipo de tabela (RETURNS TABLE), o tipo de dados de retorno do método em <method_specifier> deve ser do tipo IEnumerator ou IEnumerable, e pressupõe que a interface é implementada pelo criador da função. Ao contrário das funções Transact-SQL, as funções CLR não podem incluir PRIMARY KEY, UNIQUEou CHECK restrições no <table_type_definition>. Os tipos de dados de colunas especificados em <table_type_definition> devem corresponder aos tipos das colunas correspondentes do conjunto de resultados retornado pelo método no <method_specifier> em tempo de execução. Essa verificação de tipo não é executada no momento em que a função é criada.

Para obter mais informações sobre como programar funções CLR, consulte Funções CLR definidas pelo usuário.

Comentários

As funções escalares podem ser invocadas onde expressões escalares são usadas, o que inclui colunas computadas e CHECK definições de restrição. As funções escalares também podem ser executadas usando a instrução EXECUTE (Transact-SQL ). As funções escalares devem ser invocadas usando pelo menos o nome de duas partes da função (<schema>.<function>). Para obter mais informações sobre nomes de várias partes, confira Convenções da sintaxe Transact-SQL (Transact-SQL). Funções com valor de tabela podem ser invocadas em que as expressões de tabela são permitidas na cláusula FROM de instruções SELECT, INSERT, UPDATE ou DELETE. Para obter mais informações, consulte Executar funções definidas pelo usuário.

Interoperabilidade

As instruções a seguir são válidas em uma função:

Instruções de atribuição.
Instruções de controle de fluxo, exceto instruções TRY...CATCH.
Instruções DECLARE que definem variáveis de dados locais e cursores locais.
Instruções SELECT que contêm listas de seleção com expressões que atribuem valores a variáveis locais.
Operações de cursor que fazem referência a cursores locais que são declaradas, abertas, fechadas e desalocadas na função. Somente FETCH instruções que atribuem valores a variáveis locais usando a INTO cláusula são permitidas, FETCH instruções que retornam dados ao cliente não são permitidas.
Instruções INSERT, UPDATE e DELETE que modificam variáveis de tabela local.
Instruções EXECUTE que chamam procedimentos armazenados estendidos.

Para obter mais informações, consulte Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).

Interoperabilidade de colunas computadas

As funções têm as seguintes propriedades. Os valores dessas propriedades determinam se as funções podem ser usadas em colunas computadas que podem ser persistidas ou indexadas.

Propriedade	Descrição	Observações
`IsDeterministic`	A função é determinística ou não determinística.	O acesso a dados locais é permitido em funções determinísticas. Por exemplo, funções que sempre retornam o mesmo resultado sempre que são chamadas usando um conjunto específico de valores de entrada e com o mesmo estado do banco de dados seriam rotuladas como determinísticas.
`IsPrecise`	A função é precisa ou imprecisa.	As funções imprecisas contêm operações, como operações de ponto flutuante.
`IsSystemVerified`	As propriedades de precisão e determinismo da função podem ser verificadas pelo SQL Server.
`SystemDataAccess`	A função acessa dados do sistema (catálogos ou tabelas virtuais do sistema) na instância local do SQL Server.
`UserDataAccess`	A função acessa dados de usuário na instância local do SQL Server.	Inclui tabelas definidas pelo usuário e tabelas temporárias, mas não variáveis de tabela.

As propriedades de precisão e determinismo de funções Transact-SQL são automaticamente determinadas pelo SQL Server. O acesso a dados e as propriedades de determinismo de funções CLR podem ser especificadas pelo usuário. Para obter mais informações, consulte Integração CLR: atributos personalizados para rotinas CLR.

Para exibir os valores atuais dessas propriedades, use OBJECTPROPERTYEX (Transact-SQL).

Importante

Funções devem ser criadas com SCHEMABINDING para serem determinísticas.

Uma coluna computada que invoca uma função definida pelo usuário pode ser usada em um índice quando a função definida pelo usuário tem os seguintes valores de propriedades:

IsDeterministic é true
IsSystemVerified é true (a menos que a coluna computada seja persistente)
UserDataAccess é false
SystemDataAccess é false

Para obter mais informações, consulte Índices em colunas computadas.

Chamar procedimentos armazenados estendidos a partir de funções

O procedimento armazenado estendido, ao chamá-lo de dentro de uma função, não pode retornar conjuntos de resultados para o cliente. Quaisquer APIs ODS que retornam conjuntos de resultados para o cliente, retornam FAIL. O procedimento armazenado estendido pode se conectar novamente a uma instância do SQL Server; no entanto, ele não deve tentar ingressar na mesma transação que a função que invocou o procedimento armazenado estendido.

Semelhante às chamadas de um lote ou procedimento armazenado, o procedimento armazenado estendido é executado no contexto da conta de segurança do Windows sob a qual o SQL Server está sendo executado. O proprietário do procedimento armazenado deve considerar esse cenário ao conceder EXECUTE permissão sobre ele aos usuários.

Limitações

Funções definidas pelo usuário não podem ser usadas para executar ações que modificam o estado do banco de dados.

As funções definidas pelo usuário não podem conter uma cláusula OUTPUT INTO que tenha uma tabela como seu destino.

As seguintes instruções do Service Broker não podem ser incluídas na definição de uma função definida pelo usuário Transact-SQL:

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Funções definidas pelo usuário podem ser aninhadas, isto é, uma função definida pelo usuário pode chamar outra. O nível de aninhamento é incrementado quando a execução da função é iniciada, e reduzido quando a execução da função chamada é concluída. Até 32 níveis de funções definidas pelo usuário podem ser aninhados. Se o máximo de níveis de aninhamento for excedido haverá falha em toda a cadeia de funções da chamada de aninhamento. Qualquer referência a um código gerenciado de uma função definida pelo usuário do Transact-SQL é contada como um nível em relação ao limite de 32 níveis de aninhamento. Os métodos invocados a partir do código gerenciado não são contados em relação a esse limite.

Usar ordem de classificação em funções com valor de tabela CLR

Ao usar a cláusula ORDER em funções CLR com valor de tabela, siga estas diretrizes:

Você deve garantir que os resultados sejam sempre ordenados na ordem especificada. Se os resultados não estiverem na ordem especificada, o SQL Server gerará uma mensagem de erro quando a consulta for executada.
Se uma cláusula ORDER estiver especificada, a saída da função com valor de tabela deverá ser ordenada de acordo com a ordenação da coluna (explícita ou implícita). Por exemplo, se o agrupamento de colunas for chinês, os resultados retornados deverão ser classificados de acordo com as regras de classificação chinesas. (O agrupamento é especificado na DDL para a função com valor de tabela ou obtido a partir do agrupamento do banco de dados.)
O SQL Server sempre verifica a ORDER cláusula, se especificada, enquanto retorna resultados, se o processador de consulta a usa ou não para executar otimizações adicionais. Use a ORDER cláusula somente se souber que ela é útil para o processador de consulta.
O processador de consultas do SQL Server beneficia-se automaticamente da cláusula ORDER nos seguintes casos:
- Consultas de inserção em que a cláusula ORDER é compatível com um índice.
- Cláusulas ORDER BY compatíveis com a cláusula ORDER.
- Agregações, em que GROUP BY é compatível com a cláusula ORDER.
- Agregações DISTINCT em que as colunas distintas são compatíveis com a cláusula ORDER.

A ORDER cláusula não garante resultados ordenados quando uma SELECT consulta é executada, a menos que ORDER BY também seja especificado na consulta. Confira sys.function_order_columns (Transact-SQL) para obter informações sobre como consultar colunas incluídas na ordem de classificação nas funções com valor de tabela.

Metadados

A tabela a seguir lista as exibições do catálogo do sistema que você pode usar para retornar metadados sobre funções definidas pelo usuário.

Exibição do sistema	Descrição
sys.sql_modules	Consulte o exemplo E na seção Exemplos.
sys.assembly_modules	Exibe informações sobre funções CLR definidas pelo usuário.
sys.parameters	Exibe informações sobre os parâmetros definidos em funções definidas pelo usuário.
sys.sql_expression_dependencies	Exibe os objetos subjacentes referenciados por uma função.

Permissões

Requer a permissão CREATE FUNCTION no banco de dados e a permissão ALTER no esquema no qual a função está sendo criada. Se a função especificar um tipo definido pelo usuário, a permissão EXECUTE será exigida no tipo.

Exemplos

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

R. Use uma função definida pelo usuário com valor escalar que calcula a semana ISO

O exemplo a seguir cria a função definida pelo usuário ISOweek. Essa função usa um argumento de data e calcula o número da semana ISO. Para que essa função calcule corretamente, SET DATEFIRST 1 deve ser invocado antes da função ser chamada.

O exemplo também mostra o uso da cláusula EXECUTE AS (Transact-SQL) para especificar o contexto de segurança no qual um procedimento armazenado pode ser executado. No exemplo, a opção CALLER especifica que o procedimento é executado no contexto do usuário que o chama. As outras opções que podem ser especificadas são SELF, OWNER e user_name.

Aqui está a chamada de função. DATEFIRST é definido como 1.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

Este é o conjunto de resultados.

ISO Week
----------------
52

B. Criar uma função com valor de tabela embutida

O exemplo a seguir retorna uma função com valor de tabela embutida no banco de dados AdventureWorks2022. Ela retorna três colunas ProductID, Name e a agregação dos totais acumulados no ano por loja como YTD Total para cada produto vendido para a loja.

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
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Para invocar a função, execute esta consulta.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Criar uma função com valor de tabela de várias instruções

O exemplo a seguir cria a função fn_FindReports(InEmpID) com valor de tabela no banco de dados AdventureWorks2022. Quando fornecida com uma ID de funcionário válida, a função retorna uma tabela que corresponde a todos os funcionários subordinados ao funcionário direta ou indiretamente. A função usa uma CTE (expressão de tabela comum) recursiva para produzir a lista hierárquica de funcionários. Para obter mais informações sobre CTEs recursivas, confira WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        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

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. Criar uma função CLR

O exemplo cria a função CLR len_s. Antes que a função seja criada, o assembly SurrogateStringFunction.dll é registrado no banco de dados local.

Aplica-se a: SQL Server 2008 (10.0.x) SP 1 e versões posteriores.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Para obter um exemplo de como criar um função com valor de tabela CLR, consulte Funções com valor de tabela CLR.

E. Exibir a definição de funções definidas pelo usuário

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

A definição de funções criadas usando a opção não pode ser visualizada ENCRYPTION usando sys.sql_modules, no entanto, outras informações sobre as funções criptografadas são exibidas.

CREATE FUNCTION (Transact-SQL)

Sintaxe

Argumentos

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = padrão ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

NULL | NOT NULL

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

<> function_option ::= e <clr_function_option> ::=

ENCRYPTION

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<> column_definition ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

<> column_constraint ::= e <table_constraint> ::=

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

logical_expression

<> computed_column_definition ::=

column_name

computed_column_expression

<index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF}

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

Práticas recomendadas

Tipos de dados

Comentários

Interoperabilidade

Interoperabilidade de colunas computadas

Chamar procedimentos armazenados estendidos a partir de funções

Limitações

Usar ordem de classificação em funções com valor de tabela CLR

Metadados

Permissões

Exemplos

R. Use uma função definida pelo usuário com valor escalar que calcula a semana ISO

B. Criar uma função com valor de tabela embutida

C. Criar uma função com valor de tabela de várias instruções

D. Criar uma função CLR

E. Exibir a definição de funções definidas pelo usuário

Conteúdo relacionado

Comentários

Comentários

Recursos adicionais