在 SQL Server 2008 中创建参数化 VIEW [英] Create parameterized VIEW in SQL Server 2008

问题描述

我们可以在 SQL Server 2008 中创建参数化视图吗.

Can we create parameterized VIEW in SQL Server 2008.

或者任何其他替代方案?

Or Any other alternative for this ?

推荐答案

尝试创建内联表值函数.示例:

Try creating an inline table-valued function. Example:

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 的执行计划，您根本不会看到该函数的提及，而实际上只会向您显示正在查询的基础表.这很好，因为这意味着在为查询生成执行计划时将使用基础表的统计信息.

If you view the execution plan for the SELECT you will not see a mention of the function at all and will actually just show you the underlying tables being queried. This is good as it means statistics on the underlying tables will be used when generating an execution plan for the query.

要避免的是多语句表值函数，因为不会使用底层表统计信息，并且可能由于执行计划不佳而导致性能不佳.
避免的示例:

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

略有不同，但在查询中使用该函数时，性能可能会有很大差异.

Subtly different, but with potentially big differences in performance when the function is used in a query.

这篇关于在 SQL Server 2008 中创建参数化 VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！