Creación de funciones escalares
Las funciones escalares son herramientas esenciales en SQL Server que permiten encapsular la lógica reutilizable y devolver un solo valor. Puede usarlos directamente en SELECT instrucciones, WHERE cláusulas y otras expresiones de T-SQL , lo que hace que las consultas sean más fáciles de mantener y el código sea más modular.
Descripción de los aspectos básicos de la función escalar
Una función escalar acepta cero o más parámetros y devuelve un único valor de un tipo de datos especificado. A diferencia de los procedimientos almacenados, las funciones escalares se pueden incrustar directamente en expresiones SQL siempre que se use una columna o variable.
Las características clave de las funciones escalares incluyen su capacidad de aceptar parámetros de entrada, realizar cálculos o transformaciones y devolver exactamente un valor. El tipo de datos devuelto se define explícitamente en la definición de función, que SQL Server valida en tiempo de creación.
Al crear una función escalar, va a crear una lógica reutilizable a la que otros desarrolladores pueden llamar a través de la base de datos. Esto promueve la reutilización del código y ayuda a mantener la coherencia entre las aplicaciones.
Definición de la sintaxis de función escalar
Para crear una función escalar, use la CREATE FUNCTION instrucción con componentes de sintaxis específicos. La estructura básica incluye el nombre de la función, los parámetros, el tipo de valor devuelto y el cuerpo de la función.
Este es el patrón de sintaxis fundamental:
CREATE FUNCTION schema_name.function_name
(
@parameter1 datatype,
@parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
-- Function logic here
RETURN @result
END
La RETURNS cláusula especifica el tipo de datos del valor único que devuelve la función. Dentro del BEGIN...END bloque, escriba la lógica de T-SQL y use una RETURN instrucción para devolver el resultado.
Por ejemplo, puede crear una función sencilla que calcule los impuestos de ventas:
CREATE FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
SET @TaxAmount = @Amount * @TaxRate
RETURN @TaxAmount
END
Esta función acepta dos parámetros y devuelve el importe fiscal calculado. Puede usar esta función en cualquier SELECT instrucción.
Implementación de funciones escalares con lógica de negocios
Las funciones escalares se destacan en la encapsulación de reglas de negocios y cálculos que debe aplicar de forma coherente en la base de datos. Con las funciones escalares, centraliza la lógica que, de lo contrario, podría duplicarse en varias consultas o código de aplicación.
Considere un escenario en el que necesita calcular la antigüedad de los empleados en años. Cree una función escalar que acepte una fecha de contratación y devuelva el número de años completos:
CREATE FUNCTION dbo.GetEmployeeTenure
(
@HireDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @Tenure INT
SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
RETURN @Tenure
END
Puede usar esta función en consultas para mostrar información sobre la antigüedad:
SELECT
EmployeeName,
HireDate,
dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5
Este enfoque garantiza un cálculo coherente de la antigüedad en toda la base de datos. Si las reglas de negocios cambian, modifique la función una vez en lugar de actualizar varias consultas.
Nota:
Esta función usa GETDATE(), lo que hace que no sea determinista. Las funciones no deterministas no se pueden usar en vistas indizadas o índices en columnas calculadas. En escenarios que requieren determinismo, pase la fecha actual como un parámetro en su lugar.
Aplicación de procedimientos recomendados para funciones escalares
Al crear funciones escalares, varios procedimientos recomendados ayudan a garantizar un rendimiento y un mantenimiento óptimos. Comprender estas prácticas le ayuda a evitar problemas comunes y a crear funciones eficaces y confiables.
En primer lugar, mantenga las funciones escalares deterministas siempre que sea posible. Una función determinista siempre devuelve el mismo resultado dados los mismos parámetros de entrada. Las funciones que hacen referencia a funciones o tablas de fecha y hora del sistema no son deterministas y pueden impedir ciertas optimizaciones de consulta.
Además, evite efectos colaterales en sus funciones. Las funciones escalares no deben modificar el estado de la base de datos ni tener dependencias en recursos externos. Esta restricción existe porque SQL Server puede ejecutar funciones varias veces o en distintos pedidos de los esperados.
Por último, tenga en cuenta las implicaciones de rendimiento. Cuando se usa una función escalar en una WHERE cláusula o SELECT lista con tablas grandes, SQL Server puede ejecutar la función para cada fila. Esto puede afectar significativamente al rendimiento de las consultas. Para estos escenarios, considere utilizar funciones con valores de tabla en línea como alternativa.
Este es un ejemplo de una función escalar bien diseñada que sigue estos procedimientos:
CREATE FUNCTION dbo.FormatPhoneNumber
(
@PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
DECLARE @FormattedNumber VARCHAR(14)
IF LEN(@PhoneNumber) = 10
SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
SUBSTRING(@PhoneNumber, 4, 3) + '-' +
SUBSTRING(@PhoneNumber, 7, 4)
ELSE
SET @FormattedNumber = @PhoneNumber
RETURN @FormattedNumber
END
Esta función es determinista, no tiene efectos secundarios y realiza una transformación sencilla. Controla la entrada no válida correctamente devolviendo el valor original cuando el número de teléfono no coincide con el formato esperado.
Modificación y administración de funciones escalares
Después de crear una función escalar, puede modificar su definición mediante la ALTER FUNCTION instrucción . La ALTER FUNCTION sintaxis refleja CREATE FUNCTION, pero permite cambiar la función sin quitarla y volver a crearla, lo que conserva los permisos y las dependencias.
ALTER FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
-- Updated logic with rounding
SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
RETURN @TaxAmount
END
Para quitar una función escalar, use la DROP FUNCTION instrucción :
DROP FUNCTION IF EXISTS dbo.CalculateSalesTax
La IF EXISTS cláusula impide errores si la función no existe, lo que resulta útil en los scripts de implementación. Antes de quitar una función, compruebe que ningún otro objeto de base de datos dependa de ella comprobando las vistas del sistema como sys.sql_expression_dependencies.