CREATE FUNCTION

Se aplica a:Endpoint de analítica SQL en Microsoft Fabric y Warehouse en Microsoft Fabric

CREATE FUNCTION crea funciones de valor de tabla en línea y funciones escalares.

Nota

Los UDFs externos son una función de vista previa en Fabric Data Warehouse.

Este artículo se aplica a Microsoft Fabric Data Warehouse y al endpoint de analítica SQL de Fabric elementos. Para otras plataformas, véaseCREATE FUNCTION (Transact-SQL).

Una función definida por el usuario es una rutina Transact-SQL que acepta parámetros, realiza una acción como un cálculo complejo y devuelve el resultado de esa acción como valor. Las funciones escalares devuelven un valor escalar, como un número o una cadena. Las funciones con valores de tabla (TVF) definidas por el usuario devuelven una tabla.

Úsalo CREATE FUNCTION para crear una rutina T-SQL reutilizable que puedas emplear de estas maneras:

  • En Transact-SQL afirmaciones como SELECT.
  • En Warehouse, en Transact-SQL sentencias de manipulación de datos (DML) como UPDATE, INSERT, y DELETE.
  • En aplicaciones que llaman a la función.
  • En la definición de otra función definida por el usuario.
  • Para reemplazar un procedimiento almacenado.

Especificar CREATE OR ALTER FUNCTION crear una nueva función si no existe con ese nombre, o alterar una función existente, en una sola sentencia.

Transact-SQL Convenciones sintácticas

Sintaxis

Sintaxis de función 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 ]
}

Sintaxis de función con valores de tabla insertada

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

Sintaxis de funciones externas

Un UDF externo es una función que hace referencia a una función de datos de usuario Fabric externa.

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

Nota

Los UDFs externos son una función de vista previa en Fabric Data Warehouse.

La CREATE FUNCTION instrucción deriva automáticamente el tipo de retorno, los parámetros y los tipos de parámetros a partir de la definición de la función de datos de usuario externa de Fabric referenciada. Si cambia la firma de la Función de Datos de Usuario Fabric externa, debes recrear la función para sincronizar su definición con la firma actualizada.

Argumentos

schema_name

Nombre del esquema al que pertenece la función definida por el usuario.

function_name

Nombre de la función definida por el usuario. Los nombres de las funciones deben seguir las reglas de los identificadores y ser únicos dentro de la base de datos y su esquema.

Para definiciones de funciones escalares nativas T-SQL y de tabla en línea, incluya paréntesis después del nombre de la función incluso cuando no haya parámetros. Las definiciones de funciones externas utilizan la sintaxis separada AS EXTERNAL FUNCTION .

@ parameter_name

Parámetro de la función definida por el usuario. Puedes declarar uno o más parámetros.

Una función puede tener hasta 2.100 parámetros. Cuando un usuario o aplicación llama a una función, debes proporcionar el valor de cada parámetro declarado a menos que definas un valor por defecto para el parámetro.

Especifique un nombre de parámetro con una arroba ( @ ) como primer carácter. El nombre del parámetro debe seguir las reglas de identificadores. Los parámetros son locales para la función. Puedes usar los mismos nombres de parámetros en otras funciones. Los parámetros solo pueden reemplazar constantes. No pueden usarse en lugar de nombres de tablas, nombres de columnas o nombres de otros objetos de base de datos.

ANSI_WARNINGS No se respeta cuando pasas parámetros en un procedimiento almacenado, una función definida por el usuario o cuando declaras y estableces variables en una sentencia por lotes. Por ejemplo, si defines una variable como char(3) y luego la asignas a un valor mayor que tres caracteres, los datos se truncan al tamaño definido y la instrucción SQL tiene éxito.

parameter_data_type

Tipo de datos de parámetro. Para Transact-SQL funciones, se permiten todos los tipos de datos escalares admitidos .

[ = predeterminado ]

Valor predeterminado del parámetro. Si defines un valor por defecto , puedes ejecutar la función sin especificar un valor para ese parámetro.

Cuando un parámetro de la función tiene un valor por defecto, debes especificar la palabra clave DEFAULT al llamar a la función para recuperar el valor por defecto. Este comportamiento es distinto del uso de parámetros con valores predeterminados en los procedimientos almacenados, donde la omisión del parámetro implica especificar el valor predeterminado.

return_data_type

