CRIAR FUNÇÃO (Transact-SQL)

2025-06-28

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

Cria uma UDF (função definida pelo usuário), que é uma rotina CLR (Transact-SQL ou 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 retornado pode ser um valor escalar (único) ou uma tabela.

Use CREATE FUNCTION para criar uma rotina T-SQL reutilizável que pode ser usada destas maneiras:

Em Transact-SQL declarações 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 melhorar 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 no SQL Server é discutida neste artigo. A integração do CLR não se aplica ao Banco de Dados SQL do Azure.

Observação

Para o Microsoft Fabric Data Warehouse ou o Azure Synapse Analytics, consulte CREATE FUNCTION (Azure Synapse Analytics e Microsoft Fabric).

Dica

Você pode especificar CREATE OR ALTER FUNCTION para criar uma nova função se não existir por esse nome ou alterar uma função existente, em uma única instrução.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para Transact-SQL funções escalares.

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 Transact-SQL funções embutidas com valor de tabela.

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 Transact-SQL funções com valor de tabela de várias instruções.

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 ]
}

Argumentos

OU 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á existir.

A sintaxe opcional OR ALTER está disponível para CLR, começando com 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ção devem estar em conformidade com as regras para identificadores e devem ser exclusivos no banco de dados e em seu esquema.

Os parênteses são necessários após o nome da função, mesmo que um parâmetro não seja especificado.

@parameter_name

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

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 um padrão para o parâmetro seja definido.

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 para identificadores. Os parâmetros são locais para a função; Os mesmos nomes de parâmetros podem ser usados em outras funções. Os parâmetros podem substituir apenas as 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 BATCH. 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, incluindo tipos CLR definidos pelo usuário e tipos de tabela definidos pelo usuário, são permitidos, exceto o tipo de dados timestamp . Para funções CLR, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, 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 em funções Transact-SQL ou CLR.

Se type_schema_name não for especificado, o Mecanismo de Banco de Dados procurará na scalar_parameter_data_type 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 padrão for definido, a função poderá ser executada sem 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 implica 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.

SOMENTE LEITURA

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 nenhum outro tipo de parâmetro.

return_data_type

O valor retornado de uma função escalar definida pelo usuário. Para funções Transact-SQL, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto o tipo de dados timestamp . Para funções CLR, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto os 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 em 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 apenas em funções escalares e MSTVFs (funções com valor de tabela de várias instruções).

Em funções escalares, function_body é uma série de instruções Transact-SQL que juntas são avaliadas como 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.

TABELA

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

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

Em MSTVFs (funções com valor de tabela de várias instruções), @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 podem ser especificados apenas para funções Transact-SQL e não para funções CLR.

select_stmt

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

