FUNÇÃO CRIAR (Transact-SQL)

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

Cria uma função definida pelo usuário (UDF), que é uma rotina de 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 CREATE FUNCTION para criar uma rotina T-SQL reutilizável que pode ser usada das seguintes maneiras:

Em Transact-SQL declarações como SELECT
Em aplicativos que chamam a função
Na definição de outra função definida pelo utilizador
Para parametrizar uma vista ou melhorar a funcionalidade de uma vista 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 diretiva de segurança

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

Note

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

Tip

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

Transact-SQL convenções de sintaxe

Syntax

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 com valor de tabela embutido.

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 Transact-SQL cláusulas de função.

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

Arguments

OU ALTER

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

Condicionalmente altera a função apenas se ela já existir.

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ção devem estar em conformidade com as regras para identificadores e devem ser exclusivos dentro do banco de dados e para seu esquema.

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 um máximo de 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 tomar o lugar apenas de constantes; eles não podem ser usados em vez de nomes de tabelas, nomes de colunas ou nomes de outros objetos de banco de dados.

ANSI_WARNINGS não é honrado 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 uma variável for definida como char(3) e, em seguida, definida como um valor maior que três caracteres, os dados serão truncados para o tamanho definido e a INSERT instrução or 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 de carimbo de data/hora . 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 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 padrão for definido, a função pode ser executada sem especificar um valor para esse parâmetro.

Os valores de parâmetros 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 em que omitir o 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.

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 (TVPs) definidos pelo usuário e não pode ser usado para nenhum 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, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto o tipo de dados de carimbo de data/hora . Para funções CLR, todos os tipos de dados, incluindo tipos definidos pelo usuário CLR, 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 funções com valor de tabela de várias instruções (MSTVFs).

