blocks|key|973518|text|您不应该假设它只运行一次。可能是这样，但行为可能与计划有关，以后的开发人员也会有同样的问题。相反，正如@Larnu建议的那样，将函数推到子查询/cte中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|973519|with+q+as
(
++SELECT+func(col1,+col2)+AS+Score
++FROM+table
),
select+*
INTO+#A
from+q
WHERE+Score+>+-1;|code-block|syntax|javascript|973520|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You should not assume that it will be run only once. It may be, but the behavior may be plan-dependent, and later devs will have the same question. Instead, as @Larnu suggests, push function into a subquery/cte.
<pre><code>with q as
(
 SELECT func(col1, col2) AS Score
 FROM table
),
select *
INTO #A
from q
WHERE Score &gt; -1;
</code></pre>

blocks|key|973563|text|如果您的函数开销很大，并且唯一对(col1、col2)的数量明显少于输入记录的数量，请使用以下方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|973564|select+B.score+
from+
(
++++select+func(A.col1,A.col2)+as+score
++++from+
++++(
++++++++select+distinct+col1,+col2+from+table+
++++)+A
++++where+func(A.col1,A.col2)+>+-1
)+B
where+B.score+>+-1|code-block|syntax|javascript|973565|我认为在引擎创建查询执行计划时，函数是否是确定性的非常重要。|973566|我从经验中知道(使用大型表)临时表比子查询和CTE的解决方案更快，您可以尝试使用#temptables重写建议的查询。|973567|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If your function is expensive and the number of unique pairs (col1, col2) is significantly less than the number of input records, try using this:
<pre><code>select B.score 
from 
(
 select func(A.col1,A.col2) as score
 from 
 (
 select distinct col1, col2 from table 
 ) A
 where func(A.col1,A.col2) &gt; -1
) B
where B.score &gt; -1
</code></pre>
I think whether the function is deterministic or not - can be important when creating a query execution plan by the engine.
I know from experience that (with large tables) temporary tables can be a faster solution than subqueries and CTE's, you can try rewrite proposed query using #temptables.

blocks|key|854604|text|我编写了带有两个参数的简单函数，并返回一个整数。函数写入带有指定参数的外部文本文件，我能够跟踪它被调用了多少次。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|854605|结论是:调用总数等于输入记录数和符合WHERE子句中指定标准的记录数之和。|854606|选择不知道在哪里做什么。确定性/非确定性函数标志不会改变任何事情。|854607|当然-这只是一个简单的练习，我不是一个杰出的/认证的微软系统理论家。|854608|我担心--如果你不能重写你的CLR+--你不会得到更多，也许并行查询可以加快速度？|854609|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I wrote simple function taking two parameters and returning an integer. The function writes to external text file that was called with specified parameters, I was able to track how many times it was called.
Conclusion is: total number of calls is equal to sum of number of input records and number of records that meet criteria specified in WHERE clause.
SELECT don't know what WHERE is doing. Deterministic / Nondeterministic function flags doesn't change anything.
Of course - it's just a simple exercise, and I'm not an outstanding/certified Microsoft systems theorist.
I'm afraid - if you are not able to rewrite your CLR - you won't get much more, though, maybe parallel queries could speed things up ?

blocks|key|854641|text|如果您可以合理地将IsDeterministic和IsPrecise属性放在函数上(非常肯定IsDeterministic，非常肯定IsPrecise'；我现在无法找到有关需求的文档)，那么您可以向表中添加定义为func(col1,+col2)的计算列并对其进行索引。它将使函数在查询时不被调用，而是在插入/更新行时调用。我的建议是尝试在运行之前添加计算列并将其索引到表的一个小版本上。这就是：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|854642|select+top(100)+*+into+dbo.TestTable+from+dbo.YourTable;|unordered-list-item|854643|alter+table+dbo.TestTable+add+NewColumn+as+dbo.func(col1,+col2);|854644|create+index+FuncIndex+on+dbo.TestTable+(NewColumn);|854645|和/或，如果您有一个非生产环境，做它的活桌子在那里。|854646|如果对该列上可能查询的谓词进行筛选是有意义的，则可以对索引进行筛选。但这是一个普遍的索引问题，而不是特定于您的情况。|854647|entityMap|0|LINK|mutability|MUTABLE|url|https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlfunctionattribute?view=dotnet-plat-ext-6.0^0|2Y|G|12|2|0|0|0|1K|0|0|1S|0|0|1G|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|H|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|I|3|J|5|H|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|K|3|L|5|H|7|1A|8|@$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1D|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

If you can reasonably put the IsDeterministic and IsPrecise properties on your <a href="https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqlfunctionattribute?view=dotnet-plat-ext-6.0" rel="nofollow noreferrer">function</a> (very sure about IsDeterministic, pretty sure about IsPrecise'; I'm unable to find the relevant documentation right now on what the requirements are), then you can add a computed column to your table that is defined as <code>func(col1, col2)</code> and index it. The act of indexing it will make it so that the function won't be called at query time, but rather when rows are inserted/updated. My recommendation is to try adding the computed column and indexing it on a small version of your table before doing it live. That is:
<ul>
<li><code>select top(100) * into dbo.TestTable from dbo.YourTable;</code></li>
<li><code>alter table dbo.TestTable add NewColumn as dbo.func(col1, col2);</code></li>
<li><code>create index FuncIndex on dbo.TestTable (NewColumn);</code></li>
</ul>
And/or, if you have a non-production environment, do it on the live table there.
If predicates for likely queries on that column make sense to be filtered, you can make the index filtered. But that's a general indexing concern and not specific to your situation.

I have a C# CLR doing multiple string manipulations. It returns an int. I'm calling it with
<pre><code>SELECT func(col1, col2) AS Score
INTO #A
FROM table;

SELECT *
INTO #B
FROM #A
WHERE Score &gt; -1
</code></pre>
If I do something like
<pre><code>SELECT func(col1, col2) AS Score
INTO #A
FROM table
WHERE func(col1, col2) &gt; -1;
</code></pre>
Is the CLR called and executed two times?
In terms of performance/speed is there a better way to get the same result?
col1 is <code>nvarchar(max)</code>, col2 is <code>nvarchar(800)</code>. The function contains business logic.
There are about 10 billion rows/calculations

Using C# CLR in SELECT and WHERE at the same time

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个C# CLR执行多个字符串操作。它返回一个int。我用的是SELECT func(col1, col2) AS ScoreINTO #AFROM table;SELECT *INTO #BFROM #AWHERE Score > -1如果我做了这样的事SELECT func(col1, col2) AS Sco...

问同时在SELECT和WHERE中使用C# CLR
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问同时在SELECT和WHERE中使用C# CLREN