CREATE FUNCTION

Aplica-se a:Endpoint de análise SQL no Microsoft Fabric e Warehouse no Microsoft Fabric

CREATE FUNCTION Cria funções inline com valores de tabela e funções escalares.

Observação

Os UDFs externos são uma funcionalidade de pré-visualização no Fabric Data Warehouse.

Este artigo aplica-se a Microsoft Fabric Data Warehouse e ao endpoint de análise SQL de Fabric itens. Para outras plataformas, vejaCREATE FUNCTION (Transact-SQL).

Uma função definida pelo utilizador é uma rotina Transact-SQL que aceita parâmetros, realiza uma ação como um cálculo complexo e devolve o resultado dessa ação como valor. As funções escalares retornam um valor escalar, como um número ou uma cadeia de caracteres. Funções com valor de tabela definidas pelo usuário (TVFs) retornam uma tabela.

Use CREATE FUNCTION para criar uma rotina T-SQL reutilizável que possa usar das seguintes formas:

  • Em Transact-SQL afirmações como SELECT.
  • No Warehouse, em Transact-SQL instruções de manipulação de dados (DML) como UPDATE, INSERT, e DELETE.
  • Em aplicações que chamam a função.
  • Na definição de outra função definida pelo utilizador.
  • Para substituir um procedimento armazenado.

Especifique CREATE OR ALTER FUNCTION criar uma nova função se não existir com esse nome, ou alterar uma função existente, numa única instrução.

Transact-SQL Convenções sintáticas

Sintaxe

Sintaxe da função escalar

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

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

Sintaxe da função com valor de tabela embutido

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Sintaxe da função externa

Um UDF externo é uma função que faz referência a uma Função de Dados de Utilizador Fabric externa.

CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]

Observação

Os UDFs externos são uma funcionalidade de pré-visualização no Fabric Data Warehouse.

A CREATE FUNCTION instrução deriva automaticamente o tipo de retorno, os parâmetros e os tipos de parâmetros a partir da definição da Função de Dados de Utilizador Fabric externa referenciada. Se a assinatura da Função de Dados de Utilizador Fabric externa mudar, deve recriar a função para sincronizar a sua definição com a assinatura atualizada.

Argumentos

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 das funções devem seguir as regras dos identificadores e ser únicos dentro da base de dados e do seu esquema.

Para definições de funções escalares nativas T-SQL e de tabelas em linha, inclua parênteses após o nome da função mesmo quando não existam parâmetros. As definições de funções externas utilizam a sintaxe separada AS EXTERNAL FUNCTION .

@ parameter_name

Um parâmetro na função definida pelo usuário. Pode declarar um ou mais parâmetros.

Uma função pode ter até 2.100 parâmetros. Quando um utilizador ou aplicação chama uma função, deve fornecer o valor para cada parâmetro declarado, a menos que defina um valor 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 seguir as regras dos identificadores. Os parâmetros são locais à função. Pode usar os mesmos nomes de parâmetros noutras funções. Os parâmetros só podem substituir constantes. Não podem ser usados em vez dos nomes de tabelas, nomes de colunas ou dos nomes de outros objetos da base de dados.

ANSI_WARNINGS não é respeitado quando passa parâmetros num procedimento armazenado, numa função definida pelo utilizador ou quando declara e define variáveis numa instrução batch. Por exemplo, se definir uma variável como char(3) e depois a definir para um valor superior a três caracteres, os dados são truncados para o tamanho definido e a instrução SQL tem sucesso.

parameter_data_type

O tipo de dados do parâmetro. Para funções Transact-SQL, todos os tipos de dados escalares suportados são permitidos .

[ = padrão ]

Um valor padrão para o parâmetro. Se definir um valor por defeito , pode executar a função sem especificar um valor para esse parâmetro.

Quando um parâmetro da função tem um valor predefinido, deve especificar a palavra-chave DEFAULT ao chamar a função para recuperar o valor predefinido. 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.

return_data_type

O valor de retorno de uma função escalar definida pelo usuário.

