Fastest way to determine if record exists

The magic of EXISTS

If you suspect a record's existence, gently knock on the database door with an EXISTS:

IF EXISTS (SELECT FROM table WHERE condition)
    -- If Sherlock Homes were a query, he'd be an EXISTS


When "SELECT 1" suffices

When using EXISTS, the SELECT literal doesn't matter. Think it as: "Is mail in the box?" instead of "How much mail in there?"

Beware of "SELECT *"

Be cautious with the "SELECT * FROM..." beast. It's a vortex that consumes memory!

Unwanted attention

Don't attract unwanted rifle scope from optimizer - Select only what you need. You can use "NOLOCK hint" for a quick look without interrupting transactions, but beware of "ghost reads".

Use NOLOCK like a ninja

When you're bold enough for a quick glance, slide in with NOLOCK:

IF EXISTS (SELECT 1 FROM my_table WITH (NOLOCK) WHERE condition)
    -- Record exists, "went in - saw - left"


Make your operations nearly invisible but beware - others might not see you too.

Checking before doing

Be a "read before you leap" person. Use "IF EXISTS" before any data modification:

IF EXISTS (SELECT 1 FROM my_table WHERE condition)
    UPDATE my_table SET ...
    -- Because no one likes a pointless update, right?


This way you won't trigger unwanted write locks.