将 SELECT 移至 SQL Server 端

发布于 2024-10-10 00:20:09 字数 906 浏览 4 评论 0原文

我有一个 SQLCLR 触发器。它内部包含一个大而混乱的 SELECT，其中包含以下部分：

(CASE WHEN EXISTS(SELECT * FROM INSERTED I WHERE I.ID = R.ID)
THEN '1' ELSE '0' END) AS IsUpdated -- Is selected row just added?

以及 JOIN 等。我喜欢将结果作为包含所有内容的单个表。

问题 1。我可以将此 SELECT 移动到 SQL Server 端吗？如果是，该怎么做？

说“移动”，我的意思是创建一个存储过程或其他可以在 while 循环中读取数据集之前执行的东西。

仅当答案为“是”时，以下两个问题才有意义。

为什么我要移动 SELECT？首先，我不喜欢将 SQL 与 C# 代码混合在一起。其次，我认为服务器端查询运行得更快，因为服务器有更多机会缓存它们。

问题 2。我说得对吗？这是某种优化吗？

此外，SELECT 包含常量字符串，但它们是可本地化的。例如，

WHERE R.Status = "Enabled"

“Enabled”应该更改为法语、德语等。因此，我想编写 2 个静态方法——OnCreate 和 OnDestroy——然后将它们标记为存储过程。在服务器端注册/取消注册我的程序集时，只需分别调用它们即可。在 OnCreate 中，格式化 SELECT 字符串，将 {0}、{1}... 替换为程序集资源中所需的值。然后我只能本地化资源，而不是每个脚本。

问题3。这是个好主意吗？是否存在现有属性来标记 SQL Server 在（取消）注册程序集后自动执行的方法？

问候，

原文

I have an SQLCLR trigger. It contains a large and messy SELECT inside, with parts like:

(CASE WHEN EXISTS(SELECT * FROM INSERTED I WHERE I.ID = R.ID)
THEN '1' ELSE '0' END) AS IsUpdated -- Is selected row just added?

as well as JOINs etc. I like to have the result as a single table with all included.

Question 1. Can I move this SELECT to SQL Server side? If yes, how to do this?

Saying "move", I mean to create a stored procedure or something else that can be executed before reading dataset in while cycle.

The 2 following questions make sense only if answer is "yes".

Why do I want to move SELECT? First off, I don't like mixing SQL with C# code. At second, I suppose that server-side queries run faster, since the server have more chances to cache them.

Question 2. Am I right? Is it some sort of optimizing?

Also, the SELECT contains constant strings, but they are localizable. For instance,

WHERE R.Status = "Enabled"

"Enabled" should be changed for French, German etc. So, I want to write 2 static methods -- OnCreate and OnDestroy -- then mark them as stored procedures. When registering/unregistering my assembly on server side, just call them respectively. In OnCreate format the SELECT string, replacing {0}, {1}... with required values from the assembly resources. Then I can localize resources only, not every script.

Question 3. Is it good idea? Is there an existing attribute to mark methods to be executed by SQL Server automatically after (un)registartion an assembly?

Regards,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

所有深爱都是秘密 2024-10-17 00:20:09

嗯，SQL-CLR 触发器还将在服务器进程内在服务器上执行 - 所以这也是服务器端的，没有任何好处。

但我同意 - 触发器应该尽可能用 T-SQL 编写 - 在 C# 中使用触发器并没有真正的大好处......你能展示整个触发器代码吗？除非它包含非常奇怪的东西，否则它应该很容易转换为 T-SQL。

我不明白如何将 SELECT “移动”到 SQL 端并将其余代码保留在 C# 中 - 触发器要么在 T-SQL 中（我的偏好），要么在 C#/SQL-CLR 中- 我不认为有任何方法可以“混合搭配”。

回复收藏 0 原文

日记撕了你也走了 2024-10-17 00:20:09

首先，您可能不需要在您正在执行的任何查询中执行此类子查询。 INSERTED 表仅包含已更新的行（或已插入的行，但我们可以根据代码中的注释假设这是一个 UPDATE 触发器）。因此，您可以选择 INNER JOIN，仅匹配表中别名为“R”的行，也可以使用 LEFT JOIN，您可以知道 R 中的哪些行已更新，因为所有列显示 NULL 的行未更新。

问题1）正如下面marc_s所说，触发器在数据库的上下文中执行。但它还不止于此。所有与数据库相关的代码，包括 SQLCLR 在数据库中执行。这里没有客户端。这是大多数人在使用 SQLCLR 时遇到的问题：它在 SQL Server 上下文中运行。关于想要从触发器调用存储过程：可以做到，但 INSERTED 和 DELETED 表仅存在于触发器本身的上下文中。

问题2）看来这个问题应该以“Also, the SELECT”开头。这里有两件事需要考虑。首先，在测试“状态”值（或任何查找值）时，因为这不会向用户显示，所以您应该使用数值。 “启用”的“状态”应该类似于“1”，以便语言不相关。附带的好处是，不仅将状态值存储为数字占用的空间少得多，而且比较速度也快得多。其次，要向用户显示的任何需要对语言差异敏感的文本都应该位于表中，以便您可以传入 LanguageId 或 LocaleId 来获取要显示的适当的法语、德语等字符串。一般可以在另一个表中设置用户或系统的LocaleId。

问题 3) 如果“注册”是指程序集已创建或已删除，那么您可以通过 DDL 触发器捕获这些事件。您可以在此处查看一些基础知识：

http:// msdn.microsoft.com/en-us/library/ms175941(v=SQL.90).aspx

但 CREATE ASSEMBLY 和 DROP ASSEMBLY 是可捕获的事件。

如果您说的是何时从内存中加载和卸载程序集，那么我不知道有什么方法可以捕获它。

To start with, you probably do not need to do that type of subquery inside of whatever query you are doing. The INSERTED table only has rows that have been updated (or inserted but we can assume this is an UPDATE Trigger based on the comment in your code). So you can either INNER JOIN and you will only match rows in the Table with the alias of "R" or you can LEFT JOIN and you can tell which rows in R have been updated as the ones showing NULL for all columns were not updated.

Question 1) As marc_s said below, the Trigger executes in the context of the database. But it goes beyond that. ALL database related code, including SQLCLR executes in the database. There is no client-side here. This is the issue that most people have with SQLCLR: it runs inside of the SQL Server context. And regarding wanting to call a Stored Proc from the Trigger: it can be done BUT the INSERTED and DELETED tables only exist within the context of the Trigger itself.

Question 2) It appears that this question should have started with the words "Also, the SELECT". There are two things to consider here. First, when testing for "Status" values (or any Lookup values) since this is not displayed to the user you should be using numeric values. A "status" of "Enabled" should be something like "1" so that the language is not relevant. A side benefit is that not only will storing Status values as numbers take up a lot less space, but they also compare much faster. Second is that any text that is to be displayed to the user that needs to be sensitive to language differences should be in a table so that you can pass in a LanguageId or LocaleId to get the appropriate French, German, etc. strings to display. You can set the LocaleId of the user or system in general in another table.

Question 3) If by "registration" you mean that the Assembly is either CREATED or DROPPED, then you can trap those events via DDL Triggers. You can look here for some basics:

http://msdn.microsoft.com/en-us/library/ms175941(v=SQL.90).aspx

But CREATE ASSEMBLY and DROP ASSEMBLY are events that are trappable.

If you are speaking of when Assemblies are loaded and unloaded from memory, then I do not know of a way to trap that.

回复收藏 0 原文