SQL Server 2008 R2用户定义函数（表值）性能(SQL Server 2008R2 User Defined Function (Table valued) Performance)

 我正在使用SQL Server 2008R2。 
 
 我有以下设置： 
 
-- Query #1
SELECT * FROM
Product P
INNER JOIN ProductComments C ON C.ProductId = P.ProductId

-- Query #2
SELECT * FROM 
GetAllProducts() P
CROSS APPLY GetCommentsOfProduct(P.ProductId) C
 
 GetAllProducts()在哪里 
 
CREATE FUNCTION GetAllProducts
(      
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM Product
)
 
 和GetCommentsOfProduct(P.ProductId)是 
 
CREATE FUNCTION [dbo].[GetCommentsOfProduct]
(   
    @ProductId int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM ProductComments WHERE ProductId = @ProductId
)
 
 查询＃1和查询＃2产生完全相同的实际执行计划并返回相同的结果。 他们的查询成本相对于批次都是50％。 这是否意味着SQL查询优化器将这些查询转换为相同的查询？ 为什么调用UDF没有任何开销？ 这是否意味着我可以将所有表格构造为UDF，并且即使在执行JOIN操作时也不会有性能问题？ 你知道任何缺点吗？ 

I am using SQL Server 2008R2.
 
I've got the following setup:
 
-- Query #1
SELECT * FROM
Product P
INNER JOIN ProductComments C ON C.ProductId = P.ProductId

-- Query #2
SELECT * FROM 
GetAllProducts() P
CROSS APPLY GetCommentsOfProduct(P.ProductId) C
 
where GetAllProducts() is
 
CREATE FUNCTION GetAllProducts
(      
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM Product
)
 
and GetCommentsOfProduct(P.ProductId) is
 
CREATE FUNCTION [dbo].[GetCommentsOfProduct]
(   
    @ProductId int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM ProductComments WHERE ProductId = @ProductId
)
 
Both query #1 and query #2 result in the exact same actual execution plan and return the same result. Their query costs are both 50% relative to batch. Does this mean, that the SQL query optimizer translates these queries into the same query? And why is there no overhead for calling the UDFs? And does it mean I can structure all of my tables into UDFs and will have no performance issues even when doing JOIN operations? Do you know of any downsides?

原文：https://stackoverflow.com/questions/8789461