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
How can I further optimize a derived table query which performs better than the JOINed equivalent?

UPDATE: I found a solution. See my Answer below. My Question How can I optimize this query to minimize my downtime? I need to update over 50 schemas with the number of tickets ranging from 100,000 to 2 million. Is it advisable to …

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