Valor devuelto de una función escalar definida por el usuario.

Para funciones en Fabric Data Warehouse, puedes usar todos los tipos de datos excepto lamarca de tiempode la versión/ de filas. No se permiten tipos no escalares como tablas .

Para UDFs externos (Vista previa), la RETURNS cláusula es opcional. Si se omite, el tipo de retorno se infiere automáticamente a partir del tipo de retorno de la función de datos de usuario de Fabric referenciada. Puedes especificar la RETURNS cláusula para anular el tipo de retorno inferido cuando sea necesario.

function_body

Una serie de instrucciones Transact-SQL.

En las funciones escalares, function_body es una serie de instrucciones de Transact-SQL que se evalúan conjuntamente como un valor escalar, que puede incluir:

  • Expresión de instrucción única
  • Expresiones de varias instrucciones (IF/ELSE y BEGIN/END bloques)
  • Variables locales
  • Llamadas a funciones SQL integradas disponibles
  • Llamadas a otras UDF
  • SELECT sentencias y referencias a tablas, vistas y funciones de valor de tabla en línea
  • Sentencias de control de flujo, incluyendo IF/ELSE, WHILE, BREAK, CONTINUE, y RETURN

Para UDFs externos (Preview), no puedes especificar el function_body porque la implementación de la función se define externamente en la función de datos de usuario Fabric asociada. La base de datos almacena únicamente los metadatos de la función. La lógica ejecutable reside en la definición de la función externa.

scalar_expression

Especifica el valor escalar que devuelve la función escalar.

select_stmt

Instrucción única SELECT que define el valor devuelto de una función insertada con valores de tabla. Para una función de valor en tabla en línea, no existe cuerpo de función. La tabla es el conjunto de resultados de una única SELECT sentencia.

TABLE

Especifica que el valor devuelto de la función con valores de tabla (TVF) es una tabla. Solo puedes pasar constantes y @local_variables a TVFs.

En los TVF en línea (previsualización), defines el TABLE valor de retorno mediante una sola SELECT sentencia. Las funciones en línea no tienen variables de retorno asociadas.

<function_option>

En Fabric Data Warehouse, las ENCRYPTION palabras clave y EXECUTE AS no están soportadas.

Las opciones de función soportadas incluyen:

EN LÍNEA = AUTO

Especifica si una función escalar definida por el usuario puede ser creada o modificada independientemente de los requisitos de inlining. La INLINE cláusula es opcional. Para una UDF escalar inlineable, especificar INLINE = AUTO no cambia su inlineabilidad ni su comportamiento de ejecución.

Para un patrón de llamada independiente compatible, consulte Crear un UDF escalar para ejecución independiente.

SCHEMABINDING

Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando especificas SCHEMABINDING, no puedes modificar los objetos subyacentes (como una vista o una tabla, por ejemplo) de una manera que afecte a la definición de la función. Primero debes modificar o eliminar la definición de función para eliminar dependencias del objeto que quieres modificar.

El enlace de la función a los objetos a los que hace referencia solamente se quita cuando se ejecuta una de estas acciones:

  • Dejas la función.

  • Tú ALTER usas la declaración de función y eliminas la SCHEMABINDING opción.

Solo puedes asignar un esquema a una función si se cumplen las siguientes condiciones:

  • Cualquier función definida por el usuario a la que la función haga referencia también está limitada al esquema.

  • La función hace referencia a los objetos usando un nombre de dos partes.

  • Dentro del cuerpo de UDFs, solo puedes referenciar funciones integradas y otros UDFs en la misma base de datos.

  • El usuario que ejecuta la CREATE FUNCTION instrucción tiene permiso REFERENCES sobre los objetos de base de datos a los que la función hace referencia.

Para quitar SCHEMABINDING, use ALTER.

DEVUELVE NULL EN UNA ENTRADA NULA | LLAMADO EN UNA ENTRADA NULA

Especifica el OnNULLCall atributo de una función con valores escalares. Si no especificas este atributo, CALLED ON NULL INPUT está implícito por defecto, y el cuerpo de la función se ejecuta incluso si NULL se pasa como argumento.

COMO EXTERNO FUNCTION

Hace referencia a una Función de Datos de Usuario Fabric (UDF). Cuando se invoca la función, la ejecución se delega a la Fabric User Data Function referenciada, donde se ejecuta la lógica y el resultado se devuelve al llamador T-SQL.

