.net “选择顶部 1 1” VS “如果存在（选择 1”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11009022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"SELECT TOP 1 1" VS "IF EXISTS(SELECT 1"
提问by Eric Swanson
I have some .NET code that checks for the existence of a SQL record at a moderately-high interval. I am looking to make this check as "cheap" as possible.
我有一些 .NET 代码，它以中等高的间隔检查 SQL 记录的存在。我希望让这个支票尽可能“便宜”。
I'm wondering the characteristics of two queries:
我想知道两个查询的特征：
IF EXISTS(SELECT 1
FROM BigTable
WHERE SomeColumn = 200)
SELECT 1 AS FOUND
ELSE
SELECT 0 AS FOUND
VS
VS
SELECT TOP 1 1
FROM BigTable
WHERE SomeColumn = 200
They both produce similar execution plans. But the SELECT TOP 1 1 seems to execute faster: Less query to parse and when record is not found, it sends less down the pipe. I'm also assuming it runs faster at the client because I just need to check the Record Count, rather than marshaling the return value of IF EXISTS.
它们都产生相似的执行计划。但是 SELECT TOP 1 1 似乎执行得更快：要解析的查询更少，当没有找到记录时，它发送的管道更少。我还假设它在客户端运行得更快，因为我只需要检查记录计数，而不是封送 IF EXISTS 的返回值。
Most of the performance benefits are negligible. But if both consistently return the same result, then why not choose the slightlyfaster method?
大多数性能优势都可以忽略不计。但是如果两者始终返回相同的结果，那么为什么不选择稍微快一点的方法呢？
Is "SELECT TOP 1 1" THEE best way to check for an existence of a record in .NET?
“SELECT TOP 1 1”是检查 .NET 中记录是否存在的最佳方式吗？
(We use .NET 3.5, and I am trying to avoid LINQ because it is not used elsewhere in the application. We also have some legacy VB6 apps that we are migrating/rewriting, so they may need to execute this as well.)
（我们使用 .NET 3.5，我试图避免使用 LINQ，因为它没有在应用程序的其他地方使用。我们还有一些正在迁移/重写的旧版 VB6 应用程序，因此它们可能也需要执行此操作。）
EDIT: Just a little more detail on design. This record is a "header". There is another table that has child records that will be read/parsed when this header is found. The lack of a record is a good thing: there is no work to do.
编辑：关于设计的更多细节。该记录是“标题”。还有另一个表具有子记录，当找到此标头时将读取/解析这些子记录。没有记录是件好事：没有工作可做。
EDIT2: The lack of a record that meets the condition will occur more often. They come in sporadic waves.
EDIT2：缺少满足条件的记录会更频繁地发生。它们以零星的波涛汹涌而来。
采纳答案by Damien_The_Unbeliever
I'd recommend IF EXISTS(SELECT * ...), unless this is actually causing a performance issue. It expresses the intentof the query in a much better understood fashion than alternatives.
我建议IF EXISTS(SELECT * ...)，除非这实际上会导致性能问题。它以比替代方案更好理解的方式表达了查询的意图。
I'd avoid COUNT(*)(as in the current answers) unless you actually needthe count of rows from the table.
我会避免COUNT(*)（如当前答案），除非您确实需要表中的行数。
If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:
如果您想要从结果中检查行数的“效率”，我可能会选择：
select 1 where exists(select * from BigTable where SomeColumn=200)
Which produces the same result set as your second query (either 0 or 1 row)
产生与您的第二个查询相同的结果集（0 或 1 行）
回答by Preet Sangha
This is what you want instead of the IF statement
这是你想要的，而不是 IF 语句
SELECT ISNULL(
(SELECT TOP 1 1 FROM BigTable where SomeCol = 200), 0);
回答by Display name
Makes no difference at all, exists will not even evaluate the select portion of your statement. So, use the one you like.
根本没有区别，exists 甚至不会评估语句的 select 部分。所以，使用你喜欢的那个。
declare @test table (name varchar(20))
-- comment out inserts for testing.
insert into @test (name) values ('bob the builder')
insert into @test (name) values ('bob the builder')
-- for giggles, put 1/0 here. You'll find that divide by zero error.
select 1 from @test
-- notice that you don't receive a divide by zero error.
-- the bit in the select portion is never executed, ever.
if not exists (select 1/0 from @test) select 'Yay!'
if exists (select 1/0 from @test) select 'Boo!'
In fact you can use these interchangeably:
事实上，您可以互换使用这些：
... select * ...
... select 1 ...
... select top 1 * ...
... select top 1 1 ...
... select 'John Jacob Jingleheimer Schmidt' ...
回答by manchax
I would definitely go for the 2nd option:
我肯定会选择第二个选项：
SELECT TOP 1 1
FROM BigTable
WHERE SomeColumn = 200
The execution plan is simpler and efficient even when I/O and CPU numbers are mostly the same.
即使 I/O 和 CPU 数量大致相同，执行计划也更简单有效。
回答by James Anderson
Not sure about SQL Server but in other databases the standard method is:
不确定 SQL Server，但在其他数据库中，标准方法是：
SELECT COUNT(*) FROM BigTable where SomeCol = 200;
Actual efficiency depends on your indexes etc.
实际效率取决于您的索引等。
