"SELECT TOP 1 1" VS "IF EXISTS(SELECT 1"

Question

I have some .NET code that checks for the existence of a SQL record at a moderately-high interval. I am looking to make this check as "cheap" as possible.

I'm wondering the characteristics of two queries:

IF EXISTS(SELECT 1
          FROM   BigTable
          WHERE  SomeColumn = 200)
  SELECT 1 AS FOUND
ELSE
  SELECT 0 AS FOUND

VS

SELECT TOP 1 1
FROM   BigTable
WHERE  SomeColumn = 200 

They both produce similar execution plans. But the SELECT TOP 1 1 seems to execute faster: Less query to parse and when record is not found, it sends less down the pipe. I'm also assuming it runs faster at the client because I just need to check the Record Count, rather than marshaling the return value of IF EXISTS.

Most of the performance benefits are negligible. But if both consistently return the same result, then why not choose the slightly faster method?

Is "SELECT TOP 1 1" THEE best way to check for an existence of a record in .NET?

(We use .NET 3.5, and I am trying to avoid LINQ because it is not used elsewhere in the application. We also have some legacy VB6 apps that we are migrating/rewriting, so they may need to execute this as well.)

EDIT: Just a little more detail on design. This record is a "header". There is another table that has child records that will be read/parsed when this header is found. The lack of a record is a good thing: there is no work to do.

EDIT2: The lack of a record that meets the condition will occur more often. They come in sporadic waves.

Answer 1

I'd recommend IF EXISTS(SELECT * ...), unless this is actually causing a performance issue. It expresses the intent of the query in a much better understood fashion than alternatives.

I'd avoid COUNT(*) (as in the current answers) unless you actually need the count of rows from the table.

If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:

select 1 where exists(select * from BigTable where SomeColumn=200)

Which produces the same result set as your second query (either 0 or 1 row)

Answer 2

This is what you want instead of the IF statement

  SELECT ISNULL(
     (SELECT TOP 1 1 FROM BigTable where SomeCol = 200), 0);

Answer 3

Makes no difference at all, exists will not even evaluate the select portion of your statement. So, use the one you like.

declare @test table (name varchar(20))

-- comment out inserts for testing.
insert into @test (name) values ('bob the builder')
insert into @test (name) values ('bob the builder')

-- for giggles, put 1/0 here. You'll find that divide by zero error.
select 1 from @test

-- notice that you don't receive a divide by zero error.
-- the bit in the select portion is never executed, ever.
if not exists (select 1/0 from @test) select 'Yay!'
if     exists (select 1/0 from @test) select 'Boo!'

In fact you can use these interchangeably:

... select * ... 
... select 1 ... 
... select top 1 * ... 
... select top 1 1 ... 
... select 'John Jacob Jingleheimer Schmidt' ...