Esta cláusula acepta los siguientes parámetros:

  • Nombre del conjunto de funciones: El nombre del conjunto de funciones que contiene la función de datos de usuario Fabric. Cada función de datos de usuario de Fabric debe pertenecer a un conjunto de funciones.
  • Nombre de la función: El nombre de la función de datos de usuario de Fabric a la que se debe referenciar.

La siguiente sintaxis muestra cómo se especifican estos parámetros:

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

Como la función se ejecuta remotamente, llamar a una función externa suele tener una latencia mayor que ejecutar una función nativa T-SQL definida por el usuario. Utiliza funciones externas cuando la lógica de negocio requerida esté implementada en Fabric y no pueda expresarse directamente en T-SQL.

La CREATE FUNCTION instrucción deriva automáticamente el tipo de retorno, los parámetros y los tipos de parámetros a partir de la definición de la función de datos de usuario externa de Fabric referenciada. Si cambia la firma de la Función de Datos de Usuario Fabric externa, debes recrear la función para sincronizar su definición con la firma actualizada.

procedimientos recomendados

  • Escribe tus funciones definidas por el usuario para que sean inlineables. Para más información sobre el concepto de inlining, véase Inlining of scalar UDF. Para ejemplos de cómo hacer que una UDF escalar sea inlineable, véase Crear UDF escalar en Microsoft Fabric Data Warehouse.

  • Si no creas una función definida por el usuario con schemabinding, los cambios en los objetos subyacentes pueden afectar la definición de la función y causar resultados inesperados al invocar la función. Cuando especificas WITH SCHEMABINDING cuándo creas la función, te aseguras de que los cambios posteriores en los objetos subyacentes no puedan modificar ni romper el comportamiento de la función.

  • Siempre que sea posible, implementa tu lógica como una función definida por el usuario (UDF) de T-SQL. Utiliza un UDF externo solo para funcionalidades que no pueden implementarse en el lenguaje T-SQL. Este enfoque ayuda a minimizar la sobrecarga de ejecución remota y, en general, proporciona un mejor rendimiento de las consultas.

Interoperabilidad

Funciones definidas por el usuario con valores de tabla insertadas

Una función con valores en tabla en línea acepta solo una sentencia.SELECT

Funciones escalares definidas por el usuario

  • Cuando un UDF escalar no es lineable, CREATE la instrucción OR ALTER rechazaría la instrucción DDL con un mensaje de error personalizado.

  • La función no inlineable puede crearse especificando INLINE=AUTO la opción de la función. No se puede usar una función no inlineable en una SELECT ... FROM consulta en una tabla de usuario. INLINE=AUTO no hace que la función sea inlineable.

  • Las siguientes instrucciones son válidas en una función con valores escalares:

    • Instrucciones de asignación.
    • Sentencias de control de flujo excepto TRY...CATCH y GOTO sentencias.
    • DECLARE Sentencias que definen variables de datos locales.
    • Llamadas a funciones integradas.
    • Referencias a tablas/vistas/iTVFs/otros UDFs escalares.
  • Las sentencias DML no están permitidas en funciones escalares definidas por el usuario.

Metadatos

En esta sección se enumeran las vistas de catálogo del sistema que se pueden usar para devolver metadatos sobre funciones definidas por el usuario.

  • sys.sql_modules: Muestra la definición de Transact-SQL funciones definidas por el usuario, así como información de inlineabilidad. Por ejemplo:

      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: Muestra información sobre los parámetros definidos en las funciones definidas por el usuario. El siguiente ejemplo utiliza la sys.parameters vista de catálogo para mostrar firmas de funciones, incluyendo tipos de retorno y tipos de datos 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: Muestra los objetos subyacentes referenciados por una función.

Permisos

Los miembros de los roles Administrador, Miembro y Colaborador del área de trabajo de Fabric pueden crear funciones.

Enlineamiento de la UDF escalar

Microsoft Fabric Data Warehouse automáticamente alinea los UDF escalares elegibles durante la compilación de consultas.

El inlining de bloques de expresión compila las sentencias de la función, incluyendo ramas y bucles, en una expresión escalar que se ejecuta como parte de la consulta que llama. La línea UDF escalar transforma el cuerpo de la función en expresiones o subconsultas como parte de la consulta que llama.

