blocks|key|1411627|text|你有id和date的索引吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1411628|也许你只是想：|1411629|select+top+1++1+from+myTable+where+id=7+and+rowInsertDate+>+'01/01/2009'+|code-block|syntax|javascript|1411630|注意:如果数据存在，则返回1，否则不返回任何内容。|1411631|另一次编辑。如果没有数据，这将不会返回值为null的行，而不会返回任何行。更像是null，因为它更具比喻意义。|1411632|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Do you have an index on id and date?

maybe you just want:

<pre><code>select top 1 1 from myTable where id=7 and rowInsertDate &gt; '01/01/2009' 
</code></pre>

note: this would return 1 if data exists, or nothing otherwise.

another edit. This won't return a row with the value null if there is no data, but rather will not return any rows. More like null in its more figurative sense.

blocks|key|1196395|text|IF+EXISTS应该更高效，因为它被优化为在找到第一行时立即停止。这就是我总是做这种检查的方式，而不是使用COUNT()。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196396|为了进行性能比较，只需在每次测试之前清除数据和执行计划缓存(仅限非生产数据库服务器)，以确保您的测试是公平的：|1196397|DBCC+FREEPROCCACHE
DBCC+DROPCLEANBUFFERS|code-block|syntax|javascript|1196398|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

IF EXISTS should be more efficient, because it is optimised to stop as soon as it find the first row. This is how I would always do this kind of check, not using a COUNT().

For performance comparison, just ensure you are testing fairly by clearing down the data and execution plan caches (non-production db server only) before each test:

<pre><code>DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
</code></pre>

blocks|key|1196454|text|如果你不需要376986行，只想知道是否存在，那么如果存在就更有意义了。此外，另一个有用的做法是要求索引列(主键)而不是*，因为您并不关心实际数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196455|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you don't need 376986 rows and just want to know if something exists then IF EXISTS makes a lot more sense.
Also, another helpful bit is to ask for an indexed column (primary key) instead of * because you don't care about the actual data.

blocks|key|1411751|text|这是我能在我的项目中获得的最快速度：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1411752|SELECT+CASE+WHEN+EXISTS+(
++select+top+1+1+
++from+myTable+
++where+id=7+
++and+rowInsertDate+BETWEEN+'01/01/2009'+AND+GETDATE()
)+THEN+1+ELSE+0+END+AS+AnyData|code-block|syntax|javascript|1411753|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|$]]

This is the fastest i could get in my projects: 

<pre><code>SELECT CASE WHEN EXISTS (
 select top 1 1 
 from myTable 
 where id=7 
 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
) THEN 1 ELSE 0 END AS AnyData
</code></pre>

blocks|key|1196534|text|首先，您应该尝试虚拟一个数据库，其中包含您认为您(或您的继任者)在两年内可能需要处理的数据。那么你的测试将会更有效率。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196535|IF+EXISTS()会更快，因为数据库引擎只需找到符合条件的第一条匹配记录。当然，如果有适当的索引，它会更快。|1196536|另一个提示是，不要使用*，因为实际上不需要检索列。|1196537|IF+EXISTS(select+1+from+myTable+where+id=7+and+rowInsertDate+BETWEEN+'01/01/2009'+AND+GETDATE())|code-block|syntax|javascript|1196538|...should+(据我所知)运行得更快一些。|1196539|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

First off, you should try to dummy up a database containing as much data as you think you (or your successors) might have to deal with in two years. Then your tests will be a lot more productive.

IF EXISTS() will be faster, since the database engine only has to find a first matching record to your criteria. It will of course be faster still with proper indexes.

Another hint, don't use *, since you don't actually need to retrieve columns.

<pre><code>IF EXISTS(select 1 from myTable where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE())
</code></pre>

