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

SQL Server | Best way to check if data exists in a table ?

There are some situations where you need to check if data exists in a specific table and based on the output of that check, you will perform some action. People do often write code which will force the optimizer to go with the sub optimal query plan and in the end we will face performance issues. In this post I will explain you a simple and extremely cool solution to this kind of problem. First let me just create a sample table and insert some data into it.

--

CREATE TABLE testCounts
(
	ID INT
)
GO

INSERT INTO testCounts VALUES ( 100 )
GO 10000

CREATE CLUSTERED INDEX Ix_testCounts ON testCounts(Id)
GO

--

Using above statement I have just created a table called testCounts and inserted 10000 rows into it. Now Let me just write some statement that I have seen over the years to check for existence of data inside the table.

--Method 1
DECLARE @Counter AS BIGINT = 0
SET @Counter = (SELECT COUNT(*) FROM dbo.testCounts)
If @Counter > 0
BEGIN
	PRINT 1
END 


--Method 2
If EXISTS (SELECT 1 FROM dbo.testCounts)
BEGIN
	PRINT 1
END


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

--

Now all the above methods will give the same logical result we were looking for. Check the screen shot below

Pawan Khowal – Check Data Existence

Now comes the important question – Which one of these is the fastest. For that we will have fetch the actual execution plan.

Pawan Khowal – Check Data Existence ( Execution Plans )

By looking at the execution plans for all the methods we can clearly say that the Method 2 & 3 are quite good methods we have for checking data existence in a table.

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