Una función puede cumplir los requisitos para ambas técnicas, pero el motor selecciona una en función de su definición. Diferentes funciones dentro de la misma consulta pueden utilizar distintas técnicas.

A medida que Microsoft Fabric Data Warehouse amplía sus capacidades de inlining, las funciones existentes las adquieren automáticamente.

Constructos de funciones compatibles

La siguiente tabla compara las construcciones de cuerpo función compatibles.

Construcción función-cuerpo Inlining de bloques de expresión Inserción de funciones definidas por el usuario (UDF) escalar
Expresiones escalares, variables locales y IF/ELSE Supported Supported
WHILE bucles, incluyendo BREAK y CONTINUE Supported No soportado
Varias RETURN instrucciones Supported No soportado
NEWID() Supported No soportado
Funciones dependientes del tiempo (por ejemplo, GETDATE() y SYSDATETIME()) Supported No soportado
Acceso TVF en tabla, vista o en línea No soportado Supported
Llamadas a UDFs escalares* No soportado Supported

* Las funciones definidas por el usuario pueden anidarse: una UDF puede llamar a otra. El nivel de anidamiento aumenta cuando comienza una función llamada y disminuye cuando termina. Microsoft Fabric Data Warehouse admite hasta cuatro niveles de anidamiento cuando un cuerpo UDF hace referencia a una tabla, vista o TVF en línea, o hasta 32 niveles en caso contrario. Superar el límite de anidamiento provoca que la cadena de funciones que llama falle.

Funciones integradas

El soporte para funciones integradas, incluidas las funciones del sistema, difiere entre las dos técnicas de inlining.

Por ejemplo, crear una UDF que use @@ROWCOUNT sin INLINE = AUTO devolver el error 19841. El mensaje de error incluye el siguiente texto:

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

Para una definición de función específica, consulte Comprobar si una UDF escalar puede ser en línea.

Consultas de llamadas compatibles

La siguiente tabla compara el soporte de consultas de llamada para UDF escalares inlineables.

Característica de consulta Inlining de bloques de expresión Inserción de funciones definidas por el usuario (UDF) escalar
UDF llama en la SELECT lista, WHERE, o JOIN ... ON Supported Supported
Llamadas directas UDF en GROUP BY o HAVING Supported No soportado
Llamadas directas de la UDF ORDER BY Supported No soportado
CTEs en la consulta de llamada Supported No soportado
ROLLUP, CUBE, o GROUPING SETS No soportado No soportado

Si cualquier llamada UDF depende de la inlinación Scalar UDF, toda la consulta debe cumplir los requisitos de llamada de esa técnica.

Con la línea Scalar UDF, proyecta el resultado de la UDF en la SELECT lista y el orden por su alias de columna u ordinal en lugar de llamar directamente a la UDF en ORDER BY. Para ejemplos de reescritura de consultas, consulte Adaptar una consulta de llamada para Scalar UDF inlining. Para otros límites de ejecución, consulte Limitaciones.

Comprobar si se puede insertar una UDF escalar

La sys.sql_modules vista de catálogo describe la elegibilidad basada en la definición de la función. is_inlineable es 1 para una definición inlineable y 0 en otros casos. inline_eligibility_mask identifica las técnicas elegibles:

inline_eligibility_mask Elegibilidad para definiciones
0 Ninguna técnica
1 Inserción de funciones definidas por el usuario (UDF) escalar
2 Inlining de bloques de expresión
3 Cualquiera de las dos técnicas

Estos valores describen la definición de la función, no el plan de ejecución de una consulta concreta.

Use la siguiente consulta de ejemplo para comprobar si un UDF escalar es inlineable:

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');

Limitaciones

  • Llamar a una UDF escalar en un contexto de consulta no soportado puede devolver el error Scalar UDF execution is currently unavailable in this context.
  • Una consulta que se basa en la inlinación Scalar UDF puede fallar si contiene más de 10 llamadas UDF.

Ejemplos

A Creación de una función alineada con valores de tabla

El siguiente ejemplo crea una función en línea con valores de tabla que devuelve información clave sobre módulos, filtrando por el objectType parámetro. Incluye un valor por defecto para devolver todos los módulos cuando llamas a la función con el DEFAULT parámetro. Este ejemplo utiliza algunas de las vistas de catálogo del sistema mencionadas en Metadatos.

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

Llama a la función para devolver todas las funciones en línea con valores en tabla (IF):

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