...should (from what I've read) work a bit faster.

blocks|key|1411889|text|我只会这样写：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1411890|IF+EXISTS(
++++++SELECT+0+FROM+myTable+
++++++WHERE+id=7+and+rowInsertDate+BETWEEN+'01/01/2009'+AND+GETDATE()
)
SELECT+1
ELSE
SELECT+0|code-block|syntax|javascript|1411891|这样，您就不会返回任何数据，只需检查条件。我发现这种查询结构非常快。|1411892|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I would just write it this way :

<pre><code>IF EXISTS(
 SELECT 0 FROM myTable 
 WHERE id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0
</code></pre>

That way you don't return any data just check for conditions. I find this query structure super fast.

blocks|key|1411971|text|我认为Alex+Bagnolini的答案是正确的。系统不允许我对他的回答(新账户)发表评论。我要做的唯一修改是将第二个1改为id。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1411972|有时，减少项目部分(即列列表)中的列表可以让db引擎只命中索引，而不是表，因此速度更快。这取决于您的DB引擎，当然还有索引结构/大小。(所有rowInsertDate日期都应该是<+getDate()，所以您可以跳过该比较)|1411973|SELECT+CASE+WHEN+EXISTS+(+select+top+1+id+from+myTable+where+id=7+and+rowInsertDate+>+'01/01/2009‘)然后1或0以AnyData结尾|1411974|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I think Alex Bagnolini's answer is correct. The system wouldn't let me comment on his answer (new acct). The only modification I'd make is to change the second 1 to id. 

Sometimes reducing the list in the project section (that's the column list) allows the db engine to hit the index only, and not the table, thus faster. This depends on your DB engine, and index structure/size, of course. (all rowInsertDate dates should be &lt; getDate(), so you can skip that comparison)

SELECT CASE WHEN EXISTS (
 select top 1 id 
 from myTable 
 where id=7 
 and rowInsertDate > '01/01/2009' 
) THEN 1 ELSE 0 END AS AnyData

blocks|key|1196823|text|我将只返回select+exists，而不是将其设置为int|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196824|SELECT+EXISTS(SELECT+1+FROM+MY_TABLE);|code-block|syntax|javascript|1196825|它将返回：|1196826|​|1196827|📷|atomic|offset|length|1196828|1196829|如果您需要一个自定义名称，那么您可以这样做|1196830|SELECT+EXISTS(SELECT+1+FROM+MY_TABLE)+AS+MY_CUSTOM_NAME;|1196831|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/83cc5f5b2f3e1c3bf5691d110d54eaa6.png|imageAlt^0|0|0|0|0|0|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|15|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|16|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|17|8|@]|9|@$N|18|O|19|1|1A]]|A|$]]|$1|P|3|J|5|6|7|1B|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1C|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11|12|-4]]]]

I would just return select exists instead of making it an int
<pre class="lang-sql prettyprint-override"><code>SELECT EXISTS(SELECT 1 FROM MY_TABLE);
</code></pre>
It will return:
<a href="https://i.stack.imgur.com/I4quj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/I4quj.png" alt="Select result, showing single row result with column exists being true" /></a>
If you need a custom name then you can do
<pre class="lang-sql prettyprint-override"><code>SELECT EXISTS(SELECT 1 FROM MY_TABLE) AS MY_CUSTOM_NAME;
</code></pre>

I would like to write a query that simply returns 1 or 0 depending if there will be results. 

I am thinking to use this

<pre><code>IF EXISTS(
 select * from myTable 
 where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0
</code></pre>

That's the general premise.

The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql

My question is lets say the 'count' would return 376986 and takes 4 seconds to calculate. Is using the IF EXISTS going to stop as soon as it find 1 row that satisfies the criteria.

I'm deciding wether to use IF EXISTS or just query the @@ROWCOUNT and see if it is greater than zero.

I did try some tests and both pretty much ran at the same speed but in 2 years time when there's alot more data is it likely using IF EXISTS is going to be a performance gain or not?

Thanks

Efficient way to check if a SQL query will return results

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我想写一个简单返回1或0的查询，这取决于是否有结果。我在考虑用这个IF EXISTS(      select * from myTable       where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE())SELECT 1ELSESELECT ...

问检查SQL查询是否返回结果的有效方法
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检查SQL查询是否返回结果的有效方法EN