All geek questions in one place

What is better to choose 1 vs select * to check for a record?

Which is better in the following cases.

1.

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

Vs

2.

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

Or are both the same?

+5
source share
1 answer

EXISTS checks to see if there is any record in the set. therefore, if you make SELECT from 1 million records or you make SELECT from 1 record (say using TOP 1), they will have the same result and the same performance and even the same execution plan. (why?) Because there will be no waiting until 1 million record scans are over (or 1 record is completed). Whenever it finds an entry in the set, it will return the result as TRUE (in this case you are not using * or the column name, both will have the 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 a plan to fulfill these requests (since I have a problem with screen size, I cropped this image) enter image description hereenter image description here

But this scenario, performance, and even the execution plan will be completely changed when you use the queries as they should (I don't know why this query should be used!):

 USE pubs GO IF EXISTS(SELECT * FROM dbo.titleauthor) PRINT 'a' IF EXISTS(SELECT 1 ) PRINT 'b'

in this scenario, since SQL Server does not need to perform any scan operation in the second query, then the execution plan will be changed as follows: enter image description hereenter image description here

+6
source

Source: https://habr.com/ru/post/1206400/

More articles:


All Articles