Advanced SQL tutorial pdf, Check if data exists, Check if record exists in table for tables - MSDN - Microsoft, check if table has records, Define below transformation in DFD?, Difference between Cached Report and Snapshot Report, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to check if a specific record exists in a table in SQL, How to make connection with a FTP server?, How to show "No Data Found" Message to end user?, SQL, sql - Fastest way to determine if record exists, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, sql server - check if data exist in a table if yes do this else, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Server | Best way to check if data exists in a table ?, SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table), SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions
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
Now comes the important question – Which one of these is the fastest. For that we will have fetch the actual execution plan.
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.
Pawan Kumar Khowal
Pingback: SQL Server – Never use Count(*) in If Exists() for data existence in a Table. Why? | Enhance your SQL Server & MSBI Stack Skills