如何指定SQLCLR表值函数是有序的?

时间：2021-05-06 18:02:27

I have a sqlclr table valued function that has a prototype like

我有一个sqlclr表值函数，它有一个类似的原型

CREATE FUNCTION [dbo].[some_func]
(...)
RETURNS TABLE (
        [order_id]      INT NULL,
        [value1]        VARCHAR(50) NULL,
        [value2]        INT NULL
)
AS EXTERNAL NAME [some_db].[MyProject].[some_func]

The actual function doesn't matter. Now I know that my function always returns the results in sequential order based on order_id.

实际的函数并不重要。现在我知道我的函数总是以基于order_id的顺序返回结果。

I'm using this function and joining it against a table which is indexed on order_id and when I join the two and look at the plan I see that the join has caused a spill to do a sort.

我使用这个函数并将它与order_id上的索引的表连接在一起，当我加入这两个并查看计划时，我看到这个连接导致了一个溢出来进行排序。

Now, I found this article which told me that I could use modify the prototype and specify order.

现在，我找到了这篇文章，它告诉我可以使用修改原型和指定顺序。

http://www.sqlskills.com/blogs/bobb/sql-server-2008-ordered-sqlclr-table-valued-functions/

http://www.sqlskills.com/blogs/bobb/sql -服务器- 2008命令sqlclr -表-价值- functions/

Changing the prototype manually does produce the desired result

手动更改原型确实产生了预期的结果。

CREATE FUNCTION [dbo].[some_func]
(...)
RETURNS TABLE (
        [order_id]      INT NULL,
        [value1]        VARCHAR(50) NULL,
        [value2]        INT NULL
)
ORDER(order_id asc)
AS EXTERNAL NAME [some_db].[MyProject].[some_func]

This version of the func does not need to sort and no spill happens in tempdb. However, we regularly deploy our sql project through SSDT, and everytime we do it automatically generates the prototype. I have no way to control what get set. Is there an option in my cs function so that I can specify that the result of some_func is ordered?

这个版本的func不需要排序，并且在tempdb中不会发生泄漏。然而，我们经常通过SSDT部署我们的sql项目，每次我们这样做时，它都会自动生成原型。我无法控制什么是get set, cs函数中是否有一个选项可以指定some_func的结果是有序的?

1 个解决方案

#1

3  

Unfortunately no, SSDT does not have a mechanism (i.e. Attribute such as SqlFacet) for supporting this option (nor several others). Your options are:

不幸的是，SSDT没有支持这个选项的机制(也没有其他的机制)(比如SqlFacet)。你的选择是:

  1. Create a post-deployment SQL Script to issue an ALTER FUNCTION statement that is exactly what you want it to be. Just add a SQL script to your project, and set its "Build Action" to "PostDeploy".

    创建一个部署后SQL脚本来发出一个ALTER FUNCTION语句，这正是您想要的。只需向项目添加一个SQL脚本，并将其“构建操作”设置为“PostDeploy”。

  2. Generate the DDL (i.e. CREATE FUNCTION...) yourself and use a "Post-build event" (under Project Properties | Build Events) to execute the SQL via SQLCMD

    自己生成DDL(即CREATE FUNCTION…)，并使用“后构建事件”(在项目属性|构建事件下)通过SQLCMD执行SQL

  3. Create your own attribute that you can mark the function with, and then create a Deployment Contributor ("Build Action" = "Deployment Extension Configuration"). This would allow it to be handled inline via SSDT, but seems to be a fair bit of work.

    创建可以标记函数的属性，然后创建部署贡献者(“构建动作”=“部署扩展配置”)。这将允许通过SSDT内联地处理它，但这似乎是相当大的工作量。

Other options not supported via SSDT (please vote for them to be supported via the following links :-):

其他未经SSDT支持的选项(请通过以下链接投票支持:-):

Due to all of these unsupported options, I rarely use SSDT for the actual deployment, and when I do, I use Post Deployment SQL scripts to do the ALTER statements (though they are not dynamic, which is why it would be better to be supported via an Attribute in the code). Most of the time I just use my own deployment script ( .CMD ) that I trigger as a Post Build event.

由于所有这些不受支持的选项,我很少使用实际部署SSDT,当我做的,我使用后部署SQL脚本ALTER语句(尽管他们不是动态的,这就是为什么它会更好支持通过一个属性在代码中)。大多数时候，我只是使用我自己的部署脚本(. cmd)作为后构建事件触发。

P.S. I have now submitted a Microsoft Connection Suggestion for this particular feature:
SSDT - Support ORDER clause for SQLCLR TVFs via the SqlFunction attribute when generating the publish and create SQL scripts

我现在已经为这个特性提交了一个Microsoft连接建议:在生成发布和创建SQL脚本时，通过SqlFunction属性为SQLCLR TVFs提供SSDT - Support ORDER子句

#1

3  

Unfortunately no, SSDT does not have a mechanism (i.e. Attribute such as SqlFacet) for supporting this option (nor several others). Your options are:

不幸的是，SSDT没有支持这个选项的机制(也没有其他的机制)(比如SqlFacet)。你的选择是:

  1. Create a post-deployment SQL Script to issue an ALTER FUNCTION statement that is exactly what you want it to be. Just add a SQL script to your project, and set its "Build Action" to "PostDeploy".

    创建一个部署后SQL脚本来发出一个ALTER FUNCTION语句，这正是您想要的。只需向项目添加一个SQL脚本，并将其“构建操作”设置为“PostDeploy”。

  2. Generate the DDL (i.e. CREATE FUNCTION...) yourself and use a "Post-build event" (under Project Properties | Build Events) to execute the SQL via SQLCMD

    自己生成DDL(即CREATE FUNCTION…)，并使用“后构建事件”(在项目属性|构建事件下)通过SQLCMD执行SQL

  3. Create your own attribute that you can mark the function with, and then create a Deployment Contributor ("Build Action" = "Deployment Extension Configuration"). This would allow it to be handled inline via SSDT, but seems to be a fair bit of work.

    创建可以标记函数的属性，然后创建部署贡献者(“构建动作”=“部署扩展配置”)。这将允许通过SSDT内联地处理它，但这似乎是相当大的工作量。

Other options not supported via SSDT (please vote for them to be supported via the following links :-):

其他未经SSDT支持的选项(请通过以下链接投票支持:-):

Due to all of these unsupported options, I rarely use SSDT for the actual deployment, and when I do, I use Post Deployment SQL scripts to do the ALTER statements (though they are not dynamic, which is why it would be better to be supported via an Attribute in the code). Most of the time I just use my own deployment script ( .CMD ) that I trigger as a Post Build event.

由于所有这些不受支持的选项,我很少使用实际部署SSDT,当我做的,我使用后部署SQL脚本ALTER语句(尽管他们不是动态的,这就是为什么它会更好支持通过一个属性在代码中)。大多数时候，我只是使用我自己的部署脚本(. cmd)作为后构建事件触发。

P.S. I have now submitted a Microsoft Connection Suggestion for this particular feature:
SSDT - Support ORDER clause for SQLCLR TVFs via the SqlFunction attribute when generating the publish and create SQL scripts

我现在已经为这个特性提交了一个Microsoft连接建议:在生成发布和创建SQL脚本时，通过SqlFunction属性为SQLCLR TVFs提供SSDT - Support ORDER子句

标签：

相关文章