Why use "Select 1 from......."

[billybobk]

Hi all,
I started working at a new company and have discovered the pervasive usage of "Select 1 from.." in their stored procedures. Why? It just returns 1. Here's one use:
==============================================
if exists (select 1 from dbo.sysobjects a join sysindexes b ON a.id = b.id
where a.name = 'DIM_CUSTOMER' AND b.name = 'IX_DIM_CUSTOMER_TYPE')
drop index dbo.DIM_CUSTOMER.IX_DIM_CUSTOMER_TYPE
================================================
If the objects exist, then Select 1 will never be untrue or true (it's not boolean) because select 1 returns a 1. I don't get it.
Thanks
--Bill

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[swampBoogie]

If the query

select 1 from dbo.sysobjects a join sysindexes b ON a.id = b.id
                     where a.name = 'DIM_CUSTOMER' AND b.name = 'IX_DIM_CUSTOMER_TYPE'

returns any data the exists predicate will be true. Using 1 does not really matter, it is whether any records at all exists that is important. You could use anything in the column list.

 

[billybobk]

Thanks but one problem: It ALWAYS returns data. It ALWAYS returns 1. Therefore it is always "true". So, what's the point?

--Bill
One may not reach the dawn save by the path of the night
--Kahlil Gibran

 

[john76]

It ALWAYS returns data.

This is almost a true statement, here's the breakdown.

When you join sysobjects and sysindexes as listed, it will return a 1 for each row that matches the specified condition (where a.name....)

The IF EXISTS statement basically doesn't care about the data that is returned by the query, it just cares about whether or not there is any data returned. If there is (ie: there is at least one row of something) then it evaluates true, if no rows are returned, then false.

I suppose that having the select list return only a 1 instead of * or a field might speed the processing up a tiny amount, but I really doubt it.

HTH,
John

 

[TJRTech]

I can only assume that many think that:

IF EXISTS (SELECT 1 FROM ...)

is more efficient then:

IF EXISTS (SELECT * FROM ...)

because they believe that the SELECT 1 case only returns 1 IF there is data while SELECT * returns the whole result set.

Of course, I think the optimizer recognizes the IF EXISTS and makes each perform the exact same amount of IO so that each costs the same.

TR