Dec 27, 2014

Check the existence of records efficient | Fastest way Sql server

Optimized and fastest way to check the existence of records in a table

Some developers use COUNT aggregate function to check the existence of records in table while SQL server has introduced any very efficient operator EXISTS to check the existing of records in table. For example:

Bad approach:

IF (
              SELECT COUNT(*)
              FROM tblUser
              ) > 1
BEGIN
       SELECT 1
END


Total logic read: 367

Efficient and best approach:

IF EXISTS (
              SELECT *
              FROM tblUser
              )
BEGIN
       SELECT 1
END


Total logic read: 3

Comparison of cost of both approaches using execution plan:

Existence check sql server


The EXISTS operator follow special logic i.e. it stops the checking when it gets any records in a table while aggregate function COUNT scan whole the table.

No comments:

Post a Comment