"SELECT TOP 1 1" VS "IF EXISTS(SELECT 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)

This is what you want instead of the IF statement

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

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' ...