SQL Server – Never use Count(*) in If Exists() for data existence in a Table. Why?

Why? Simple because it doesn’t work.

In this post I will explain you the same. First let me just create a sample table and insert some data into it.

--
 
CREATE TABLE testCounts
(
    ID INT
)
GO

--

Now we have not inserted any rows means table is empty. now let me just run the code for you.

--


--Bad Method
If EXISTS (SELECT COUNT(*) FROM dbo.testCounts)
	BEGIN
		PRINT 1
	END
ELSE 
	BEGIN
		PRINT 0
	END

--

Now if you execute the above code you will always get 1, the number of rows doesn’t matter. Even we have 0 rows it is giving us 1. Folks always use below approach to check data existence in a table.(For details please refer – https://msbiskills.com/2015/09/16/sql-server-best-way-to-check-if-data-exists-in-a-table/)

--

If EXISTS (SELECT TOP 1 1 FROM dbo.testCounts)
BEGIN
    PRINT 1
END

--

Above query will give excellent execution plan. I hope now things are more clear. That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com