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