Re: Not passing back values on an if statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 02/21/05 

Date: Mon, 21 Feb 2005 16:21:09 -0500

Hugo Kornelis wrote:
> On Mon, 21 Feb 2005 09:37:16 -0800, tshad wrote:
>
> (snip)
>> BTW, why would "select *" give you a better response than "select
>> Application"?
>
> Hi Tom,
>
> With SELECT *, SQL Server only has to check for the existance of a
> row. This can be done with any index, without the need to access the
> real data.
>
> With SELECT Application, SQL Server has to check not only that a row
> exists, but also that the Application in that row is not NULL. If the
> column Application doesn't allow NULLs, performance will be the same.
> But if this column does allow NULLs, SQL Server will have to fetch
> the data to check for NULL. This might require extra access to the
> data, or restrict the choice if indices to use for the query.
>
> Best, Hugo

OK, the last time I suggested there may be adifference between using "SELECT
* ..." and "SELECT column ..." in an EXISTS subquery, I was called on it by
several people. So I will pose to you the same question that was posed to
me: Can you provide a repro script that illustrates a difference in
execution plan or performance when using * as opposed to a column name in an
EXISTS subquery? :-)

Bob Barrows 

-- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Relevant Pages

  • Re: SQL Humor
    ... Hugo Kornelis wrote: ... SQL Server will often produce the same execution for both versions. ... the big questions for the RM, *for me* seem to include view updatability which has to do with the operators of the algebra as well as whether a relational engine can implement customary features such as concurrency control and presentation coherence without being written in a language that eschews the relational operators. ...
    (comp.databases.theory)
  • Re: Need help with hint syntax in SQL Server 2000
    ... unable to recognize that it shoul push the where clause condition down ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... Thank you both for the replies. ...
    (comp.databases.ms-sqlserver)
  • Re: NOLOCK hint
    ... I have identity as clustered index on almost all tables. ... values has been tampered with (using DBCC CHECKIDENT with the RESEED ... SQL Server has to split the page. ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with missing data in query
    ... I did go to that site But I couldn't figure out how to upload anything. ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Multiplying numeric(19,4) Values
    ... in the context of my original query: ... I don't think I suggested that in the context of this query. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.programming)