Inserción de Funciones definidas por el usuario

Inserción de UDF escalares SQL Server 2019

La Inserción de UDF escalar (Función definida por el usuario escalar), es una característica nueva de SQL Server 2019 incluída dentro del procesamiento de consultas inteligentes. La inserción de UDF escalar mejora el rendimiento de las consultas que llaman a UDF escalares en SQL Server.

Las UDF escalares en SQL Server son los tipos de funciones definidas por el usuario que devuelven un único valor. El objetivo de usar las UDF es de la reutilización y modularidad del código en todas las consultas en SQL Server.

Para más información:
Ver funciones definidas por el usuario.
Ver Funciones definidas por el usuario con valores de tabla.

Rendimiento de las UDF escalares en SQL Server

Las UDF escalares pueden tener un rendimiento deficiente por lo siguiente:

  1. Invocación iterativa: la UDF escalar se invocan en cada registro, lo que supone costos adicionales en cada cambio de contexto.
  2. Falta de costos: durante la optimización, solo se calcula el costo de los operadores relacionales, mientras que el de
    los operadores escalares no.
  3. Ejecución interpretada: las UDF se evalúan como un lote de instrucciones, y se ejecutan instrucción por instrucción.
  4. La ejecución en serie, debido a que SQL Server no admite el paralelismo entre consultas en las consultas que invocan las UDF.

Inserción automática de UDF escalares

La característica de Inserción de UDF escalar permite mejorar el rendimiento de las consultas que consumen funciones definidas por el usuario escalares de SQL Server, donde el uso de una UDF puede causar lentitud.
Esta nueva característica transforman automáticamente las UDF escalares en subconsultas escalares que se sustituyen en la consulta que realiza la llamada a la UDF. El plan de ejecución de la consulta permite ver como al UDF escalar se incluye en la consulta.

Ejemplos

Usando la base de datos Northwind
use northwind
go

Ejercicio 1

Mostar las categorías y la cantidad de productos en Stock de cada una de estas.

Consulta con Join y agrupamientos
select C.CategoryID, C.Categoryname,
count(P.CategoryID) As ‘Cantidad’
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
group by C.CategoryID, C.Categoryname
go

La imagen muestra el listado.

La UDF para la cantidad de productos es como sigue:
Create function udfCantidadProductosCategoria(@CodigoCategoria int)
returns int
As
Begin
Declare @Cantidad int
Set @Cantidad =
(select count(P.ProductID) from Products As P
where P.CategoryID = @CodigoCategoria)
return @Cantidad
End
go

Ejecutar la consulta con el nivel de compatibilidad de una versión anterior a SQL Server 2019.

Cambiando el nivel de compatibilidad a SQL Server 2014
Alter database Northwind set compatibility_Level = 130
go

Ejecutando la consulta
select C.CategoryID, C.Categoryname,
dbo.udfCantidadProductosCategoria(C.CategoryID) As ‘Cantidad’
from Categories As C
go

El plan estimado de ejecución se muestra como en la imagen siguiente
Note que la UDF se ejecuta en una consulta adicional.

Regresando el nivel de compatibilidad a SQL Server 2019 (150)
Alter database Northwind set compatibility_Level = 150
go

Ejecutando la consulta
select C.CategoryID, C.Categoryname,
dbo.udfCantidadProductosCategoria(C.CategoryID) As ‘Cantidad’
from Categories As C
go

El plan estimado de ejecución se muestra como en la imagen siguiente
Note que la UDF automáticamente al ejecutarse en SQL Server 2019 se incluye en la consulta principal.

Ejercicio 2

UDF escalar con varias instrucciones

En este ejercicio se va a crear una UDF que permita calificar al empleado de acuerdo a un volumen de ventas.
Para calcular el total de ventas de un empleado se debe calcular la suma del total de las órdenes, el diagrama mostrado nos ayuda a entender el conjunto de resultados.

Para calcular el total de una orden se va a crear una UDF escalar
Create function dbo.udfTotalPorOrden(@NumeroOrden int)
Returns Numeric(9,2)
As
Begin
Declare @TotalOrden Numeric(9,2)
Set @TotalOrden =
(select sum((D.UnitPrice * D.Quantity)*(1 – D.Discount))
from [Order Details] As D where D.OrderID =@NumeroOrden )
Return @TotalOrden
End
go

Para calcular el total de la orden nº 10248
Select dbo.udfTotalPorOrden(10248)
go

Para calcular el total de ventas de un empleado se deben sumar todas los totales de las órdenes
Create function dbo.udfTotalVentasPorEmpleado(@CodigoEmpleado int)
Returns Numeric(9,2)
As
Begin
Declare @TotalVentasEmpleado Numeric(9,2)
Set @TotalVentasEmpleado =
(select sum(dbo.udfTotalPorOrden(O.OrderID))
from Orders As O where O.EmployeeID = @CodigoEmpleado)
Return @TotalVentasEmpleado
End
go