PEDIDO (<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 a ordem de classificação em funções com valor de tabela CLR mais adiante neste artigo.

NOME EXTERNO <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 aos quais o nome da função criada deve se referir.

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

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

Geralmente, o valor contém um ponto ou ponto incorporado. Nesses casos, a sintaxe Transact-SQL requer que o valor seja limitado por um par de colchetes ([]) ou por um par de aspas duplas ("").
method_name - deve corresponder a method_name um valor na coluna 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 o 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 ativar 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 da tabela para uma função Transact-SQL. A declaração de 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 (versão prévia em algumas regiões).

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

NULO | NÃO NULO

Com suporte apenas para funções escalares definidas pelo usuário compiladas nativamente. Para obter mais informações, consulte Funções de User-Defined escalares para OLTP In-Memory.

COMPILAÇÃO_NATIVA

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

COMECE ATÔMICO COM

Necessário, e com suporte 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.

EXECUTAR COMO

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 opções a seguir.

ENCRIPTAÇÃO

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 arquivos de banco de dados. Além disso, os usuários que podem anexar um depurador ao processo do servidor 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 aos quais ela faz referência. 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 de função deve primeiro ser modificada ou descartada para remover dependências no objeto que deve ser modificado.

A associação da função aos objetos aos quais ela faz referência é removida somente quando ocorre uma das seguintes ações:

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

Uma função só poderá ser associada ao esquema se as seguintes condições forem verdadeiras:

A função é uma função Transact-SQL.
As funções e exibições definidas pelo usuário referenciadas pela função também são associadas ao esquema.
Os objetos referenciados pela função são referenciados usando 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 CREATE FUNCTION instrução tem REFERENCES permissão sobre os objetos de banco de dados aos quais a função faz referência.

RETORNA NULL NA ENTRADA NULA | CHAMADO EM ENTRADA NULA

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 se NULL for passado como um argumento.

Se RETURNS NULL ON NULL INPUT for especificado em uma função CLR, ele indicará que o SQL Server pode retornar NULL quando qualquer um dos argumentos recebidos for 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 indique RETURNS NULL ON NULL INPUT, mas a CREATE FUNCTION instrução indicar 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.

EXECUTAR COMO

Especifica o contexto de segurança no qual a função definida pelo usuário é executada. Portanto, você pode controlar qual conta de usuário o SQL Server usa para validar permissões em qualquer objeto de banco de dados referenciado pela função.

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

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

INLINE = { ON | DESLIGADO }

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

Especifica se essa UDF escalar deve ser embutida ou não. Essa cláusula se aplica somente a funções escalares definidas pelo usuário. A INLINE cláusula 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 a UDF é inlineable. Se INLINE = ON for especificado, mas a UDF não for inlineable, um erro será gerado. Para obter mais informações, consulte de inlining da UDF escalar.

< > column_definition ::=

Define o tipo de dados da tabela. A declaração de tabela inclui definições e restrições de coluna. Para funções CLR, somente 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 para identificadores e devem ser exclusivos na tabela. column_name pode consistir de 1 a 128 caracteres.

data_type

Especifica o tipo de dados de coluna. Para funções Transact-SQL, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto o carimbo de data/hora. Para funções CLR, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, 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 em funções Transact-SQL ou CLR.

PADRÃO constant_expression

Especifica o valor fornecido para a coluna quando um valor não é fornecido explicitamente durante uma inserção. constant_expression é uma constante, NULL, ou 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.

AGRUPAR collation_name

Especifica a ordenação da coluna. Se não for especificado, a coluna receberá 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, consulte 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 coluna de identificador globalmente exclusivo de linha. Somente uma coluna uniqueidentifier por tabela pode ser designada como a ROWGUIDCOL coluna. 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 on INSERT instruções. Um valor padrão pode ser especificado; no entanto, NEWID não pode ser especificado como padrão.

IDENTIDADE

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 normalmente são usadas junto com PRIMARY KEY restrições para servir como o identificador de linha exclusivo para a tabela. A IDENTITY propriedade pode ser atribuída a 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 associados não podem ser usados com uma coluna de identidade. Você deve especificar a semente e o incremento ou nenhum. Se nenhum dos dois for especificado, o padrão será (1,1).

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

semente

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

incremento

O valor inteiro a ser adicionado ao valor inicial 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.

NULO | NÃO NULO

Determina se valores nulos são permitidos 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.

CHAVE PRIMÁRIA

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.

ÚNICO

Uma restrição que fornece integridade de entidade para uma coluna ou 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.

AGRUPADO | NÃO AGRUPADO

Indique que um índice clusterizado ou não clusterizado é criado para a PRIMARY KEY restrição or UNIQUE . PRIMARY KEY restrições usam CLUSTERED, e UNIQUE restrições usam NONCLUSTERED.

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 .NONCLUSTERED

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

VERIFICAR

Uma restrição que impõe a integridade do domínio limitando os valores possíveis que podem ser inseridos em uma coluna ou 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, consulte 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, consulte CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

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

FILLFACTOR = fillfactor

Especifica uma porcentagem que indica 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 recalculadas. O padrão é OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

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

PERMITIR_TRAVAS_DE_PÁGINA = { LIGADO | DESLIGADO }

Especifica se os 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 da 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 qualquer objeto especificado na definição da função.

Para obter mais informações e considerações de desempenho sobre funções embutidas com valor de tabela (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 os parâmetros forem especificados em uma função CLR, eles deverão ser tipos do SQL Server, conforme definido anteriormente para scalar_parameter_data_type. Para obter mais informações comparando tipos de dados do sistema SQL Server com tipos de dados de integração CLR ou tipos de dados de Common Language Runtime do .NET Framework, consulte Mapeando dados de parâmetro CLR.

Para que o SQL Server faça referência ao método correto quando ele estiver 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 ].
Receba todos os parâmetros por valor, não por referência.
Use tipos de parâmetro compatíveis com os 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 deverá <method_specifier> ser do tipo IEnumerator ou IEnumerable, e pressupõe que a interface seja implementada pelo criador da função. Ao contrário das funções Transact-SQL, as funções CLR não podem incluir PRIMARY KEY, , ou UNIQUE restrições no CHECK<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 em <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 User-Defined CLR.

Observações

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, consulte Transact-SQL Convenções de sintaxe (Transact-SQL). As funções com valor de tabela podem ser invocadas onde as FROM expressões de tabela são permitidas na cláusula de SELECT, INSERT, UPDATE, ou DELETE instruções. 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 TRY...CATCH instruções.
DECLARE instruções que definem variáveis de dados locais e cursores locais.
SELECT Instruções que contêm listas SELECT com expressões que atribuem valores a variáveis locais.
Operações de cursor que fazem referência a cursores locais que são declarados, abertos, fechados e desalocados 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 para o cliente não são permitidas.
INSERT, UPDATEe DELETE instruções que modificam variáveis de tabela local.
EXECUTE instruções 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 coluna computada

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 persistentes ou indexadas.

Propriedade	Descrição	Anotaçõ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.	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 do sistema ou tabelas do sistema virtual) na instância local do SQL Server.
`UserDataAccess`	A função acessa os dados do 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 Transact-SQL funções são determinadas automaticamente pelo SQL Server. As propriedades de acesso a dados e determinismo das 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