Em funções escalares, function_body é uma série de Transact-SQL instruções que, juntas, avaliam até 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 de retorno da função com valor de tabela (TVF) é uma tabela. Apenas 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 funções com valor de tabela de várias instruções (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 apenas 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 embutido (TVF).

ENCOMENDAR (<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.

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

Muitas vezes, o valor contém um ponto ou ponto incorporado. 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 method_name um valor na coluna de SELECT * FROM sys.assembly_modules;.

O método deve ser estático.

Em um exemplo típico para 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 Common Language Runtime. 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 contido.

< > 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 (Visualização 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 colunas e tipos de dados. A tabela é sempre colocada no grupo de arquivos primário.

NULO | NÃO NULO

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

NATIVE_COMPILATION

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 suportado apenas para funções escalares definidas pelo usuário compiladas nativamente. Para obter mais informações, consulte Atomic Blocks in Native Procedures.

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 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 texto original da CREATE FUNCTION 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 tempo de execução. 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. Esta opção não pode ser especificada para funções CLR.

SCHEMABINDING

Especifica que a função está vinculada aos objetos de banco de dados aos quais faz referência. Quando SCHEMABINDING é especificado, os objetos base não podem ser modificados de uma forma que afetaria 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 a 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 pode ser vinculada ao esquema somente se as seguintes condições forem verdadeiras:

A função é uma função Transact-SQL.
As funções definidas pelo usuário e as exibições referenciadas pela função também são vinculadas 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 nos objetos de banco de dados aos quais a função faz referência.

DEVOLVE NULL NA ENTRADA NULA | CHAMADA NA 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, isso indica que o SQL Server pode retornar NULL quando qualquer um dos argumentos que recebe for NULL, sem realmente invocar o corpo da função. Se o método de uma função CLR especificado 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.

EXECUTAR COMO

Especifica o contexto de segurança sob o 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 quaisquer objetos de banco de dados referenciados pela função.

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

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

INLINE = { LIGADO | 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. Esta cláusula aplica-se apenas a funções escalares definidas pelo utilizador. A cláusula INLINE não é obrigatória. Se a INLINE cláusula não for especificada, ela será automaticamente definida como ON ou OFF com base no fato de a UDF ser inlineável. Se INLINE = ON for especificado, mas o UDF for considerado não inlineável, um erro será lançado. Para obter mais informações, consulte Scalar UDF Inlining.

< > column_definition ::=

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

column_name

O nome de uma coluna na tabela. Os nomes das colunas 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 da coluna. Para funções Transact-SQL, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto carimbo de data/hora. Para funções CLR, todos os tipos de dados, incluindo tipos CLR definidos pelo usuário, são permitidos, exceto texto, ntext, image, char, varchar, varchar(max) e carimbo de data/hora. 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, NULLou um valor de função do sistema. DEFAULT As definições podem ser aplicadas a qualquer coluna, exceto àquelas que têm a IDENTITY propriedade. DEFAULT não pode ser especificado para funções com valor de tabela CLR.

COLLATE collation_name

Especifica o agrupamento para a coluna. Se não for especificado, será atribuído à coluna o agrupamento padrão do banco de dados. O nome do agrupamento pode ser um nome de agrupamento do Windows ou um nome de agrupamento SQL. Para obter uma lista e mais informações sobre agrupamentos, consulte Nome de agrupamento do Windows (Transact-SQL) e Nome de agrupamento 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 global exclusivo de linha. Apenas uma coluna uniqueidentifier por tabela pode ser designada como ROWGUIDCOL coluna. A ROWGUIDCOL propriedade só pode ser atribuída a uma coluna uniqueidentifier .

A ROWGUIDCOL propriedade não impõe exclusividade dos valores armazenados na coluna. 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 juntamente 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 numérica(p,0 ). Apenas uma coluna de identidade pode ser criada por tabela. Os padrões e DEFAULT restrições vinculados 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.

seed

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

increment

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

UNIQUE

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.

AGRUPADOS | NÃO AGRUPADO

Indique 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, os PRIMARY KEY usos NONCLUSTERED.

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

CHECK

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

< > index_option ::=

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

PAD_INDEX = { EM | DESLIGADO }

Especifica o preenchimento do índice. A predefinição é OFF.

FILLFACTOR = fator de enchimento

Especifica uma porcentagem que indica o quão cheio o Mecanismo de Banco de Dados deve tornar o nível de 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 = { EM | DESLIGADO }

Especifica a resposta de erro quando uma operação de inserção tenta inserir valores de chave duplicados 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 reconstruído. A predefinição é OFF.

STATISTICS_NORECOMPUTE = { LIGADO | DESLIGADO }

Especifica se as estatísticas de distribuição são recalculadas. A predefinição é OFF.

ALLOW_ROW_LOCKS = { EM | DESLIGADO }

Especifica se os bloqueios de linha são permitidos. A predefinição é ON.

ALLOW_PAGE_LOCKS = { EM | DESLIGADO }

Especifica se os bloqueios de página são permitidos. A predefinição é ON.

Melhores práticas

Se uma função definida pelo usuário não for criada com a SCHEMABINDING cláusula, as alterações feitas nos objetos subjacentes poderão afetar a definição da função e produzir resultados inesperados quando ela for invocada. Recomendamos que você implemente um dos seguintes métodos para garantir que a função não fique 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 sp_refreshsqlmodule procedimento armazenado 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 com valor de tabela embutido (TVFs embutidos) 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âmetros 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 ].
Receba todos os parâmetros por valor, não por referência.
Use tipos de parâmetros 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 Transact-SQL funções, 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 em <method_specifier> 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 CLR User-Defined Functions.

Remarks

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 com 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, UPDATEou DELETE instruções. Para obter mais informações, consulte Executar funções definidas pelo usuário.

UDFs no Microsoft Fabric Data Warehouse

As UDFs escalares são um recurso de visualização no Fabric Data Warehouse.

No Microsoft Fabric Data Warehouse, os UDFs escalares têm de ser inlineáveis para serem usados com SELECT ... FROM consultas em tabelas de utilizador, mas ainda assim podes criar funções que não são inlineáveis. UDFs escalares que não são inlineable funcionam em um número limitado de cenários. Você pode verificar se um UDF pode ser embutido. Para mais informações, consulte a versão do CREATE FUNCTION para o Fabric Data Warehouse.

Interoperability

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

Declarações de atribuição.
Instruções Control-of-Flow, 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 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 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 locais.
EXECUTE instruções chamando 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 persistentes ou indexadas.

Property	Description	Notes
`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 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 virtuais do sistema) 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 para essas propriedades, use OBJECTPROPERTYEX (Transact-SQL).

Important

As funções devem ser criadas para SCHEMABINDING 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 calculada 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. 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 unir a 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 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.

Limitations

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

As funções definidas pelo usuário não podem conter uma OUTPUT INTO cláusula que tenha uma tabela como 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 diminuído quando a função chamada termina a execução. As funções definidas pelo usuário podem ser aninhadas em até 32 níveis. Exceder os níveis máximos de aninhamento faz com que toda a cadeia de funções chamadoras falhe. Qualquer referência a código gerenciado a partir 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 contam para esse limite.

Usar 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 deve ser classificada de acordo com o agrupamento 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 em chinês. (O agrupamento é especificado na DDL para a função com valor de tabela ou obtido do agrupamento 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 apenas se 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:
- Inserir consultas em que a ORDER cláusula é compatível com um índice.
- ORDER BY cláusulas compatíveis com a ORDER cláusula.
- Agregados, quando GROUP BY for compatível com ORDER 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 especificada 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.

Metadata

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.

Vista do sistema	Description
sys.sql_modules	Veja o exemplo E na seção Exemplos.
sys.assembly_modules	Exibe informações sobre funções definidas pelo usuário CLR.
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.

Permissions

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

Examples

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

A. Use 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 . Esta função usa um argumento de data e calcula o número da semana ISO. Para que esta 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 (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 está 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';

Aqui está o conjunto de resultados.

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

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

O exemplo seguinte devolve uma função com valores de tabela inline na base de dados AdventureWorks2025. Ele retorna três colunas ProductID, Namee o agregado de totais do ano até a data 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 ufn_FindReports(InEmpID) seguir cria a função AdventureWorks2025 com valor de tabela no banco de dados. Quando fornecida com um ID de funcionário válido, a função retorna uma tabela que corresponde a todos os funcionários que se reportam ao funcionário direta ou indiretamente. A função usa uma expressão de tabela comum recursiva (CTE) 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 CLR Table-Valued Functions.

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 visualizada usando sys.sql_modules, no entanto, outras informações sobre as funções criptografadas são exibidas.

Comentários

Esta página foi útil?

Last updated on 2025-11-19

Partilhar via

FUNÇÃO CRIAR (Transact-SQL)

Syntax

Arguments

OU 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

ENCOMENDAR (<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

NATIVE_COMPILATION

COMECE ATÔMICO COM

SCHEMABINDING

EXECUTAR COMO

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

ENCRYPTION

SCHEMABINDING

DEVOLVE NULL NA ENTRADA NULA | CHAMADA NA ENTRADA NULA

EXECUTAR COMO

INLINE = { LIGADO | DESLIGADO }

< > column_definition ::=

column_name

data_type

PADRÃO constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

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

NULO | NÃO NULO

CHAVE PRIMÁRIA

UNIQUE

AGRUPADOS | NÃO AGRUPADO

CHECK

logical_expression

< > computed_column_definition ::=

column_name

computed_column_expression

< > index_option ::=

PAD_INDEX = { EM | DESLIGADO }

FILLFACTOR = fator de enchimento

IGNORE_DUP_KEY = { EM | DESLIGADO }

STATISTICS_NORECOMPUTE = { LIGADO | DESLIGADO }

ALLOW_ROW_LOCKS = { EM | DESLIGADO }

ALLOW_PAGE_LOCKS = { EM | DESLIGADO }

Melhores práticas

Tipos de dados

Remarks

UDFs no Microsoft Fabric Data Warehouse

Interoperability

Interoperabilidade de colunas computadas

Chamar procedimentos armazenados estendidos a partir de funções

Limitations

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

Metadata

Permissions

Examples

A. 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 embutido

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