在表值 UDF 中断言参数
有没有办法在表值 UDF 的参数上创建“断言”。
出于性能原因，我想使用表值 UDF，但我知道某些参数组合（例如相隔一个月以上的开始日期和结束日期）会导致所有用户的服务器出现性能问题。
最终用户使用 UDF 通过 Excel 查询数据库。当数据对于 Excel 来说太大时，UDF（尤其是表值 UDF）非常有用。用户编写简单的 SQL 查询，将数据分组以减少行数。例如，用户可能对每周聚合而不是每小时聚合感兴趣。用户编写一个group by SELECT语句将行数减少24x7=168倍。我知道我可以在多语句 UDF 中编写 RAISERROR 语句，但表值 UDF 集成在查询优化器中，因此使用表值 UDF 时这些查询会更高效。
那么，我可以对传递给表值 UDF 的参数定义断言吗？
Is there a way to create "asserts" on the parameters of a table-valued UDF.
I'd like to use a table-valued UDF for performance reasons, however I know that certain parameter combinations (like start and end dates that are more than a month apart) will cause performance issues on the server for all users.
End users query the database via Excel using UDFs. UDFs (and table-valued UDFs in particular) are useful when the data is too large for Excel. Users write simple SQL queries that categorizes the data into groups to reduce the number of rows. For example, the user may be interested in weekly aggregates rather than hourly ones. Users write a group by SELECT statement to reduce the rows by 24x7=168 times. I know I can write RAISERROR statements in multistatement UDFs, but table-valued UDFs are integrated in the query optimizer so these queries are more efficient with table-valued UDFs.
So, can I define assertions on the parameters passed to a table-valued UDF?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
简短的回答是否定的 - 单个语句 TVF 只能包含单个语句。
您可以尝试几种替代方案。一种方法是通过扩展
WHERE子句来对 SQL 语句中的参数进行验证 - 例如，由于以下几个原因，这可能并不理想 - 首先，它可能会导致用户认为没有存在符合其标准的数据，因为无法传达为什么没有返回结果。其次，无法保证数据库引擎在评估参数之前不会运行查询的数据部分。第三，它可能导致错误的计划被缓存。
如果您使用的是 SQL 2008，另一种方法是查看 SQL 服务器资源管理器提供了一种方法来限制用户或用户组运行估计执行时间（以秒为单位）小于给定阈值的查询。
另一种方法是在用户用于查询的 Excel 工作表中构建一些参数验证，但这可能不实用，具体取决于设置的详细信息。
The short answer is no - single statement TVFs can only contain a single statement.
There are a couple of alternatives you could try. One would be to carry out validation of the parameters within the SQL statement by extending the
WHEREclause - likeThis may not be ideal for a couple of reasons - first, it may lead the users to think that no data exists meeting their criteria since there's no means to communicate why no results have been returned. Second, there's no guarantee that the DB engine won't run the data parts of the query anyway before evaluating the parameters. Thirdly, it may lead to a bad plan being cached.
If you're on SQL 2008, an alternative approach would be to look into the SQL server resource govenor which provides a means to limit users or groups of users to running queries for which the estimated execution time in seconds is less than a given threshold.
Another approach again would be to build some parameter validation into the Excel sheets your users use for their queries, but this may not be practical depending on the details of your setup.