O bien, busque todas las funciones escalares (FN):

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

B. Combinación de resultados de una función con valores de tabla insertados

Este ejemplo sencillo utiliza el TVF en línea creado previamente para demostrar cómo puedes combinar sus resultados con otras tablas usando CROSS APPLY. Aquí, seleccionas todas las columnas de ambos sys.objects y los resultados de ModulesByType para todas las filas que coinciden en la type columna. Para más información sobre el uso APPLYde , véase la cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL).

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

C. Creación de una función UDF escalar

En el ejemplo siguiente se crea una UDF escalar insertable que enmascara un 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

Puede llamar a la función de la siguiente manera:

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

SELECT dbo.cleanInput (@input) AS function_output;

Más ejemplos de cómo puede usar UDF escalares en Fabric Data Warehouse:

En una SELECT instrucción :

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

En una WHERE 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';

En una JOIN 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);

En una ORDER BY cláusula :

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

En Warehouse, también puedes llamar a UDF escalares a partir de sentencias de manipulación de datos como INSERT, UPDATE, o DELETE. Estos ejemplos de modificación de datos no se aplican al endpoint de analítica 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. Crear una función externa

El siguiente ejemplo crea una función externa nombrada geohash en el dbo esquema que hace referencia a la geohash Función de Datos de Usuario Fabric en el spatial_functions conjunto de funciones. Como las definiciones de parámetros y el tipo de retorno se infieren a partir de la función de datos de usuario Fabric referenciada, no es necesario especificarlos explícitamente en la CREATE FUNCTION sentencia.

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

Una vez creada, la función externa puede usarse en consultas T-SQL igual que cualquier otra función definida por el usuario o incorporada. Puede consultarse en cualquier lugar donde se soporten llamadas a funciones, permitiendo una integración fluida de las Funciones de Datos de Usuario Fabric en el código T-SQL.

E. Crear un UDF escalar para ejecución independiente

La siguiente configuración de creación de tablas y carga de datos requiere Warehouse. En un endpoint de analítica SQL, utilice una tabla respaldada por código fuente existente con las columnas y datos requeridos.

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

La función siguiente combina el acceso a la tabla con GETUTCDATE(). El acceso a la tabla impide la línea en línea del bloque de expresión y GETUTCDATE() evita la línea en línea Escalar de la UDF.

Sin WITH INLINE = AUTO, la definición se rechaza en la creación o alteración. El error indica que la función no puede ser inlineada y da las razones de rechazo para ambas técnicas. Especifica WITH INLINE = AUTO para permitir la creación:

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

La función se crea pero permanece no inlineable: is_inlineable y inline_eligibility_mask son ambos 0. Una llamada independiente tiene éxito:

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

No se admite llamar a la función en una consulta sobre una tabla de usuario:

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

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

Sugerencia

Para utilizar esta lógica en una consulta sobre tablas de usuario, evalúe el valor en tiempo actual en el llamador y pásalo como parámetro. Para la función reescrita y la consulta de llamada, consulte Pasar un valor dependiente del tiempo a una UDF de acceso a datos.

Aplica a:Azure Synapse Analytics

Crea una función definida por el usuario (UDF) en Azure Synapse Analytics. Una función definida por el usuario es una rutina de Transact-SQL que acepta parámetros, realiza una acción, como un cálculo complejo, y devuelve el resultado de esa acción como un valor. Las funciones con valores de tabla definidas por el usuario (TVF) devuelven un tipo de datos table.

Sugerencia

Para la sintaxis en Fabric Data Warehouse, véase la versión de CREATE FUNCTION for Fabric Data Warehouse.

  • En Azure Synapse Analytics, CREATE FUNCTION puede devolver una tabla mediante la sintaxis de las funciones con valores de tabla insertados (versión preliminar) o puede devolver un único valor mediante la sintaxis de las funciones escalares.

  • En los grupos de SQL sin servidor de Azure Synapse Analytics, CREATE FUNCTION puede crear funciones de valores de tabla insertadas, pero no funciones escalares.

    Utiliza esta frase para crear una rutina reutilizable que puedas emplear de estas maneras:

  • En instrucciones Transact-SQL, como SELECT

  • En las aplicaciones que llaman a la función

  • En la definición de otra función definida por el usuario

  • Para definir una restricción CHECK en una columna

  • Para reemplazar un procedimiento almacenado

  • Usar una función insertada como predicado de filtro de la directiva de seguridad