Las ventas de los empleados se muestran en el listado siguiente

select Empleado = concat_ws(‘ ‘,E.FirstName, E.LastName),
dbo.udfTotalVentasPorEmpleado(E.EmployeeID) As Total
from employees As E
order by Total desc
go
El listado es el siguiente

El plan de ejecución del listado en SQL Server 2019

Cambiando el nivel de compatibilidad a SQL Server 2017

Alter database Northwind set compatibility_Level = 140
go
El listado de los empleados
select Empleado = concat_ws(‘ ‘,E.FirstName, E.LastName),
dbo.udfTotalVentasPorEmpleado(E.EmployeeID) As Total
from employees As E
order by Total desc
go

El plan de ejecución del listado en SQL Server 2017.
Note que se realizan 3 consultas, se muestran en la imagen en recuadros verdes.

Para calificar el empleado de acuerdo a las ventas se usarán los siguientes rangos:
Menor a 100,000: Regular
Hasta 200,000: Gold
Más de 200,000: Platinium

La UDF para la calificación es como sigue:

Create or alter function dbo.udfCalificaEmpleadoVolumenVenta(@CodigoEmpleado int)
Returns nvarchar(9)
As
Begin
Declare @TotalVentas Numeric(9,2)
Declare @Calificacion nvarchar(9)
Set @TotalVentas = (select dbo.udfTotalVentasPorEmpleado(@CodigoEmpleado)
from employees As E where E.EmployeeID = @CodigoEmpleado )
— Calificación del empleado
If @TotalVentas < 100000 Begin
Set @Calificacion = ‘Regular’ End
Else If @TotalVentas < 200000 Begin
Set @Calificacion = ‘Gold’ End
Else Begin
Set @Calificacion = ‘Platinium’ End
Return @Calificacion
End
go

Regresar al nivel de compatibilidad de SQL Server 2019
Alter database Northwind set compatibility_Level = 150
go

Listado de los empleados y su calificación

select Empleado = concat_ws(‘ ‘,E.FirstName, E.LastName),
dbo.udfTotalVentasPorEmpleado(E.EmployeeID) As Total,
dbo.udfCalificaEmpleadoVolumenVenta(E.EmployeeID) As ‘Calificativo’
from employees As E
order by Total desc
go

Plan de ejecución para SQL Server 2019

Plan de ejecución para SQL Server 2017

Requisitos para que las UDF escalares se inserten

Para que se pueda insertar una UDF escalar se debe cumplir las siguientes condiciones.

La UDF se escribe usando DECLARE y SET, SELECT, IF/ELSE, RETURN, UDF anidadas o usando EXISTS o ISNULL.
La UDF no invoca ninguna función intrínseca que dependa de la hora.
La UDF usa la cláusula Execute As Caller
La UDF no hace referencia a variables de tabla.
La consulta que invoca una UDF escalar no agrupa usando Group by.
La consulta que invoca una UDF escalar usando DISTINCT no contiene ORDER BY.
La UDF no se utiliza en la cláusula ORDER BY.
La UDF no se usa en una columna calculada
La UDF no se una en una definición de restricción CHECK.
La UDF no hace referencia a tipos definidos por el usuario.
La UDF no es una función de partición.
La UDF no contiene referencias a expresiones de tabla comunes (CTE).

Comprobación de si una UDF se puede insertar o no

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

Regresar al nivel de compatibilidad de SQL Server 2019
Alter database Northwind set compatibility_Level = 150
go

El listado de las UDF utilizadas en este artículo
select definition as ‘Texto’, is_inlineable from sys.sql_modules
where definition like ‘Create function%’
go

Crear UDF escalares especificando si se pueden insertar o no.

Para crear una UDF escalar y especificar si se va a insertar se usa la cláusula With Inline

UDF que NO se puede insertar, en este ejercicio se ha utilizado la cláusula With inline = Off.

Create or Alter Function dbo.udfCalculaTotalItem(@Precio Numeric(9,2), @Cantidad Numeric(9,2))
RETURNS Numeric(9,2) WITH INLINE = OFF
As
BEGIN
RETURN @Precio * @Cantidad
END
go

UDF que SI se puede insertar, obviamente debe cumplir los requerimientos. Se ha utilizado la cláusula with inline = on

Create or Alter Function dbo.udfCalculaTotalItem(@Precio Numeric(9,2), @Cantidad Numeric(9,2))
RETURNS Numeric(9,2) WITH INLINE = On
As
BEGIN
RETURN @Precio * @Cantidad
END
go