Función valorada de tabla de declaraciones múltiples frente a function valorada de tabla en línea

Algunos ejemplos para mostrar, solo en caso de:

Tabla en línea valorada

CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO 

Tabla de statement múltiple valorada

 CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO 

¿Hay alguna ventaja al usar un tipo (enunciado en línea o multi) sobre el otro? ¿Hay ciertos escenarios cuando uno es mejor que el otro o las diferencias son puramente sintácticas? Me doy count de que las dos consultas de ejemplo están haciendo cosas diferentes, pero ¿hay alguna razón por la que las escriba de esa manera?

Leer sobre ellos y las ventajas / diferencias realmente no se han explicado.

Al investigar el comentario de Matt, he revisado mi statement original. Él está en lo cierto, habrá una diferencia en el performance entre una function con valores de tabla en línea (ITVF) y una function con valores de tabla multi declaraciones (MSTVF) incluso si ambos simplemente ejecutan una instrucción SELECT. SQL Server tratará un ITVF como una VIEW en el sentido de que calculará un plan de ejecución utilizando las últimas statistics en las tablas en cuestión. Un MSTVF es equivalente a rellenar todo el contenido de su instrucción SELECT en una variable de tabla y luego join a eso. Por lo tanto, el comstackdor no puede usar ninguna estadística de tabla en las tablas en MSTVF. Por lo tanto, si todo sigue igual (lo que rara vez sucede), la ITVF tendrá un mejor performance que la MSTVF. En mis testings, la diferencia de performance en el time de finalización fue insignificante, sin embargo, desde el punto de vista estadístico, fue notable.

En tu caso, las dos funciones no son funcionalmente equivalentes. La function MSTV realiza una consulta adicional cada vez que se llama y, lo más importante, filtra en la identificación del cliente. En una consulta grande, el optimizador no podría aprovechar otros types de uniones ya que necesitaría llamar a la function para cada ID de cliente pasado. Sin embargo, si reescribió su function MSTV de la siguiente manera:

 CREATE FUNCTION MyNS.GetLastShipped() RETURNS @CustomerOrder TABLE ( SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL ) AS BEGIN INSERT @CustomerOrder SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.OrderDate = ( Select Max(SH1.OrderDate) FROM Sales.SalesOrderHeader As SH1 WHERE SH1.CustomerID = A.CustomerId ) RETURN END GO 

En una consulta, el optimizador podría llamar a esa function una vez y build un mejor plan de ejecución, pero aún así no sería mejor que una ITVS equivalente o no parametrizable o una VIEW .

Las ITVF deben preferirse a las MSTVF cuando sea posible debido a los types de datos, la nulabilidad y la intercalación de las columnas en la tabla, mientras que usted declara esas properties en una function de tabla de múltiples declaraciones y, lo que es más importante, obtendrá mejores planes de ejecución de la ITVF. En mi experiencia, no he encontrado muchas circunstancias en las que una ITVF fuera una mejor opción que una VISTA pero el kilometraje puede variar.

Gracias a Matt.

Adición

Desde que vi esto aparecer recientemente, aquí hay un excelente análisis hecho por Wayne Sheffield comparando la diferencia de performance entre las funciones de Inline Table Valued y las funciones de Multi-Statement.

Su publicación de blog original.

Copiar en SQL Server Central

Internamente, SQL Server trata una function con valores de tabla en línea de forma muy similar a como lo haría con una vista y trata una function con valores de tabla multi-extracto similar a cómo sería un procedimiento almacenado.

Cuando se utiliza una function en línea con valores de tabla como parte de una consulta externa, el procesador de consultas expande la definición UDF y genera un plan de ejecución que accede a los objects subyacentes, utilizando los índices en estos objects.

Para una function con valores de tabla multi-extracto, se crea un plan de ejecución para la function en sí y se almacena en el caching del plan de ejecución (una vez que la function se ha ejecutado por primera vez). Si las funciones con valores de tabla de declaraciones múltiples se utilizan como parte de consultas más grandes, entonces el optimizador no sabe qué devuelve la function, y por lo tanto hace algunas suposiciones estándar; de hecho, asume que la function devolverá una sola fila, y que los retornos de se accederá a la function usando un escaneo de tabla contra una tabla con una sola fila.

Donde las funciones con valores de tabla multi-statement pueden tener un performance bajo es cuando devuelven una gran cantidad de filas y se unen en contra en consultas externas. Los problemas de performance se deben principalmente al hecho de que el optimizador producirá un plan suponiendo que se devuelva una sola fila, que no será necesariamente el plan más apropiado.

Como regla general, hemos encontrado que, cuando sea posible, las funciones con valores de tabla en línea deben usarse con preference a las de statement múltiple (cuando la UDF se utilizará como parte de una consulta externa) debido a estos posibles problemas de performance.

Hay otra diferencia. Una function en línea con valores de tabla puede insertse, actualizarse y eliminarse, como una vista. Se aplican restricciones similares: no se pueden actualizar las funciones mediante el uso de agregados, no se pueden actualizar las columnas calculadas, y así sucesivamente.

Sus ejemplos, creo, responden muy bien a la pregunta. La primera function se puede hacer como una única selección, y es una buena razón para usar el estilo en línea. El segundo podría hacerse probablemente como una sola statement (usando una subconsulta para get la date máxima), pero algunos codificadores pueden encontrar más fácil de leer o más natural para hacerlo en múltiples declaraciones como lo has hecho. Algunas funciones simplemente no se pueden hacer en una statement, y por lo tanto requieren la versión multi-statement.

Sugiero usar el más simple (en línea) siempre que sea posible, y usar declaraciones múltiples cuando sea necesario (obviamente) o cuando la preference / legibilidad personal lo haga saber la escritura adicional.

consulte la comparación de funciones con valores de tabla en línea y en varias instrucciones , puede encontrar buenas descripciones y puntos de reference de performance

Si va a hacer una consulta, puede join a su function de Inline Table Valued como:

 SELECT a.*,b.* FROM AAAA a INNER JOIN MyNS.GetUnshippedOrders() b ON az=bz 

incurrirá en gastos generales y correrá bien.

Si intenta usar su Table Multi Statement Table Valued en una consulta similar, tendrá problemas de performance:

 SELECT xa,xb,xc,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty FROM xxxx x 

porque ejecutará la function 1 vez por cada fila devuelta, a medida que el set de resultados sea grande, se irá haciendo cada vez más lento.