Transact-SQL Convenciones sintácticas

Sintaxis

Sintaxis de función 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 ]
}

Sintaxis de función con valores de tabla insertada

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

Nombre del esquema al que pertenece la función definida por el usuario.

function_name

Nombre de la función definida por el usuario. Los nombres de las funciones deben seguir las reglas de los identificadores y ser únicos dentro de la base de datos y su esquema.

Nota

Debes incluir paréntesis después del nombre de la función aunque no especifiques un parámetro.

@ parameter_name

Parámetro de la función definida por el usuario. Puedes declarar uno o más parámetros.

Una función puede tener hasta 2.100 parámetros. Cuando un usuario o aplicación llama a una función, debes proporcionar el valor de cada parámetro declarado a menos que definas un valor por defecto para el parámetro.

Especifique un nombre de parámetro con una arroba ( @ ) como primer carácter. El nombre del parámetro debe seguir las reglas de identificadores. Los parámetros son locales a la función; Puedes usar los mismos nombres de parámetros en otras funciones. Los parámetros solo pueden sustituir constantes; No pueden usarse en lugar de nombres de tablas, columnas o nombres de otros objetos de base de datos.

Nota

ANSI_WARNINGS No se respeta cuando pasas parámetros en un procedimiento almacenado, una función definida por el usuario o cuando declaras y estableces variables en una sentencia por lotes. Por ejemplo, si defines una variable como char(3) y luego la asignas a un valor mayor que tres caracteres, los datos se truncan al tamaño definido y la INSERT instrucción o UPDATE tiene éxito.

parameter_data_type

Tipo de datos de parámetro. En las funciones de Transact-SQL, se permiten todos los tipos de datos escalares admitidos en Azure Synapse Analytics. El tipo de dato de marca de tiempo (rowversion) no es un tipo soportado.

[ = predeterminado ]

Valor predeterminado del parámetro. Si defines un valor por defecto , puedes ejecutar la función sin especificar un valor para ese parámetro.

Cuando un parámetro de la función tiene un valor por defecto, debes especificar la palabra clave DEFAULT al llamar a la función para recuperar el valor por defecto. Este comportamiento es distinto del uso de parámetros con valores predeterminados en los procedimientos almacenados, donde la omisión del parámetro implica especificar el valor predeterminado.

return_data_type

Valor devuelto de una función escalar definida por el usuario. En las funciones de Transact-SQL, se permiten todos los tipos de datos escalares admitidos en Azure Synapse Analytics. El tipo dedata de la marca/ no es un tipo soportado. No se permiten los tipos no escalares de cursor y tabla.

function_body

Serie de instrucciones de Transact-SQL. El function_body no puede contener una SELECT sentencia ni referenciar datos de la base de datos. La function_body no puede consultar tablas ni vistas. El cuerpo de funciones puede llamar a otras funciones deterministas, pero no puede llamar a funciones no deterministas.

En las funciones escalares, function_body es una serie de instrucciones de Transact-SQL que se evalúan como un valor escalar.

scalar_expression

Especifica el valor escalar que devuelve la función escalar.

select_stmt

Instrucción única SELECT que define el valor devuelto de una función insertada con valores de tabla. Para una función en línea con valores de tablas, no existe cuerpo de funciones; la tabla es el conjunto de resultados de una única SELECT afirmación.

TABLE

Especifica que el valor devuelto de la función con valores de tabla (TVF) es una tabla. Solo puedes pasar constantes y @local_variables a TVFs.

En los TVF en línea (previsualización), defines el TABLE valor de retorno mediante una sola SELECT sentencia. Las funciones en línea no tienen variables de retorno asociadas.

<function_option>

Especifica que la función tiene una o varias de las siguientes opciones.

SCHEMABINDING

Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando especificas SCHEMABINDING, no puedes modificar los objetos subyacentes (como una vista o una tabla, por ejemplo) de una manera que afecte a la definición de la función. Primero debes modificar o eliminar la definición de función para eliminar dependencias del objeto que quieres modificar.

El enlace de la función a los objetos a los que hace referencia solamente se quita cuando se ejecuta una de estas acciones:

  • Dejas la función.

  • Tú ALTER usas la declaración de función y eliminas la SCHEMABINDING opción.

