optimization - SQL: How to properly check if a record exists

Question

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 1

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.