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