Solo puedes asignar un esquema a una función si se cumplen las siguientes condiciones:

  • Cualquier función definida por el usuario a la que la función haga referencia también está limitada al esquema.

  • Las referencias de funciones utilizan nombres de una o dos partes.

  • Dentro del cuerpo de UDFs, solo puedes referenciar funciones integradas y otros UDFs en la misma base de datos.

  • El usuario que ejecuta la CREATE FUNCTION instrucción tiene permiso REFERENCES sobre los objetos de base de datos a los que la función hace referencia.

Para quitar SCHEMABINDING, use ALTER.

DEVUELVE NULL EN ENTRADA NULL | SE LLAMA A ON NULL INPUT (LLAMADA EN ENTRADA NULL)

Especifica el OnNULLCall atributo de una función con valores escalares. Si no especificas este atributo, CALLED ON NULL INPUT está implícito por defecto, y el cuerpo de la función se ejecuta incluso si NULL se pasa como argumento.

procedimientos recomendados

Si no creas una función definida por el usuario con la cláusula SCHEMABIND, los cambios en los objetos subyacentes pueden afectar la definición de la función y causar resultados inesperados cuando la invoques. Especifica la WITH SCHEMABINDING cláusula cuando crees la función. Esta cláusula asegura que no puedas modificar los objetos referenciados en la definición de función a menos que también modifiques la función.

Interoperabilidad

Las siguientes instrucciones son válidas en una función con valores escalares:

  • Instrucciones de asignación.

  • Sentencias de control de flujo, excepto INTENTAR... Declaraciones CATCH.

  • Sentencias DECLARE que definen variables de datos locales.

En una función de valores de tabla en línea (previsualización), solo puedes usar una única sentencia select.

Limitaciones

No puedes usar funciones definidas por el usuario para realizar acciones que modifiquen el estado de la base de datos.

Puedes anidar funciones definidas por el usuario. Una función definida por el usuario puede llamar a otra. El nivel de anidamiento aumenta cuando la función llamada comienza su ejecución, y disminuye cuando la función llamada termina de ejecución. Si superas los niveles máximos de anidamiento, toda la cadena de funciones de llamada falla.

No puedes crear objetos, incluidas funciones, en la master base de datos de tu pool SQL serverless en Azure Synapse Analytics.

Metadatos

En esta sección se enumeran las vistas de catálogo del sistema que se pueden usar para devolver metadatos sobre funciones definidas por el usuario.

  • sys.sql_modules: Muestra la definición de Transact-SQL funciones definidas por el usuario. Por ejemplo:

    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: Muestra información sobre los parámetros definidos en las funciones definidas por el usuario.

  • sys.sql_expression_dependencies: Muestra los objetos subyacentes referenciados por una función.

Permisos

Requiere CREATE FUNCTION permiso en la base de datos y permiso ALTER sobre el esquema en el que se está creando la función.

Ejemplos

A Uso de una función definida por el usuario con valores escalares para cambiar un tipo de datos

Esta función simple toma un tipo de dato int como entrada y devuelve un tipo de dato decimal(10,2) como salida.

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

Nota

Las funciones escalares no están disponibles en pools SQL serverless.

B. Creación de una función alineada con valores de tabla

El siguiente ejemplo crea una función en línea con valores de tabla que devuelve información clave sobre módulos, filtrando por el objectType parámetro. Incluye un valor por defecto para devolver todos los módulos cuando llamas a la función con el DEFAULT parámetro. Este ejemplo utiliza algunas de las vistas de catálogo del sistema mencionadas en Metadatos.

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

Puedes llamar a la función para devolver todos los objetos de vista (V) con:

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

Nota

Las funciones de valor de tabla en línea están disponibles en pools SQL serverless, pero en versión previa en pools SQL dedicados.

C. Combinación de resultados de una función con valores de tabla insertados

Este ejemplo sencillo utiliza el TVF en línea creado previamente para demostrar cómo puedes combinar sus resultados con otras tablas usando CROSS APPLY. En este ejemplo, seleccionas todas las columnas tanto de sys.objects como de los resultados de ModulesByType para todas las filas que coinciden en la type columna. Para más información sobre el uso APPLYde , véase la cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL).

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

Nota

Las funciones de valor de tabla en línea están disponibles en pools SQL serverless, pero en versión previa en pools SQL dedicados.

Paso siguiente