Para funções em Fabric Data Warehouse, podes usar todos os tipos de dados, exceto ocarimbo temporalda versão/ de linhas. Tipos não escalares como tabela não são permitidos.

Para UDFs externos (Pré-visualização), a RETURNS cláusula é opcional. Se for omitido, o tipo de retorno é automaticamente inferido a partir do tipo de retorno da Fabric User Data Function referenciada. Pode especificar a RETURNS cláusula para sobrepor o tipo de retorno inferido quando necessário.

function_body

Uma série de declarações Transact-SQL.

Em funções escalares, function_body é uma série de Transact-SQL instruções que, juntas, avaliam até um valor escalar, que pode incluir:

  • Expressão de instrução única
  • Expressões de múltiplas instruções (IF/ELSE e BEGIN/END blocos)
  • Variáveis locais
  • Chamadas para funções SQL incorporadas disponíveis
  • Chamadas para outras UDFs
  • SELECT instruções e referências a tabelas, vistas e funções de tabela inline
  • Instruções de controlo de fluxo, incluindo IF/ELSE, WHILE, BREAK, CONTINUE, e RETURN

Para UDFs externos (Preview), não pode especificar o function_body porque a implementação da função está definida externamente na Fabric User Data Function associada. A base de dados armazena apenas os metadados da função. A lógica executável reside na definição da função externa.

scalar_expression

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

select_stmt

A instrução única SELECT que define o valor de retorno de uma função com valor de tabela embutido. Para uma função inline com valores em tabela, não existe corpo de funções. A tabela é o conjunto de resultados de uma única SELECT instrução.

TABLE

Especifica que o valor de retorno da função com valor de tabela (TVF) é uma tabela. Só podes passar constantes e @local_variables aos TVFs.

Nos TVFs inline (pré-visualização), define-se o TABLE valor de retorno através de uma única SELECT instrução. As funções embutidas não têm variáveis de retorno associadas.

<function_option>

Em Fabric Data Warehouse, as ENCRYPTION palavras-chave e EXECUTE AS não são suportadas.

As opções de função suportadas incluem:

INLINE = AUTO

Especifica se uma função escalar definida pelo utilizador pode ser criada ou alterada, independentemente dos requisitos de inlining. A INLINE cláusula é opcional. Para um UDF escalar inlineável, especificar INLINE = AUTO não altera a sua inlineabilidade nem o comportamento de execução.

Para um padrão de chamada standalone suportado, consulte Criar um UDF escalar para execução autónoma.

SCHEMABINDING

Especifica que a função está vinculada aos objetos de banco de dados aos quais faz referência. Quando especificas SCHEMABINDING, não podes modificar os objetos subjacentes (como uma vista ou uma tabela, por exemplo) de uma forma que afete a definição da função. Deve primeiro modificar ou eliminar a definição da função para remover dependências do objeto que pretende modificar.

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

  • Deixas de usar a função.

  • Usa ALTER a declaração de função e remove a SCHEMABINDING opção.

Só pode vincular um esquema a uma função se as seguintes condições forem verdadeiras:

  • Quaisquer funções definidas pelo utilizador que a função referenciam também são limitadas ao esquema.

  • A função faz referência a objetos usando um nome em duas partes.

  • No corpo dos UDFs, só pode referenciar funções incorporadas e outros UDFs na mesma base de dados.

  • O utilizador que executa a CREATE FUNCTION instrução tem permissão REFERENCES sobre os objetos da base de dados que a função referencia.

Para remover SCHEMABINDING, use ALTER.

DEVOLVE NULL NA ENTRADA NULA | CHAMADA NA ENTRADA NULA

Especifica o OnNULLCall atributo de uma função com valor escalar. Se não especificares este atributo, CALLED ON NULL INPUT está implícito por defeito, e o corpo da função executa-se mesmo que NULL seja passado como argumento.

COMO EXTERNO FUNCTION

Faz referência a uma Função de Dados de Utilizador Fabric (UDF). Quando a função é invocada, a execução é delegada à Fabric User Data Function referenciada, onde a lógica é executada e o resultado é devolvido ao chamador T-SQL.

