在 SQL Server 2008 中创建参数化 VIEW

本文介绍了在 SQL Server 2008 中创建参数化 VIEW的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我们可以在 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.