última valores una tipos tabla ser parametros para orden opcionales línea linea las instrucción instrucciones función funciones escalares debe crear correcto con sql-server function tsql aggregate-functions sqlclr

sql server - una - ¿Diferencia entre las funciones escalares, con valores de tabla y agregadas en el servidor SQL?



tipos de funciones en sql server (3)

¿Cuál es la diferencia entre las funciones de valor escalar, de valor de tabla y agregado en el servidor SQL? ¿Y los llama desde una consulta necesitan un método diferente, o los llamamos de la misma manera?

Funciones escalares

Las funciones escalares (a veces denominadas Funciones definidas por el usuario / UDF) devuelven un único valor como valor devuelto, no como un conjunto de resultados, y se pueden usar en la mayoría de los lugares dentro de una consulta o declaración SET , excepto para la cláusula FROM (y tal vez 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: Why escalar funciones valiosas necesitan permiso de ejecución en lugar de seleccionar? ). Estos se pueden crear tanto en T-SQL como en SQLCLR.

  • T-SQL (UDF): estas funciones escalares suelen ser un problema de rendimiento porque generalmente se ejecutan para cada fila devuelta (o escaneada) y siempre prohíben los planes de ejecución en paralelo.

  • 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 de retorno se pueden plegar constantemente en el plan de ejecución SI la UDF no realiza ningún acceso a los datos, y si está marcado como IsDeterministic = true . En este caso, la función no se ejecutaría por cada fila.
    • Las funciones escalares SQLCLR pueden funcionar en planes paralelos (😃) si no tienen acceso a la base de datos.

Funciones con valores de tabla

Las funciones con valores de tabla (TVF) devuelven conjuntos de resultados y se pueden usar en una cláusula FROM , JOIN o CROSS APPLY / OUTER APPLY de cualquier consulta, pero a diferencia de las simples Views, no pueden ser el objetivo de ninguna declaración DML ( INSERT / UPDATE / DELETE ). Estos también se pueden crear tanto en T-SQL como en SQLCLR.

  • T-SQL MultiStatement (TVF): estos TVF, como su nombre lo indica, pueden tener varias declaraciones, similares 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 lo informado al Query Optimizer (que afecta el plan de ejecución) depende de la versión de SQL Server:

    • Antes de SQL Server 2014: estos siempre reportan 1 (sí, solo 1) fila.
    • SQL Server 2014 y 2016: estos siempre reportan 100 filas.
    • A partir de SQL Server 2017: el valor predeterminado es reportar 100 filas, PERO en algunas condiciones, el recuento de filas será bastante preciso (basado en las estadísticas actuales) gracias a la nueva función de ejecución intercalada .

  • T-SQL Inline (iTVF): estos TVF solo pueden ser una sola declaración, y esa declaración es una consulta completa, como una vista. Y, de hecho, Inline TVFs son esencialmente una vista que acepta parámetros de entrada para usar 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 que se describen aquí), por lo tanto, 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 T-SQL MultiStatement TVF en que construyen 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, tal como se informa al Optimizador de consultas (que afecta el plan de ejecución), es siempre de 1000 filas. Dado que un recuento fijo de filas está lejos de ser ideal, respalde mi solicitud para permitir especificar el recuento de filas: Permitir que 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 complejo C # / VB.NET al igual que los TVF SQLCLR regulares, pero son especiales en el sentido de que 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ía el primero, por lo que la consulta no necesita esperar a que se complete todo el proceso de la función antes de ver los resultados. 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 lo informado al Optimizador de consultas (que afecta el plan de ejecución) siempre es 1000 filas. Dado que un recuento fijo de filas está lejos de ser ideal, respalde mi solicitud para permitir especificar el recuento de filas: Permitir que TVF (T-SQL y SQLCLR) proporcionen estimaciones de filas definidas por el usuario para el optimizador de consultas

Funciones agregadas

Los Agregados definidos por el usuario (UDA) son agregados similares a SUM() , COUNT() , MIN() , MAX() , etc. y generalmente requieren una cláusula GROUP BY . 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 ordenamiento de filas dentro del grupo, por lo que crear un total acumulado, que sería relativamente fácil si se pudiera garantizar el pedido, no es posible dentro de una Asamblea SAFE .

Por favor, también vea:

Una función escalar devuelve un solo valor. Puede que ni siquiera esté relacionado con las tablas en su base de datos.

Una función de valor de tabla devuelve las columnas especificadas para las filas de su tabla que cumplen sus criterios de selección.

Una función de valor agregado devuelve un cálculo a lo largo de las filas de una tabla, por ejemplo, sumando valores.

Función escalar

Devuelve un solo valor. Es como escribir funciones en otros lenguajes de programación usando la sintaxis T-SQL .

Función de tabla de valores

Es un poco diferente en comparación con lo anterior. Devuelve un valor de tabla. Dentro del cuerpo de esta función, escribe una consulta que devolverá la tabla exacta. Por ejemplo:

CREATE FUNCTION <function name>(parameter datatype) RETURN table AS RETURN ( -- *write your query here* --- )

Tenga en cuenta que no hay ninguna BEGIN & END aquí.

Funciones agregadas

Incluye funciones integradas que se utilizan junto con la cláusula GROUP . Por ejemplo: SUM() , MAX() , MIN() , AVG() , COUNT() son funciones agregadas.