Inserción de UDF escalar

2025-06-27

Se aplica a: SQL Server 2019 (15.x) Azure SQL Database Instancia administrada de Azure SQL Punto de conexión de análisis SQL en Microsoft Fabric Almacén en Microsoft Fabric

En este artículo se presenta la inserción de UDF escalar, una función del conjunto de funciones Procesamiento inteligente de consultas en bases de datos SQL. Esta función mejora el rendimiento de las consultas que invocan UDF escalares en SQL Server 2019 (15.x) y versiones posteriores.

Funciones escalares definidas por el usuario de T-SQL

Las funciones definidas por el usuario (UDF) que se implementan en Transact-SQL y que devuelven un único valor de datos se conocen como funciones escalares definidas por el usuario de T-SQL. Las UDF de T-SQL son una forma elegante de lograr la reutilización y modularidad del código en todas las consultas de Transact-SQL. Algunos cálculos (como las reglas de negocios complejas) son más fáciles de expresar en forma de UDF imperativa. Las UDF ayudan a crear una lógica compleja, sin necesidad de tener experiencia en escribir consultas de SQL complejas. Para obtener más información sobre las UDF, consulte Crear funciones definidas por el usuario (motor de base de datos).

Rendimiento de las UDF escalares

Las UDF escalares suelen tener un rendimiento deficiente debido a las razones siguientes:

Invocación iterativa. las UDF se invocan de forma iterativa, una vez por cada tupla certificada. Esto incurre en un coste adicional de cambio de contexto repetido debido a la invocación de funciones. En concreto, las UDF que ejecutan consultas de Transact-SQL en su definición se ven gravemente afectadas.
Falta de costos: Durante la optimización, solo se calcula el costo de los operadores relacionales, mientras que el de los operadores escalares no. Antes de la introducción de las UDF escalares, otros operadores escalares eran generalmente baratos y no requerían coste. Un pequeño costo de CPU agregado para una operación escalar era suficiente. Hay escenarios donde el costo real es significativo y, aun así, se sigue representando de forma insuficiente.
Ejecución interpretada: las UDF se evalúan como un lote de instrucciones, y se ejecutan instrucción por instrucción. Se compila cada instrucción y el plan compilado se almacena en caché. Aunque esta estrategia de almacenamiento en caché ahorra algo de tiempo porque evita las recompilaciones, cada instrucción se ejecuta de forma aislada. No se realizan optimizaciones entre instrucciones.
Ejecución en serie: SQL Server no admite el paralelismo entre consultas en las consultas que invocan las UDF.

Inserción automática de UDF escalares

El objetivo de la característica Inserción de UDF escalar es mejorar el rendimiento de las consultas que llaman a UDF escalares de T-SQL, donde la ejecución de la UDF es el principal cuello de botella.

Con esta nueva característica, las UDF escalares se transforman automáticamente en expresiones o subconsultas escalares que se sustituyen en la consulta que realiza la llamada en lugar del operador de UDF. Después, estas expresiones y subconsultas se optimizan. Como resultado, el plan de consulta ya no tiene un operador de función definido por el usuario, pero sus efectos se observan en el plan, como las vistas o las funciones valoradas en tabla (TVF) en línea.

Inserción automática de UDF escalares en Microsoft Fabric Data Warehouse

En Microsoft Fabric Data Warehouse, las UDF escalares (actualmente en versión preliminar) se insertan automáticamente en tiempo de compilación cuando el cuerpo de la función y la consulta de llamada cumplen los requisitos de inserción. Para obtener más información, consulte CREATE FUNCTION y Scalar UDF inlining.

Ejemplos

Los ejemplos de esta sección utilizan la base de datos de referencia TPC-H. Para obtener más información, consulte la Página principal de TPC-H.

A. UDF escalar de instrucción única

Considere la consulta siguiente.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Esta consulta calcula la suma de los precios con descuento para los artículos de línea y presenta los resultados agrupados por fecha de envío y prioridad de envío. La expresión L_EXTENDEDPRICE *(1 - L_DISCOUNT) es la fórmula para el precio con descuento para un determinado artículo de línea. Estas fórmulas se pueden extraer en funciones para el beneficio de la modularidad y la reutilización.

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Ahora se puede modificar la consulta para invocar esta UDF.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

La consulta con la UDF tiene un rendimiento deficiente, debido a las razones descritas anteriormente. Con la Inserción UDF escalar, la expresión escalar en el cuerpo de la UDF se sustituye directamente en la consulta. Los resultados de la ejecución de esta consulta se muestran en la siguiente tabla:

Consulta:	Consulta sin UDF	Consulta con UDF (sin inserción)	Consulta con inserción de UDF escalar
`Execution time:`	1.6 segundos	29 minuto y 11 segundos	1.6 segundos

Estos números se basan en una base de datos de CCI de 10 GB (con el esquema de TPC-H), que se ejecuta en un equipo con procesador dual (12 núcleos), 96 GB de RAM, respaldado por SSD. Los números incluyen el tiempo de compilación y ejecución con un procedimiento pasivo de almacenamiento en caché y un grupo de búferes. Se ha usado la configuración predeterminada y no se han creado otros índices.

B. UDF escalar con varias instrucciones

Las UDF escalares que se implementan mediante varias instrucciones de T-SQL, como las asignaciones de variables y las bifurcaciones condicionales, también se pueden insertar. Observe la siguiente UDF escalar que, dada una clave de cliente, determina la categoría de servicio para ese cliente. Para llegar a la categoría, primero calcula el precio total de todos los pedidos realizados por el cliente mediante una consulta SQL. Después, usa una instrucción IF (...) ELSE lógica para decidir la categoría en función del precio total.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

Ahora, considere la posibilidad de una consulta que invoca esta UDF.

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

El plan de ejecución para esta consulta en SQL Server 2017 (14.x) (nivel de compatibilidad 140 y versiones anteriores) es el siguiente:

Como se muestra en el plan, SQL Server adopta aquí una estrategia sencilla: para cada tupla de la tabla CUSTOMER, se invoca la UDF y se muestran los resultados. Esta estrategia es ingenua e ineficaz. Con la inserción, esas UDF se transforman en subconsultas escalares equivalentes, que se sustituyen en la consulta que realiza la llamada en lugar de la UDF.

Para la misma consulta, el plan con la UDF insertada tiene el siguiente aspecto.

Como se ha mencionado antes, el plan de consulta ya no tiene un operador de función definida por el usuario, pero sus efectos se tienen en cuenta en el plan, como las vistas o las funciones con valores de tabla insertadas. He aquí algunas observaciones clave del plan anterior:

SQL Server infiere la unión implícita entre CUSTOMER y ORDERS y la hace explícita mediante un operador join.
SQL Server también infiere el GROUP BY O_CUSTKEY on ORDERS implícito y usa IndexSpool + StreamAggregate para implementarlo.
Ahora SQL Server usa el paralelismo de todos los operadores.

Según la complejidad de la lógica de la UDF, es posible que el plan de consulta resultante también aumente de tamaño y complejidad. Como se puede ver, las operaciones dentro de la UDF ahora ya no son opacas y, por tanto, el optimizador de consultas es capaz de calcular los costos de esas operaciones y optimizarlas. Además, como la UDF ya no está en el plan, la invocación iterativa de UDF se sustituye por un plan que evita totalmente la sobrecarga de la llamada de función.

Requisitos de las UDF escalares insertables

Una UDF T-SQL escalar se puede insertar si la definición de la función utiliza construcciones permitidas y la función se utiliza en un contexto que permita la inserción:

Todas las condiciones siguientes de la definición de UDF deben ser verdaderas:

La UDF se escribe con las construcciones siguientes:
- DECLARE, SET: declaración de variables y asignaciones.
- SELECT: consulta SQL con asignaciones de una o múltiples variables ¹.
- IF / ELSE: bifurcación con niveles de anidamiento arbitrarios.
- RETURN: una o varias instrucciones return. A partir de SQL Server 2019 (15.x) CU5, la UDF solo puede contener una única instrucción RETURN que se debe considerar para la inserción ⁶.
- UDF: llamadas de función anidadas o recursivas ².
- Otros: operaciones relacionales como EXISTS, IS NULL.
La UDF no invoca ninguna función intrínseca dependiente del tiempo (como GETDATE()) o tiene efectos secundarios ³ (como NEWSEQUENTIALID()).
La UDF usa la cláusula EXECUTE AS CALLER (comportamiento predeterminado si no se especifica la cláusula EXECUTE AS).
La UDF no hace referencia a variables de tabla ni parámetros con valores de tabla.
La UDF no se compila de forma nativa (se admite la interoperabilidad).
La UDF no hace referencia a tipos definidos por el usuario.
No se agrega ninguna firma a la UDF ⁹.
La UDF no es una función de partición.
La UDF no contiene referencias a expresiones de tabla comunes (CTE).
La UDF no contiene referencias a funciones intrínsecas que pueden modificar los resultados al insertar (como @@ROWCOUNT) ⁴.
La UDF no contiene funciones de agregado que se pasan como parámetros a una UDF escalar ⁴.
La UDF no hace referencia a vistas integradas (como OBJECT_ID) ⁴.
La UDF no hace referencia a los métodos XML ⁵.
La UDF no contiene una instrucción SELECT con ORDER BY sin una cláusula TOP 1 ⁵.
La UDF no contiene una consulta SELECT que realiza una asignación con la cláusula ORDER BY (como SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
La UDF no contiene varias instrucciones RETURN ⁶.
La UDF no hace referencia a la función STRING_AGG ⁶.
La UDF no hace referencia a las tablas remotas ⁷.
La UDF no hace referencia a las columnas cifradas ⁸.
La UDF no contiene referencias a WITH XMLNAMESPACES⁸.
Si la definición de UDF se ejecuta en miles de líneas de código, es posible que SQL Server decida no insertarla.

¹SELECT con acumulación o agregación variable no se admite para la inserción (por SELECT @val += col1 FROM table1).

² Las UDF recursivas solo se insertan hasta una profundidad concreta.

³ Las funciones intrínsecas cuyos resultados dependen de la hora actual del sistema son dependientes de la hora. Una función intrínseca que pueda actualizar algún estado global interno es un ejemplo de función con efectos secundarios. Estas funciones devuelven resultados diferentes cada vez que se llaman, en función del estado interno.

⁴ Restricción agregada en SQL Server 2019 (15.x) CU 2

⁵ Restricción agregada en SQL Server 2019 (15.x) CU 4

⁶ Restricción agregada en SQL Server 2019 (15.x) CU 5

⁷ Restricción agregada en SQL Server 2019 (15.x) CU 6

⁸ Restricción agregada en SQL Server 2019 (15.x) CU 11

⁹ Dado que las firmas se pueden agregar y quitar después de crear una UDF, la decisión de si se va a insertar o no se toma cuando se compila la consulta que hace referencia a una UDF escalar. Por ejemplo, las funciones del sistema suelen estar firmadas con un certificado. Puede usar sys.crypt_properties para encontrar los objetos que están firmados.

Todos los requisitos siguientes del contexto de ejecución deben ser verdaderos:

La UDF no se usa en la cláusula ORDER BY.
La consulta que invoca una UDF escalar no hace referencia a una llamada de UDF escalar en su cláusula GROUP BY.
La consulta que invoca una UDF escalar en su lista de selección con cláusula DISTINCT no tiene una cláusula ORDER BY.
No se llama a la UDF desde una instrucción RETURN ¹.
La consulta que invoca la UDF no tiene expresiones de tabla comunes (CTE) ³.
La consulta de llamada ADF no usa GROUPING SETS, CUBE o ROLLUP².
La consulta de llamada a UDF no contiene una variable que se usa como parámetro UDF para la asignación (por ejemplo, SELECT @y = 2, @x = UDF(@y)) ².
La UDF no se usa en una columna calculada ni en una definición de restricción check.

¹ Restricción agregada en SQL Server 2019 (15.x) CU 5

² Restricción agregada en SQL Server 2019 (15.x) CU 6

³ Restricción agregada en SQL Server 2019 (15.x) CU 11

Para obtener información sobre las correcciones más recientes de la inserción de UDF escalares de T-SQL y los cambios en escenarios de elegibilidad de inserción, vea el artículo de Knowledge Base: FIX: Problemas de inserción de UDF escalares en SQL Server 2019.

Comprobar si una UDF se puede insertar

Para todas las UDF escalares de T-SQL, la vista de catálogo sys.sql_modules incluye una propiedad denominada is_inlineable, que indica si una UDF se puede insertar.

La propiedad is_inlineable se deriva de las construcciones que se encuentran dentro de la definición de UDF. No comprueba si la UDF es de hecho inlineable en tiempo de compilación. Para más información, vea las condiciones para la inserción.

Un valor de 1 indica que el UDF es insertable, y 0 indica lo contrario. Esta propiedad tiene un valor de 1 para todos los TVF en línea también. Para todos los demás módulos, el valor es 0.

Si una UDF escalar es insertable, no implica que siempre se inserte. SQL Server decide (en función de cada consulta y cada UDF) si se puede insertar una UDF. Consulte las listas de requisitos anteriores en este artículo.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Compruebe si se ha realizado la inserción

Si se cumplen todas las condiciones previas y SQL Server decide realizar la inserción, transforma la UDF en una expresión relacional. A partir del plan de consulta, puede averiguar si se ha realizado la inserción:

El XML del plan no tiene un nodo XML <UserDefinedFunction> para una UDF que se ha insertado correctamente.
Se emiten ciertos Eventos Extendidos.

Habilitación de la inserción de UDF escalar

Puede hacer que las cargas de trabajo sean aptas automáticamente para la inserción de UDF escalar si habilita el nivel de compatibilidad 150 para la base de datos. Puede establecerlo con Transact-SQL. Por ejemplo:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Aparte de este paso, no es necesario realizar ningún otro cambio en las UDF ni en las consultas para aprovechar esta función.

Deshabilitación de la inserción de UDF escalar sin cambiar el nivel de compatibilidad

La inserción de UDF escalares se puede deshabilitar en el ámbito de la base de datos, la instrucción o la UDF mientras se mantiene el nivel de compatibilidad de base de datos 150 o superior. Para deshabilitar la inserción de UDF escalares en el ámbito de la base de datos, ejecute la instrucción siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Para volver a habilitar la inserción de UDF escalares para la base de datos, ejecute la instrucción siguiente en el contexto de la base de datos aplicable:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Cuando ON, esta configuración aparece como habilitada en sys.database_scoped_configurations.

También puede deshabilitar la inserción de UDF escalares para una consulta específica mediante la designación de DISABLE_TSQL_SCALAR_UDF_INLINING como una sugerencia de consulta USE HINT.

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de nivel de compatibilidad.

Por ejemplo:

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

La inserción de UDF escalares también se puede deshabilitar para una UDF específica mediante la cláusula INLINE en la instrucción CREATE FUNCTION o ALTER FUNCTION. Por ejemplo:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Una vez ejecutada la instrucción anterior, esta UDF nunca se inserta en ninguna consulta que la invoque. Para volver a habilitar la inserción para esta UDF, ejecute la instrucción siguiente:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

La cláusula INLINE no es obligatoria. Si no se especifica la cláusula INLINE, se establece en ON/OFF automáticamente en función de si la UDF se puede insertar. Si se especifica INLINE = ON pero se detecta que la UDF no es insertable, se producirá un error.

Comentarios

Como se describe en este artículo, la inserción de UDF escalar transforma una consulta con UDF escalares en una consulta con una subconsulta escalar equivalente. Debido a esta transformación, es posible que observe algunas diferencias de comportamiento en los siguientes casos:

La inserción dará como resultado otro valor de hash de consulta para el mismo texto de consulta.
Ciertas advertencias en instrucciones dentro de la UDF (como dividir por cero, etc.) que podrían estar ocultas anteriormente, pueden aparecer debido a la inserción.
Las sugerencias de unión a nivel de consulta pueden dejar de ser válidas, ya que la inserción puede introducir nuevas uniones. En su lugar, deben utilizarse las sugerencias de unión locales.
Las vistas que hacen referencia a UDF escalares insertadas no se pueden indexar. Si tiene que crear un índice en esas vistas, deshabilite la inserción para las UDF a las que se hace referencia.
Puede haber algunas diferencias en el comportamiento del enmascaramiento dinámico de datos con la inserción de UDF.

En determinadas situaciones (dependiendo de la lógica de la UDF), la inserción podría ser más conservadora con respecto al enmascaramiento de columnas de salida. En escenarios en los que las columnas a las que se hace referencia en una UDF no son columnas de salida, no se enmascararán.
Si una UDF hace referencia a funciones integradas como SCOPE_IDENTITY(), @@ROWCOUNT o @@ERROR, con la inserción se cambiará el valor devuelto por la función integrada. Este cambio de comportamiento se debe a que la inserción modifica el ámbito de las instrucciones dentro de la UDF. A partir de SQL Server 2019 (15.x) CU2, la inserción se bloquea si la UDF hace referencia a determinadas funciones intrínsecas (por ejemplo, @@ROWCOUNT).
Si se asigna una variable con el resultado de una UDF insertada y también se usa como index_column_name en FORCESEEKSugerencias de consulta (Transact-SQL), da como resultado el error 8622, lo que indica que el procesador de consultas no pudo generar un plan de consulta debido a las sugerencias definidas en la consulta.

Compartir a través de

Inserción de UDF escalar

Funciones escalares definidas por el usuario de T-SQL

Rendimiento de las UDF escalares

Inserción automática de UDF escalares

Inserción automática de UDF escalares en Microsoft Fabric Data Warehouse

Ejemplos

A. UDF escalar de instrucción única

B. UDF escalar con varias instrucciones

Requisitos de las UDF escalares insertables

Comprobar si una UDF se puede insertar

Compruebe si se ha realizado la inserción

Habilitación de la inserción de UDF escalar

Deshabilitación de la inserción de UDF escalar sin cambiar el nivel de compatibilidad

Comentarios

Contenido relacionado

Comentarios

Recursos adicionales