Subquery using Exists 1 or Exists *

Question

I used to write my EXISTS checks like this:

IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters) BEGIN    UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters END 

One of the DBA's in a previous life told me that when I do an EXISTS clause, use SELECT 1 instead of SELECT *

IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters) BEGIN    UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters END 

Does this really make a difference?

Answer 1

No, SQL Server is smart and knows it is being used for an EXISTS, and returns NO DATA to the system.

Quoth Microsoft: http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

To check yourself, try running the following:

SELECT whatever   FROM yourtable  WHERE EXISTS( SELECT 1/0                  FROM someothertable                  WHERE a_valid_clause ) 

If it was actually doing something with the SELECT list, it would throw a div by zero error. It doesn't.

EDIT: Note, the SQL Standard actually talks about this.

ANSI SQL 1992 Standard, pg 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

3) Case:
a) If the <select list> "*" is simply contained in a <subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>.