Solución:
Funciones escalares
Las funciones escalares (a veces denominadas funciones definidas por el usuario / UDF) devuelven un valor único como valor de retorno, no como un conjunto de resultados, y se pueden utilizar en la mayoría de los lugares dentro de una consulta o SET declaración, a excepción de la FROM cláusula (¿y tal vez en otros lugares?). Además, las funciones escalares se pueden llamar a través de EXEC, al igual que los procedimientos almacenados, aunque no hay muchas ocasiones para hacer uso de esta capacidad (para obtener más detalles sobre esta capacidad, consulte mi respuesta a la siguiente pregunta en DBA.StackExchange: ¿Por qué las funciones con valores escalares necesitan permiso de ejecución en lugar de seleccionar? ). Estos se pueden crear tanto en T-SQL como en SQLCLR.
-
T-SQL (UDF):
- Antes de SQL Server 2019: estas funciones escalares suelen ser un problema de rendimiento porque generalmente se ejecutan para cada fila devuelta (o escaneada) y prohíba siempre los planes de ejecución paralela.
- A partir de SQL Server 2019: ciertas UDF escalares de T-SQL pueden estar en línea, es decir, colocar sus definiciones directamente en la consulta de modo que la consulta no llame a la UDF (similar a cómo funcionan los iTVF (ver más abajo)). Existen restricciones que pueden evitar que una UDF sea insertable (si antes no era una palabra, lo es ahora), y las UDF que pueden estar insertadas no siempre estarán insertadas debido a varios factores. Esta función se puede desactivar en los niveles de base de datos, consulta y UDF individual. Para obtener más información sobre esta nueva característica realmente interesante, consulte: Scalar UDF Inlining (asegúrese de revisar la sección “requisitos”).
-
SQLCLR (UDF): estas funciones escalares también suelen ejecutarse por cada fila devuelta o escaneada, pero hay dos ventajas importantes sobre las UDF de T-SQL:
- A partir de SQL Server 2012, los valores devueltos se pueden plegar constantemente en el plan de ejecución SI la UDF no lo hace alguna acceso a datos, y si está marcado
IsDeterministic = true. En este caso, la función no se ejecutaría en cada fila. - Funciones escalares SQLCLR pueden trabajar en planes paralelos () si no lo hacen alguna acceso a la base de datos.
- A partir de SQL Server 2012, los valores devueltos se pueden plegar constantemente en el plan de ejecución SI la UDF no lo hace alguna acceso a datos, y si está marcado
Funciones con valores de tabla
Las funciones con valores de tabla (TVF) devuelven conjuntos de resultados y se pueden utilizar en una FROM cláusula, JOIN, o CROSS APPLY / OUTER APPLY de cualquier consulta, pero a diferencia de las vistas simples, no puede ser el objetivo de ninguna declaración DML (INSERT / UPDATE / DELETE). Estos también se pueden crear tanto en T-SQL como en SQLCLR.
-
Declaración múltiple de T-SQL (TVF): estos TVF, como su nombre lo indica, pueden tener varias declaraciones, similar a un procedimiento almacenado. Los resultados que vayan a devolver se almacenan en una variable de tabla y se devuelven al final; es decir, no se devuelve nada hasta que la función haya terminado de procesarse. El número estimado de filas que devolverán, según se informa al Optimizador de consultas (que afecta el plan de ejecución) depende de la versión de SQL Server:
- Antes de SQL Server 2014: estos siempre informan 1 (sí, solo 1) fila.
- SQL Server 2014 y 2016: siempre informan 100 filas.
- A partir de SQL Server 2017: el valor predeterminado es informar 100 filas, PERO en algunas condiciones, el recuento de filas será bastante preciso (según las estadísticas actuales) gracias a la nueva función de ejecución intercalada.
-
T-SQL en línea (iTVF): estos TVF solo pueden ser una única declaración, y esa declaración es una consulta completa, como una Vista. Y, de hecho, los TVF en línea son esencialmente una vista que acepta parámetros de entrada para su uso en la consulta. Tampoco almacenan en caché su propio plan de consulta, ya que su definición se coloca en la consulta en la que se utilizan (a diferencia de los otros objetos descritos aquí), por lo que se pueden optimizar mucho mejor que los otros tipos de TVF (). Estos TVF funcionan bastante bien y se prefieren si la lógica se puede manejar en una sola consulta.
-
SQLCLR (TVF): estos TVF son similares a los TVF de T-SQL MultiStatement en que acumulan todo el conjunto de resultados en la memoria (incluso si es un archivo de intercambio / página) antes de liberarlo todo al final. El número estimado de filas que devolverán, según se informa al Optimizador de consultas (que afecta el plan de ejecución) es siempre de 1000 filas. Dado que un recuento de filas fijo está lejos de ser ideal, apoye mi solicitud para permitir especificar el recuento de filas: Permita que los TVF (T-SQL y SQLCLR) proporcionen estimaciones de filas definidas por el usuario para el optimizador de consultas
-
Transmisión SQLCLR (sTVF): estos TVF permiten código C # / VB.NET complejo al igual que los TVF SQLCLR normales, pero son especiales porque devuelven cada fila a la consulta de llamada a medida que se generan (). Este modelo permite que la consulta de llamada comience a procesar los resultados tan pronto como se envíe la primera, por lo que la consulta no necesita esperar a que se complete todo el proceso de la función antes de ver algún resultado. Y requiere menos memoria, ya que los resultados no se almacenan en la memoria hasta que se completa el proceso. El número estimado de filas que devolverán, según se informa al Optimizador de consultas (que afecta el plan de ejecución) es siempre de 1000 filas. Dado que un recuento de filas fijo está lejos de ser ideal, apoye mi solicitud para permitir especificar el recuento de filas: Permita que los TVF (T-SQL y SQLCLR) proporcionen estimaciones de filas definidas por el usuario para el optimizador de consultas
Funciones agregadas
Agregados definidos por el usuario (UDA) son agregados similares a SUM(), COUNT(), MIN(), MAX(), etc. y normalmente requieren un GROUP BY cláusula. Estos solo se pueden crear en SQLCLR, y esa capacidad se introdujo en SQL Server 2005. Además, a partir de SQL Server 2008, los UDA se mejoraron para permitir múltiples parámetros de entrada (). Una deficiencia particular es que no hay conocimiento del orden de filas dentro del grupo, por lo que crear un total acumulado, que sería relativamente fácil si se pudiera garantizar el orden, no es posible dentro de un SAFE Montaje.
Por favor vea también:
- CREAR FUNCIÓN (documentación de MSDN)
- CREAR AGREGADO (documentación de MSDN)
- Ejemplo de función con valores de tabla CLR con transmisión completa (STVF / TVF) (artículo que escribí)
Una función escalar devuelve un solo valor. Es posible que ni siquiera esté relacionado con las tablas de su base de datos.
Una función con valores de tabla devuelve las columnas especificadas para las filas de su tabla que cumplen con sus criterios de selección.
Una función de valor agregado devuelve un cálculo en las filas de una tabla, por ejemplo, sumando valores.