Esta cláusula aceita os seguintes parâmetros:

  • Nome do conjunto de funções: O nome do conjunto de funções que contém a Função de Dados de Utilizador Fabric. Cada Função de Dados do Utilizador Fabric deve pertencer a um conjunto de funções.
  • Nome da função: O nome da Função de Dados de Utilizador Fabric a referenciar.

A seguinte sintaxe mostra como estes parâmetros são especificados:

CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>

Como a função é executada remotamente, chamar uma função externa normalmente implica uma latência maior do que executar uma função T-SQL nativa definida pelo utilizador. Use funções externas quando a lógica de negócio necessária está implementada no Fabric e não pode ser expressa diretamente em T-SQL.

A CREATE FUNCTION instrução deriva automaticamente o tipo de retorno, os parâmetros e os tipos de parâmetros a partir da definição da Função de Dados de Utilizador Fabric externa referenciada. Se a assinatura da Função de Dados de Utilizador Fabric externa mudar, deve recriar a função para sincronizar a sua definição com a assinatura atualizada.

Melhores práticas

  • Escreva as suas funções definidas pelo utilizador para serem inlineáveis. Para mais informações sobre o conceito de inlining, veja Inlining of scalar UDF. Para exemplos de como tornar um UDF escalar inlineável, veja Criar UDF escalar em Microsoft Fabric Data Warehouse.

  • Se não criar uma função definida pelo utilizador com schemabinding, alterações aos objetos subjacentes podem afetar a definição da função e causar resultados inesperados ao invocar a função. Quando especifica WITH SCHEMABINDING quando cria a função, assegura que alterações posteriores aos objetos subjacentes não podem alterar ou quebrar o comportamento da função.

  • Sempre que possível, implemente a sua lógica como uma função T-SQL definida pelo utilizador (UDF). Use um UDF externo apenas para funcionalidades que não possam ser implementadas na linguagem T-SQL. Esta abordagem ajuda a minimizar a sobrecarga de execução remota e geralmente proporciona melhor desempenho nas consultas.

Interoperabilidade

Funções definidas pelo usuário com valor de tabela embutido

Uma função com valores em tabela em linha aceita apenas uma única SELECT afirmação.

Funções escalares definidas pelo usuário

  • Quando um UDF escalar não é inlineável, CREATE a instrução OR ALTER rejeitaria a instrução DDL com uma mensagem de erro personalizada.

  • A função não inlineável pode ser criada especificando INLINE=AUTO a opção da função. Uma função não inlineável não pode ser usada numa SELECT ... FROM consulta numa tabela de utilizador. INLINE=AUTO não torna a função inlineável.

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

    • Declarações de atribuição.
    • Instruções de controlo de fluxo, exceto TRY...CATCH e GOTO instruções.
    • DECLARE instruções que definem variáveis de dados locais.
    • Chamadas para funções integradas.
    • Referências a tabelas/views/iTVFs/outros UDFs escalares.
  • As instruções DML não são permitidas em funções escalares definidas pelo utilizador.

Metadados

Esta seção lista as exibições do catálogo do sistema que você pode usar para retornar metadados sobre funções definidas pelo usuário.

  • sys.sql_modules: Exibe a definição de Transact-SQL funções definidas pelo utilizador, bem como informação sobre inlineabilidade. Por exemplo:

      SELECT 
          SCHEMA_NAME(o.schema_id) AS SchemaName,
          o.name AS FunctionName,
          m.definition AS FunctionDefinition,
          m.is_inlineable AS Inlineable,
          m.inline_eligibility_mask AS InlineEligibilityMask
      FROM sys.objects o
      JOIN sys.sql_modules m ON o.object_id = m.object_id
      WHERE o.type = 'FN';
    
  • sys.parameters: Exibe informações sobre os parâmetros definidos em funções definidas pelo utilizador. O exemplo seguinte utiliza a sys.parameters vista de catálogo para mostrar assinaturas de funções, incluindo tipos de retorno e tipos de dados de parâmetros:

     WITH metadata AS
     (
         SELECT  o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name
         FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id
         WHERE o.type IN ('FN', 'XF')
     ),
     signature AS (
     SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT(
         SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id),
         ') -> ',  MAX(CASE WHEN parameter_id = 0 THEN type_name END)
     ) AS signature
     FROM metadata GROUP BY schema_id, object_id, name
     )
     SELECT *
     FROM signature;
    
  • sys.sql_expression_dependencies: Exibe os objetos subjacentes referenciados por uma função.

