¿Diferencia entre funciones escalares, de valor de tabla y agregadas en SQL server?

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

Author: Solomon Rutzky, 2016-01-13
Source

3 answers

Funciones escalares

Las funciones escalares (a veces denominadas Funciones definidas por el usuario / UDFs) devuelven un solo 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 sentencia SET, excepto por la cláusula FROM (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 más detalles sobre esta capacidad, consulte mi respuesta a la siguiente pregunta sobre 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): 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 se ejecutan típicamente por cada fila devuelto o escaneado, pero hay dos beneficios importantes sobre T-SQL UDFs:

    • A partir de SQL Server 2012, los valores de retorno se pueden plegar constantemente en el plan de ejecución SI el UDF no hace ningún acceso a datos, y si está marcado IsDeterministic = true. En este caso, la función no se ejecutaría por cada fila.
    • Las funciones escalares SQLCLR pueden trabajar en planes paralelos ( ) si no tienen acceso a la base de datos.

Valor del cuadro Funciones

Las funciones de valor de tabla (TVFs) 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 vistas simples, no puede ser el objetivo de ninguna instrucció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 múltiples sentencias, similares a un Procedimiento Almacenado. Sean cuales sean los resultados que van 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 se realiza el procesamiento. El número estimado de filas que devolverán, según se informa al Optimizador de consultas (que afecta al 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: estos siempre reportan 100 filas.
    • A partir de SQL Server 2017: el valor predeterminado es reporte 100 filas, PERO bajo algunas condiciones el conteo de filas será bastante preciso (basado en las estadísticas actuales) gracias a la nueva característica Ejecución intercalada.

  • T-SQL Inline (iTVF): Estos TVF solo pueden ser una sola instrucción, y esa instrucción es una consulta completa, al igual que 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 consultas como su la definición se coloca en la consulta en la que se utilizan (a diferencia de los otros objetos descritos aquí), por lo tanto, se pueden optimizar mucho mejor que los otros tipos de TVFs ( ). Estos TVF funcionan bastante bien y son preferidos si la lógica se puede manejar en una sola consulta.

  • SQLCLR (TVF): estos TVF son similares a T-SQL MultiStatement TVF en que acumulan todo el conjunto de resultados en memoria (incluso si es un archivo swap / page) antes de liberarlo todo en el muy fin. El número estimado de filas que devolverán, según se informa al Optimizador de consultas (que afecta al plan de ejecución) es siempre de 1000 filas. Dado que un recuento de filas fijo está lejos de ser ideal, por favor apoye mi solicitud para permitir especificar el recuento de filas: Permitir que TVFs (T-SQL y SQLCLR) proporcione estimaciones de filas definidas por el usuario al optimizador de consultas

  • SQLCLR Streaming (sTVF): estos TVF permiten complejos C# / VB.NET código igual que SQLCLR normal TVFs, pero son especiales en que devuelven cada fila a la consulta que llama a medida que se generan (). Este modelo permite que la consulta llamante comience a procesar los resultados tan pronto como se envíe el primero, por lo que la consulta no necesita esperar a que se complete todo el proceso de la función antes de ver ningún resultado. Y requiere menos memoria ya que los resultados no se almacenan en la memoria hasta que se complete el proceso. El número estimado de filas que devolverán, según se informa a la consulta El optimizador (que afecta el plan de ejecución) es siempre de 1000 filas. Dado que un recuento de filas fijo está lejos de ser ideal, por favor apoye mi solicitud para permitir especificar el recuento de filas: Permitir que TVFs (T-SQL y SQLCLR) proporcione estimaciones de filas definidas por el usuario al optimizador de consultas

Funciones agregadas

Los agregados definidos por el usuario (UDA) son agregados similares a SUM(), COUNT(), MIN(), MAX(), etc. y normalmente requieren una cláusula GROUP BY. Estos solo pueden ser creado en SQLCLR, y esa habilidad fue introducida en SQL Server 2005. Además, a partir de SQL Server 2008, las UDAs fueron mejoradas 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 continuo, que sería relativamente fácil si se pudiera garantizar el orden, no es posible dentro de un Ensamblaje SAFE.

Por favor vea también:

 44
Author: Solomon Rutzky,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-24 15:01:00

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 valor presentada devuelve las columnas especificadas para las filas de la tabla que cumplen los criterios de selección.

Una función con valor agregado devuelve un cálculo a través de las filas de una tabla for por ejemplo, sumando valores.

 6
Author: Michael Bennett,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-01-12 22:29:43

Función Escalar

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

Función de valor de la tabla

Es un poco diferente en comparación con lo anterior. Devuelve un valor de tabla. Dentro del cuerpo de esta función se 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 BEGIN & END declaraciones aquí.

Agregado Funciones

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

 -1
Author: LooselyCoded,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-11 19:59:25