As 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 propriedade:

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

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

Chamar procedimentos armazenados estendidos 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. Todas as 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 invocações de um lote ou procedimento armazenado, o procedimento armazenado estendido é executado no contexto da conta de segurança do Windows na qual o SQL Server está sendo executado. O proprietário do procedimento armazenado deve considerar esse cenário ao conceder EXECUTE permissão 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 Transact-SQL definida pelo usuário:

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

As funções definidas pelo usuário podem ser aninhadas; ou seja, uma função definida pelo usuário pode chamar outra. O nível de aninhamento é incrementado quando a função chamada inicia a execução e decrementado quando a função chamada termina a execução. As funções definidas pelo usuário podem ser aninhadas até 32 níveis. Exceder os níveis máximos de aninhamento faz com que toda a cadeia de funções de chamada falhe. Qualquer referência ao código gerenciado de uma função definida pelo usuário Transact-SQL conta como um nível em relação ao limite de aninhamento de 32 níveis. Os métodos invocados a partir do código gerenciado não são contados em relação a esse limite.

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

Ao usar a ORDER cláusula em funções com valor de tabela CLR, 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 ORDER cláusula for especificada, a saída da função com valor de tabela deverá ser classificada de acordo com a ordenação da coluna (explícita ou implícita). Por exemplo, se a ordenação de coluna for chinesa, os resultados retornados deverão ser classificados de acordo com as regras de classificação chinesas. (A ordenação é especificada na DDL para a função com valor de tabela ou obtida da ordenação do banco de dados.)
O SQL Server sempre verifica a ORDER cláusula, se especificada, ao retornar resultados, se o processador de consultas a usa ou não para executar otimizações adicionais. Use a ORDER cláusula somente se você souber que ela é útil para o processador de consultas.
O processador de consultas do SQL Server aproveita a ORDER cláusula automaticamente nos seguintes casos:
- Insira consultas em que a ORDER cláusula é compatível com um índice.
- ORDER BY cláusulas compatíveis com a ORDER cláusula.
- Agregados, onde GROUP BY é compatível com ORDER a cláusula.
- DISTINCT agregados em que as colunas distintas são compatíveis com a ORDER cláusula.

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. Consulte sys.function_order_columns (Transact-SQL) para obter informações sobre como consultar colunas incluídas na ordem de classificação para 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.

Visualização do sistema	Descrição
sys.sql_módulos	Veja 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 nas 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).

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

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

O exemplo também mostra o uso da cláusula EXECUTE AS Clause (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 você pode especificar são SELF, OWNERe 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';

Veja aqui o conjunto de resultados.

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

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

O exemplo a seguir retorna uma função embutida com valor de tabela no banco de dados AdventureWorks2022. Ele retorna três colunas ProductID, Name, e o agregado 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 AdventureWorks2022 banco de dados. Quando fornecida com uma ID de funcionário válida, a função retorna uma tabela que corresponde a todos os funcionários que se reportam direta ou indiretamente ao funcionário. A função usa uma expressão de tabela comum (CTE) recursiva para produzir a lista hierárquica de funcionários. Para obter mais informações sobre CTEs recursivas, consulte 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 len_sCLR. Antes da função ser 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 uma função com valor de tabela CLR, consulte Funções Table-Valued 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 ENCRYPTION opção não pode ser exibida usando sys.sql_modules; no entanto, outras informações sobre as funções criptografadas são exibidas.

Compartilhar via

CRIAR FUNÇÃO (Transact-SQL)

Sintaxe

Argumentos

OU ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = padrão ]

SOMENTE LEITURA

return_data_type

function_body

scalar_expression

TABELA

select_stmt

PEDIDO (<order_clause>)

NOME EXTERNO <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 ] )

NULO | NÃO NULO

COMPILAÇÃO_NATIVA

COMECE ATÔMICO COM

SCHEMABINDING

EXECUTAR COMO

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

ENCRIPTAÇÃO

SCHEMABINDING

RETORNA NULL NA ENTRADA NULA | CHAMADO EM ENTRADA NULA

EXECUTAR COMO

INLINE = { ON | DESLIGADO }

< > column_definition ::=

column_name

data_type

PADRÃO constant_expression

AGRUPAR collation_name

ROWGUIDCOL

IDENTIDADE

semente

incremento

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

NULO | NÃO NULO

CHAVE PRIMÁRIA

ÚNICO

AGRUPADO | NÃO AGRUPADO

VERIFICAR

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 }

PERMITIR_TRAVAS_DE_PÁGINA = { LIGADO | DESLIGADO }

Práticas recomendadas

Tipos de dados

Observações

Interoperabilidade

Interoperabilidade de coluna computada

Chamar procedimentos armazenados estendidos de funções

Limitações

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

Metadados

Permissões

Exemplos

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

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

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

Recursos adicionais