Permissões

Os membros das funções de Administrador, Membro e Colaborador do espaço de trabalho Malha podem criar funções.

Inlining do UDF escalar

Microsoft Fabric Data Warehouse integra automaticamente os UDFs escalares elegíveis durante a compilação de consultas.

O Expression Block inlining compila as instruções da função, incluindo branches e loops, numa expressão escalar que é executada como parte da consulta que chama. O inlining escalar UDF transforma o corpo da função em expressões ou subconsultas como parte da consulta chamada.

Uma função pode cumprir os requisitos para ambas as técnicas, mas o motor seleciona uma com base na sua definição. Diferentes funções dentro da mesma consulta podem usar técnicas distintas.

À medida que Microsoft Fabric Data Warehouse expande as suas capacidades de inlining, as funções existentes ganham-nas automaticamente.

Construções de funções suportadas

A tabela seguinte compara as construções função-corpo suportadas.

Construção função-corpo Inlining de blocos de expressão Inlining de UDF escalar
Expressões escalares, variáveis locais e IF/ELSE Suportada Suportada
WHILE loops, incluindo BREAK e CONTINUE Suportada Não suportado
Múltiplas RETURN afirmações Suportada Não suportado
NEWID() Suportada Não suportado
Funções dependentes do tempo (por exemplo, GETDATE() e SYSDATETIME()) Suportada Não suportado
Acesso TVF de tabela, vista ou em linha Não suportado Suportada
Chamadas a UDFs escalares* Não suportado Suportada

* Funções definidas pelo utilizador podem ser aninhadas: uma UDF pode chamar outra. O nível de aninhamento aumenta quando uma função chamada começa e diminui quando termina. Microsoft Fabric Data Warehouse suporta até quatro níveis de aninhamento quando um corpo UDF referencia uma tabela, vista ou TVF em linha, ou até 32 níveis caso contrário. Ultrapassar o limite de aninhamento faz com que a cadeia de funções de chamada falhe.

Funções incorporadas

O suporte para funções incorporadas, incluindo funções de sistema, difere entre as duas técnicas de inlining.

Por exemplo, criar um UDF que utiliza @@ROWCOUNT sem INLINE = AUTO retornos o erro 19841. A mensagem de erro inclui o seguinte texto:

Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.

Para uma definição de função específica, consulte Verificar se um UDF escalar pode ser inlineado.

Consultas de chamada suportadas

A tabela seguinte compara o suporte de calling-query para UDFs escalares inlineáveis.

Funcionalidade de consulta Inlining de blocos de expressão Inlining de UDF escalar
UDF chama na SELECT lista, WHERE, ou JOIN ... ON Suportada Suportada
Chamadas UDF diretas em GROUP BY ou HAVING Suportada Não suportado
Chamadas diretas da UDF ORDER BY Suportada Não suportado
CTEs na consulta de chamada Suportada Não suportado
ROLLUP, CUBE, ou GROUPING SETS Não suportado Não suportado

Se qualquer chamada UDF depender do inlining Scalar UDF, toda a consulta deve cumprir os requisitos de consulta de chamada dessa técnica.

Com o inlining Scalar UDF, projete o resultado da UDF na SELECT lista e na ordem pelo seu alias de coluna ou ordinal em vez de chamar diretamente a UDF em ORDER BY. Para exemplos de reescrita de consulta, consulte Adaptar uma consulta de chamada para o inlining Scalar UDF. Para outros limites de execução, consulte Limitações.

Verifique se uma UDF escalar pode ser embutida

