哪个更好 select 1 与 select * 来检查记录的存在? [英] Which is better select 1 vs select * to check the existence of record?

问题描述

在以下情况下哪个更好.

Which is better in below cases.

1.

IF EXISTS(SELECT * FROM Table WHERE ID = 3)
BEGIN
    -------
END

对比

2.

IF EXISTS(SELECT 1 FROM Table WHERE ID = 3)
BEGIN
    -------
END

还是两者都一样?

推荐答案

EXISTS 将检查集合中是否存在任何记录.因此，如果您从 100 万条记录中进行 SELECT 或从 1 条记录中进行 SELECT(假设使用 TOP 1)，它们将具有相同的结果和相同的性能，甚至相同的执行计划.(为什么?)因为存在不会等到 100 万条记录扫描完成(或 1 条记录扫描完成).每当它在集合中找到一条记录时，它都会返回结果为 TRUE(在这种情况下，无论您使用 * 还是列名都将具有相同的性能结果).

EXISTS will check if any record exists in a set. so if you are making a SELECT from 1 million records or you are making a SELECT from 1 record(let say using TOP 1), they will have same result and same performance and even same execution plan.(why?) Because exists will not waits until 1 million record scan complete(or 1 record scan complete). Whenever it finds a record in a set, it will be return the result as TRUE(There is no matter in this case you are using * or column name both will have same performance result).

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT TOP 1 * FROM dbo.titleauthor)
PRINT 'b'

下面是这些查询的执行计划(因为我有屏幕尺寸问题，我已经裁剪了它的图像)

below is the execution plan for these queries(as I have Screen size problem, I have cropped it's image)

但是这个场景和性能甚至执行计划都会完全改变，当你使用如下查询时(我不知道为什么要使用这个查询！):

But this scenario and performance and even execution plan will be completly change, when you are using queries as follow(I do not know why should use this query!):

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT 1 )
PRINT 'b'

在这种情况下，由于 SQL Server 不需要在第二次查询中执行任何扫描操作，因此执行计划将更改如下:

in this scenario, as SQL Server does not need to perform any scan operation in second query, then the execution plan will be changed as follow:

这篇关于哪个更好 select 1 与 select * 来检查记录的存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！