count vs exists

Hi experts

i have this code
if exists
 (
      select 1
      from sales.SalesOrderDetail
      where ProductID = 970
 )
 print 'Exists = True'

 declare @rowcount int
 set @rowcount =
 (
      select count(*)
      from sales.SalesOrderDetail
      where ProductID = 970
 )
 if @rowcount > 0
 print 'Exists = True'

I need an example where you can check with the execution plan  or statistical is best to use EXISTS instead of COUNT
enrique_aeoAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Btw, you didn't clean any data buffer but stored procedure only. The correct command is DBCC DROPCLEANBUFFERS.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Not sure about the example, but remembering my training EXISTS is more efficient, as once the query engine finds a single row that meets the condition it will move on, whereas COUNT needs to consume the entire dataset and get the aggregated count, which takes longer.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jim is right but if you need to proof something then add SET STATISTICS IO ON to the beginning of the script so you can have the necessary information for reporting:

SET STATISTICS IO ON
...
--your code here
...
SET STATISTICS IO OFF
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
enrique_aeoAuthor Commented:
this is results

Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True


set statistics io on
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

but i do not understand
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
The table is already in cache on the 2nd interaction so you should clean the buffers between each interaction.
Also if the table is very small (like one extent data page) there's no difference between them since all records will be loaded into memory.
0
 
enrique_aeoAuthor Commented:
set statistics io on
DBCC FREEPROCCACHE
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

DBCC FREEPROCCACHE
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Also if the table is very small (like one extent data page) there's no difference between them since all records will be loaded into memory.
0
 
enrique_aeoAuthor Commented:
set statistics io on
DBCC DROPCLEANBUFFERS
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       print 'Exists = True'

DBCC DROPCLEANBUFFERS
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
            where ProductID = 970
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
0
 
enrique_aeoAuthor Commented:
it's OK

 set statistics io on
DBCC DROPCLEANBUFFERS
GO
       if exists
       (
            select 1
            from sales.SalesOrderDetail
       )
       print 'Exists = True'

DBCC DROPCLEANBUFFERS
GO
       declare @rowcount int
       set @rowcount =
       (
            select count(*)
            from sales.SalesOrderDetail
       )
       if @rowcount > 0
       print 'Exists = True'
 set statistics io off

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'SalesOrderDetail'. Scan count 1, logical reads 228, physical reads 1, read-ahead reads 226, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Exists = True
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.