try 创建一个内联表值函数.例子:
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS TABLE
AS
RETURN
(
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
)
-- Then call like this, just as if it's a table/view just with a parameter
SELECT * FROM dbo.fxnExample(1)
如果查看SELECT的执行计划,则根本看不到该函数,实际上只会显示正在查询的基础表.这很好,因为这意味着在为查询生成执行计划时,将使用基础表的统计信息.
The thing to avoid would be a multi-statement table valued function as underlying table statistics will not be used and can result in poor performance due to a poor execution plan.
Example of what to avoid:
CREATE FUNCTION dbo.fxnExample (@Parameter1 INTEGER)
RETURNS @Results TABLE(Field1 VARCHAR(10), Field2 VARCHAR(10))
AS
BEGIN
INSERT @Results
SELECT Field1, Field2
FROM SomeTable
WHERE Field3 = @Parameter1
RETURN
END
在查询中使用该函数时,会有细微的不同,但在性能上可能会有很大的差异.