using "top 1" or "exists" or other method performance ideas

I have code that needs to know if any rows exist before deciding to do something. I'll make a stored procedure for the code.

My select statement would be
select * from table1 where col1=value

I only want to know if any rows exist that match the criteria following the where clause.

"select top 1 ..." would be an improvement, I think.
Would "exists" syntax be even better? What would the "exists" statement look like?

Thanks for any ideas.
LVL 4
GordonPrinceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BrandonGalderisiConnect With a Mentor Commented:
if exists (select null from Table1 where col1=value)
begin
print 'do something'
end

No need to do a top since the condition will be met if ONE record exists.  It won't return all records.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
exists will be better

select * from table1 where exists (select 1 from ... where  <> )
0
 
chapmandewCommented:
exists would be better in this case because using a top(1) indicates that an ORDER By may be occurring behind the scenes.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
chapmandewCommented:
wow.
0
 
Brendt HessSenior DBACommented:
The above answers are all correct - the EXISTS syntax is almost always more efficient than any other method of checking.  I do note, however, different versions of the exists check using NULL and 1.  These are based on a condition that existed in SQL Server no later that version 7, and (I think) not since 6.x - that specifying a constant value in the EXISTS chech is faster than specifying some value from the actual data record.  This is not true, so the version:

IF EXISTS (
    SELECT *
    FROM Table1
    WHERE Col1 = @Value
    )

is just as fast as the versions with a 1 or NULL in the select list, and looks more natural to the potentially inexperienced coder in the future who may need to maintain the code.  I recommend using it.  
0
 
BrandonGalderisiCommented:
I don't * or 1 for any reason other than I want to make it clear to "inexperienced coders" that it is not actually selecting any data.

My opinion is that an inexperienced coder probably shouldn't be attempting to decypher anything I have written regardless :).
0
 
GordonPrinceAuthor Commented:
I think all coders owe it to the other coders to make their code as easy to understand as possible, assuming no performance problems. So I think I'm with BHESS1 above on that.

Also, if "select *" is version independent, that's a plus.

Whenever there's a choice, write the code so you or someone else will have the maximum chance of quickly understanding what's going on and how the code could be fixed or modified or enhanced. In my opinion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.