SQL: How to properly check if a record exists

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?