A sys.sql_modules vista de catálogo descreve a elegibilidade com base na definição da função. is_inlineable é 1 para uma definição inlineável e 0 de outra forma. inline_eligibility_mask identifica as técnicas elegíveis:

inline_eligibility_mask Elegibilidade para definição
0 Nenhuma das técnicas
1 Inlining de UDF escalar
2 Inlining de blocos de expressão
3 Qualquer uma das técnicas

Estes valores descrevem a definição da função, não o plano de execução de uma consulta específica.

Utilize a seguinte consulta de exemplo para verificar se um UDF escalar é inlineável:

SELECT
    SCHEMA_NAME(b.schema_id) AS function_schema_name,
    b.name AS function_name,
    b.type_desc AS function_type,
    a.is_inlineable,
    a.inline_eligibility_mask
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('FN');

Limitações

  • Chamar um UDF escalar num contexto de consulta não suportado pode devolver o erro Scalar UDF execution is currently unavailable in this context.
  • Uma consulta que depende do inlining Scalar UDF pode falhar se contiver mais de 10 chamadas UDF.

Exemplos

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

O exemplo seguinte cria uma função inline com valores de tabela que devolve informações-chave sobre módulos, filtrando pelo objectType parâmetro. Inclui um valor padrão para devolver todos os módulos quando chamas a função com o DEFAULT parâmetro. Este exemplo utiliza algumas das visualizações de catálogo do sistema mencionadas em Metadados.

CREATE FUNCTION dbo.ModulesByType (@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN (
        SELECT sm.object_id AS 'Object Id',
            o.create_date AS 'Date Created',
            OBJECT_NAME(sm.object_id) AS 'Name',
            o.type AS 'Type',
            o.type_desc AS 'Type Description',
            sm.DEFINITION AS 'Module Description',
            sm.is_inlineable AS 'Inlineable'
        FROM sys.sql_modules AS sm
        INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
        WHERE o.type LIKE '%' + @objectType + '%'
        );
GO

Chame a função para devolver todas as funções de tabela em linha (IF):

SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION

Ou, localize todas as funções escalares (FN):

SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION

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

Este exemplo simples utiliza o TVF inline previamente criado para demonstrar como pode combinar os seus resultados com outras tabelas usando CROSS APPLY. Aqui, seleciona todas as colunas de ambos sys.objects e os resultados de ModulesByType para todas as linhas que correspondem na type coluna. Para mais informações sobre a utilização APPLYde , veja a cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO

C. Criar uma função UDF escalar

O exemplo a seguir cria um UDF escalar inlineável que mascara um texto de entrada.

CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
    RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @Result VARCHAR(50);
        DECLARE @CleanedInput VARCHAR(50);

        -- Trim whitespace
        SET @CleanedInput = LTRIM(RTRIM(@InputString));

        -- Handle empty or null input
        IF @CleanedInput = '' OR @CleanedInput IS NULL
        BEGIN
            SET @Result = '';
        END
        ELSE IF LEN(@CleanedInput) <= 2
        BEGIN
            -- If string length is 1 or 2, just return the cleaned string
            SET @Result = @CleanedInput;
        END
        ELSE
        BEGIN
            -- Construct the masked string
            SET @Result = 
                LEFT(@CleanedInput, 1) +
                REPLICATE('*', LEN(@CleanedInput) - 2) +
                RIGHT(@CleanedInput, 1);
        END

        RETURN @Result
    END

Você pode chamar a função assim:

DECLARE @input varchar(100) = '123456789';

SELECT dbo.cleanInput (@input) AS function_output;

Mais exemplos de como você pode usar UDFs escalares no Fabric Data Warehouse:

Em comunicado SELECT :

SELECT TOP 10 
t.id, t.name, 
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;

WHERE Numa cláusula:

 SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

JOIN Numa cláusula:

SELECT t1.id, t1.name, 
     dbo.cleanInput (t1.name) AS function_output, 
     dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
    INNER JOIN dbo.MyTable2 AS t2 
        ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);

ORDER BY Numa cláusula:

SELECT  t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;

