SQL: How to properly check if a record exists

sql optimization query-optimization
systempuntoout picture systempuntoout · Nov 23, 2010 · Viewed 424.6k times · Source

While reading some SQL Tuning-related documentation, I found this:

SELECT COUNT(*) :

  • Counts the number of rows.
  • Often is improperly used to verify the existence of a record.

Is SELECT COUNT(*) really that bad?

What's the proper way to verify the existence of a record?

Answer

Martin Schapendonk picture Martin Schapendonk · Nov 23, 2010

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Related questions

How to find out why the status of a spid is suspended? What resources the spid is waiting for?

I run EXEC sp_who2 78 and I get the following results: How can I find why its status is suspended? This process is a heavy INSERT based on an expensive query. A big SELECT that gets data from several tables …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the …

Read More