Introducción a las funciones de SQL Server, sus beneficios e inconvenientes
¿Qué es una función SQL?
Las funciones de SQL Server se pueden usar para devolver valores únicos (escalador) o tablas, usando rutinas T-SQL o CLR (tiempo de ejecución de lenguaje común) y, a menudo, realizando cálculos más complejos de los que le gustaría usar en el código general.
¿Cuándo es una buena idea utilizar una función en lugar de un código en línea?
Buen uso
Las funciones se pueden usar para reemplazar vistas (devolver una tabla), como una columna calculada en una tabla, realizar acciones de búsqueda consistentes o simplemente para modularizar su código, lo que puede ayudar a reducir los cambios necesarios.
Mal uso
Lo vemos todo el tiempo, pero las funciones no deben usarse para devolver datos de búsqueda en lugar de una combinación cuando se trata de grandes conjuntos de datos. Cada fila llamará a la misma función incluso si ya ha encontrado ese valor. En estos casos, use una combinación.
Ejemplos de funciones de escalador
Las funciones del escalador se utilizan mejor para realizar lógicas como el reformateo o los cálculos basados en filas, ya que por su naturaleza se llaman para cada fila, se pueden utilizar para buscar datos en otra tabla, pero en general, obtendrá un mejor rendimiento si utiliza una unión. Para ello, podemos consultar nuestra función get age en el siguiente enlace.
No tendría sentido almacenar la edad de una persona en el momento en que completó un formulario, ya que cuando los datos se consulten más tarde, estarán desactualizados. Una mejor opción sería capturar una fecha de nacimiento y calcularla sobre la marcha. En nuestra función agregamos un campo hasta, que se puede usar para retroceder un cálculo, o quizás más sombríamente, calcular la edad y el momento de la muerte (esta función se extendió para un contrato del NHS).
Example
CREATE FUNCTION [Dates].[GetAge](@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDEjemplos de funciones de escalador
Para usar esto de una tabla ficticia, simplemente usaríamos esto, que proporcionaría la edad actual o la edad al morir.
Use in a select statement
SELECT [PersonID],[DateOfBirth],[dbo].[GetAge]([DateOfBirth],[DeceasedDate]) AgeAsFunction,--Simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]Ejemplos de funciones de escalador
Ventajas : Consistente, modular, más compacto, potencialmente reduce el número de cambios.
Desventajas : para ver el código, debe buscar en la función
Aunque en general es útil, esta función también es extremadamente precisa, ya que utiliza una función de año bisiesto. No es determinista por naturaleza, por lo que nunca debe almacenarse como datos persistentes.
Ejemplos de columnas de tabla
Las columnas calculadas se pueden agregar como persistentes (cambia cuando los datos lo hacen) o no persistentes (calculadas cada vez que se selecciona la fila). Podemos ver dos formas en que los hemos utilizado aquí dentro de nuestro Sistema de gestión de contenido.
Nota : Los datos persistentes pueden ser más difíciles de lograr, ya que requieren que se cumpla un conjunto de restricciones.
No persistente: edad
Usando la función de edad como arriba, podemos agregar esto a una tabla y pasar valores de otras columnas. Luego, simplemente lo seleccionamos como una columna.
Add to a table
CREATE TABLE [Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,[DateOfBirth] [datetime] NULL,[Age] AS ([dbo].[GetAge]([DateOfBirth],[DeceasedDate])),[DeceasedDate] [datetime] NULL)Select Statement
SELECT [PersonID],[DateOfBirth],[Age] AgeAsColumn,--Even simpler code to understand!DATEDIFF(YEAR,[DateOfBirth],[DeceasedDate])+(CASE WHEN DATEPART(DAYOFYEAR,[DateOfBirth])>(DATEPART(DAYOFYEAR,[DeceasedDate])+(CASE WHEN dbo.GetLeapYear([DateOfBirth])=1 AND DATEPART(DAYOFYEAR,[DateOfBirth])>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END) AgeAsScript,[DeceasedDate]FROM [Person]No persistente: edad
Ventajas : Consistente, modular
Desventajas : Ralentiza la velocidad de consulta si no es necesario.
Persistente: CSS minimizado
Tenemos una función que reduce el espacio necesario para CSS hasta en un 30%. Llamar a esto con regularidad ralentizaría la velocidad de selección de la tabla y, dado que los datos rara vez se actualizan, tenía sentido realizar cálculos en el momento de la inserción / actualización. Al crear la columna como una función, tampoco tenemos necesidad de realizar estas operaciones como un disparador.
Add to a Table
CREATE TABLE CSS(CSSID INT IDENTITY(1,1) NOT NULL,CSSText NVARCHAR(MAX),CSSMin AS (ous.CSSProcessor([CSSText])) PERSISTED)Persistente: CSS minimizado
Se puede seleccionar como una columna normal y los datos se almacenan en la tabla. También evita el uso de una declaración de reemplazo masiva que infla nuestro código.
Ventajas : Velocidad de selección consistente, modular y más rápida, ¡sin necesidad de disparador!
Desventajas : aumenta el espacio necesario para la mesa, reduce la velocidad de inserción
Reemplazo de una vista
Tendemos a no usar vistas, excepto cuando usamos regularmente las mismas combinaciones en varios lugares.
Incluso en estos casos, no hay ninguna razón por la que una función de tabla no se pueda utilizar de forma más eficaz. La tabla que hemos utilizado se puede encontrar en el enlace a continuación, y tenemos dos ejemplos de uso, uno a través de una función y el otro mediante una vista.
Create a function
CREATE FUNCTION Dates.GetCalender(@DateFrom DATETIME2,@DateTo DATETIME2,@Function INT) RETURNS @D TABLE (CalenderDate DATETIME2(7) NOT NULL PRIMARY KEY,CalenderCA INT NULL,CalenderCD INT NULL,WeekDayID INT NULL,WeekDayName VARCHAR(9) NULL,HolidayType NVARCHAR(100)) AS BEGININSERT INTO @DSELECT c.*,HolidayTypeFROM [Utilities].[Dates].[Calender] cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND CalenderFunction=@FunctionWHERE c.CalenderDate BETWEEN @DateFrom AND @DateTo RETURNENDGOCreate a view
CREATE VIEW Dates.GetCalenderView ASSELECT c.CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName,h.HolidayType,c.CalenderFunctionFROM (SELECT CalenderDate, CalenderCA, CalenderCD, WeekDayID, WeekDayName, CalenderFunction FROM [Utilities].[Dates].[Calender],(SELECT DISTINCT CalenderFunction FROM Dates.CalenderHolidays) x) cLEFT JOIN Utilities.Dates.CalenderHolidays h ON h.CalenderDate=c.CalenderDate AND h.CalenderFunction=c.CalenderFunctionUsage
SELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',0) --England & WalesSELECT * FROM Dates.GetCalender('2018-01-01','2018-12-31',1) --Scotland----OR----SELECT * FROM Dates.GetCalenderView WHERE CalenderDate BETWEEN '2018-01-01' AND '2018-12-31' AND CalenderFunction=0Reemplazo de una vista
Beneficios : Compacto para llamar, devuelto con clave principal (perfecto para unir más), los parámetros se pueden usar antes en el código.
Desventajas : más código para construir, menos flexible
Usar en Aplicar uniones
Las funciones de tabla son excelentes para usar en Apply Joins, ya que los datos se pueden pasar fila por fila. Usamos nuestra función TextToRows para separar cadenas en SQL Server. En el siguiente ejemplo, usamos una aplicación doble para dividir los datos dos veces con diferentes delimitadores.
SQL Code
DECLARE @TestText TABLE(TextToSplit NVARCHAR(100))INSERT INTO @TestText SELECT 'Text,To,Tows:Split,One'INSERT INTO @TestText SELECT 'Text,To,Tows:Split,Two'SELECT t.TextToSplit,s1.WordInt,s1.WordStr,s2.WordInt,s2.WordStrFROM @TestText tOUTER APPLY dbo.TextToRows(':',TextToSplit) s1OUTER APPLY dbo.TextToRows(',',WordStr) s2Further detail
Some of the functions we have written can be found below.