No Warehouse, também pode chamar UDFs escalares a partir de instruções de manipulação de dados como INSERT, UPDATE, ou DELETE. Estes exemplos de modificação de dados não se aplicam ao endpoint de análise SQL.

SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output 
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;

UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;

DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';

D. Criar uma função externa

O exemplo seguinte cria uma função externa nomeada geohash no dbo esquema que faz referência à geohash Fabric User Data Function no spatial_functions conjunto de funções. Como as definições de parâmetros e o tipo de retorno são inferidos a partir da Fabric User Data Function referenciada, não precisam de ser especificados explicitamente na CREATE FUNCTION instrução.

CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;

Uma vez criada, a função externa pode ser usada em consultas T-SQL tal como qualquer outra função definida pelo utilizador ou incorporada. Pode ser referenciado onde quer que sejam suportadas chamadas de função, permitindo a integração perfeita das Funções de Dados de Utilizador Fabric no código T-SQL.

E. Crie um UDF escalar para execução autónoma

A criação de tabelas e a configuração de carregamento de dados a seguir requerem o Warehouse. Num endpoint de análise SQL, utilize uma tabela de origem já existente com as colunas e dados necessários.

CREATE TABLE dbo.Customer
(
    CustomerID INT NOT NULL,
    CustomerName VARCHAR(100) NOT NULL,
    CustomerType VARCHAR(20) NOT NULL,
    MemberSince DATE NOT NULL,
    YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO

INSERT INTO dbo.Customer
(
    CustomerID,
    CustomerName,
    CustomerType,
    MemberSince,
    YearToDateRevenue
)
VALUES
    (1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
    (2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
    (3, 'Customer 3', 'VIP',     '2024-03-10', 36000.00);
GO

A função abaixo combina o acesso à tabela com GETUTCDATE(). O acesso à tabela impede o inlining do Bloco de Expressão e GETUTCDATE() impede o inlining Scalar UDF.

Sem WITH INLINE = AUTO, a definição é rejeitada na criação ou alteração. O erro indica que a função não pode ser inlineada e apresenta as razões de rejeição para ambas as técnicas. Especifique WITH INLINE = AUTO para permitir a criação:

CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
    DECLARE @MemberSince DATE;

    SELECT @MemberSince = MemberSince
    FROM dbo.Customer
    WHERE CustomerID = @CustomerID;

    RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO

A função é criada mas permanece não inlineável: is_inlineable e inline_eligibility_mask são ambos 0. Uma chamada autónoma tem sucesso:

DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';

Chamar a função numa consulta sobre uma tabela de utilizador não é suportado:

SELECT
    CustomerID,
    dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;

Esta consulta retorna Scalar UDF execution is currently unavailable in this context.

Sugestão

Para utilizar esta lógica numa consulta sobre tabelas de utilizador, avalie o valor em tempo atual no chamador e transmita-o como parâmetro. Para a função reescrita e a consulta de chamada, consulte Passar um valor dependente do tempo para um UDF de acesso a dados.

Aplica-se a:Azure Synapse Analytics

Cria uma função definida pelo utilizador (UDF) no Azure Synapse Analytics. Uma função definida pelo usuário é uma rotina Transact-SQL que aceita parâmetros, executa uma ação, como um cálculo complexo, e retorna o resultado dessa ação como um valor. As funções com valor de tabela (TVFs) definidas pelo usuário retornam um tipo de dados de tabela.

Sugestão

Para sintaxe em Fabric Data Warehouse, veja a versão de CREATE FUNCTION for Fabric Data Warehouse.

  • No Azure Synapse Analytics, CREATE FUNCTION pode retornar uma tabela usando a sintaxe para funções com valor de tabela embutido (visualização) ou pode retornar um único valor usando a sintaxe para funções escalares.

  • Em pools SQL sem servidor no Azure Synapse Analytics, pode criar funções de valor de tabela embutidas, CREATE FUNCTION mas não funções escalares.

    Use esta declaração para criar uma rotina reutilizável que possa usar das seguintes formas:

  • 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 definir uma restrição CHECK em uma coluna

  • Para substituir um procedimento armazenado

  • Usar uma função embutida como um predicado de filtro para uma diretiva de segurança

Transact-SQL Convenções sintáticas

Sintaxe

Sintaxe da função escalar

-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics)
-- Not available in the serverless SQL pools in Azure Synapse Analytics

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

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

Sintaxe da função com valor de tabela embutido

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Argumentos

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 das funções devem seguir as regras dos identificadores e ser únicos dentro da base de dados e do seu esquema.

Observação

Deve incluir parênteses após o nome da função, mesmo que não especifique um parâmetro.

@ parameter_name

Um parâmetro na função definida pelo usuário. Pode declarar um ou mais parâmetros.

Uma função pode ter até 2.100 parâmetros. Quando um utilizador ou aplicação chama uma função, deve fornecer o valor para cada parâmetro declarado, a menos que defina um valor 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 seguir as regras dos identificadores. Os parâmetros são locais à função; Podes usar os mesmos nomes de parâmetros noutras funções. Os parâmetros só podem substituir constantes; não podem ser usados em vez de nomes de tabelas, nomes de colunas ou nomes de outros objetos de base de dados.

Observação

ANSI_WARNINGS não é respeitado quando passa parâmetros num procedimento armazenado, numa função definida pelo utilizador ou quando declara e define variáveis numa instrução batch. Por exemplo, se definir uma variável como char(3) e depois a definir para um valor superior a três caracteres, os dados são truncados para o tamanho definido e a INSERT instrução ou UPDATE tem sucesso.

parameter_data_type

O tipo de dados do parâmetro. Para funções Transact-SQL, todos os tipos de dados escalares suportados no Azure Synapse Analytics são permitidos. O tipo de dado timestamp (rowversion) não é um tipo suportado.

[ = padrão ]

Um valor padrão para o parâmetro. Se definir um valor por defeito , pode executar a função sem especificar um valor para esse parâmetro.

Quando um parâmetro da função tem um valor predefinido, deve especificar a palavra-chave DEFAULT ao chamar a função para recuperar o valor predefinido. 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.

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 escalares suportados no Azure Synapse Analytics são permitidos. O tipo de datade timestamp da / não é um tipo suportado. Os tipos não escalares de cursor e tabela não são permitidos.

function_body

Série de declarações Transact-SQL. O function_body não pode conter uma SELECT instrução nem referenciar dados da base de dados. A function_body não pode consultar tabelas ou vistas. O corpo de funções pode chamar outras funções determinísticas, mas não pode chamar funções não determinísticas.

Em funções escalares, function_body é uma série de Transact-SQL instruções que, juntas, avaliam até um valor escalar.

scalar_expression

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

select_stmt

A instrução única SELECT que define o valor de retorno de uma função com valor de tabela embutido. Para uma função com valores de tabela em linha, não existe corpo de funções; a tabela é o conjunto de resultados de uma única SELECT afirmação.

TABLE

Especifica que o valor de retorno da função com valor de tabela (TVF) é uma tabela. Só podes passar constantes e @local_variables aos TVFs.

Nos TVFs inline (pré-visualização), define-se o TABLE valor de retorno através de uma única SELECT instrução. As funções embutidas não têm variáveis de retorno associadas.

<function_option>

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

SCHEMABINDING

Especifica que a função está vinculada aos objetos de banco de dados aos quais faz referência. Quando especificas SCHEMABINDING, não podes modificar os objetos subjacentes (como uma vista ou uma tabela, por exemplo) de uma forma que afete a definição da função. Deve primeiro modificar ou eliminar a definição da função para remover dependências do objeto que pretende modificar.

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

  • Deixas de usar a função.

  • Usa ALTER a declaração de função e remove a SCHEMABINDING opção.

Só pode vincular um esquema a uma função se as seguintes condições forem verdadeiras:

  • Quaisquer funções definidas pelo utilizador que a função referenciam também são limitadas ao esquema.

  • As referências de funções usam nomes de uma ou duas partes.

  • No corpo dos UDFs, só pode referenciar funções incorporadas e outros UDFs na mesma base de dados.

  • O utilizador que executa a CREATE FUNCTION instrução tem permissão REFERENCES sobre os objetos da base de dados que a função referencia.

Para remover SCHEMABINDING, use ALTER.

DEVOLVE NULL NA ENTRADA NULA | CHAMADA NA ENTRADA NULA

Especifica o OnNULLCall atributo de uma função com valor escalar. Se não especificares este atributo, CALLED ON NULL INPUT está implícito por defeito, e o corpo da função executa-se mesmo que NULL seja passado como argumento.

Melhores práticas

Se não criar uma função definida pelo utilizador com a cláusula SCHEMABIND, alterações aos objetos subjacentes podem afetar a definição da função e causar resultados inesperados quando a invoca. Especifica a WITH SCHEMABINDING cláusula quando criares a função. Esta cláusula garante que não podes modificar os objetos referenciados na definição da função, a menos que também modifiques a função.

Interoperabilidade

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

  • Declarações de atribuição.

  • Instruções de controlo do fluxo, exceto TRY... Declarações CATCH.

  • Instruções DECLARE que definem variáveis locais de dados.

Numa função com valores de tabela inline (pré-visualização), só pode usar uma única instrução select.

Limitações

Não podes usar funções definidas pelo utilizador para realizar ações que modifiquem o estado da base de dados.

Podes aninhar funções definidas pelo utilizador. Uma função definida pelo utilizador pode chamar outra. O nível de aninhamento incrementa quando a função chamada inicia a execução, e diminui quando a função chamada termina a execução. Se ultrapassar os níveis máximos de nesting, toda a cadeia de funções de chamada falha.

Não pode criar objetos, incluindo funções, na master base de dados do seu pool SQL serverless no Azure Synapse Analytics.

Metadados

Esta seção lista as exibições do catálogo do sistema que você pode usar para retornar metadados sobre funções definidas pelo usuário.

  • sys.sql_modules: Exibe a definição de Transact-SQL funções definidas pelo utilizador. Por exemplo:

    SELECT definition, type   
    FROM sys.sql_modules AS m  
    JOIN sys.objects AS o   
        ON m.object_id = o.object_id   
        AND type = ('FN');
    
  • sys.parameters: Exibe informações sobre os parâmetros definidos em funções definidas pelo utilizador.

  • sys.sql_expression_dependencies: Exibe os objetos subjacentes referenciados por uma função.

Permissões

Requer CREATE FUNCTION permissão na base de dados e permissão ALTER no esquema onde a função está a ser criada.

Exemplos

Um. Usar uma função definida pelo usuário com valor escalar para alterar um tipo de dados

Esta função simples recebe um tipo de dado int como entrada e devolve um tipo de dado decimal(10,2) como saída.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  

SELECT dbo.ConvertInput(15) AS 'ConvertedValue';  

Observação

Funções escalares não estão disponíveis em pools SQL serverless.

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

O exemplo seguinte cria uma função inline com valores de tabela que devolve informações-chave sobre módulos, filtrando pelo objectType parâmetro. Inclui um valor padrão para devolver todos os módulos quando chamas a função com o DEFAULT parâmetro. Este exemplo utiliza algumas das visualizações de catálogo do sistema mencionadas em Metadados.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

Pode chamar a função para devolver todos os objetos da vista (V) com:

select * from dbo.ModulesByType('V');

Observação

As funções de valor de tabela em linha estão disponíveis em pools SQL serverless, mas em pré-visualização nos pools SQL dedicados.

C. Combinar resultados de uma função com valor de tabela embutido

Este exemplo simples utiliza o TVF inline previamente criado para demonstrar como pode combinar os seus resultados com outras tabelas usando CROSS APPLY. Neste exemplo, seleciona todas as colunas tanto de sys.objects como dos resultados de ModulesByType para todas as linhas que correspondem na type coluna. Para mais informações sobre a utilização APPLYde , veja a cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL).

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Observação

As funções de valor de tabela em linha estão disponíveis em pools SQL serverless, mas em pré-visualização nos pools SQL dedicados.

Próximo passo