sql server programming: Select Null or Select * ? -- sql server programming

RE: Select Null or Select * ?

Nigel Rivett
3/3/2005 12:13:06 PM

sql server programming: Look at the query plan - you should find they are the same.

if exists (select * from ...)
looks better though.

As long as you don't use
if exists (select col from ...)
then it doesn't really matter.

select * shows you are just checking for rows, if you use select 1 or select
null then people looking at it will probably waste time wondering if there is
a reason for it.

[quoted text, click to view]

RE: Select Null or Select * ?

Alejandro Mesa
3/3/2005 12:21:03 PM

This example shows the same execution plan for both.

use northwind
go

set statistics profile on
go

declare @i int

set @i = 10250

dbcc freeproccache
if exists(select * from dbo.orders where orderid = @i)
print '1'

dbcc freeproccache
if exists(select null from dbo.orders where orderid = @i)
print '1'
go

set statistics profile off
go

AMB

[quoted text, click to view]

Re: Select Null or Select * ?

Alejandro Mesa
3/3/2005 1:03:02 PM

Not when the statement is used with EXISTS.

use northwind
go

set statistics profile on
go

dbcc freeproccache
if exists (Select * from categories where categoryname='Produce')
print 1

dbcc freeproccache
if exists (Select Null from categories where categoryname='Produce')
print 1
go

set statistics profile off
go

AMB

[quoted text, click to view]

Re: Select Null or Select * ?

Aaron [SQL Server MVP]
3/3/2005 3:11:35 PM

I don't think either is faster, because the engine is smart enough to know
that it's not actually going to return those columns, only a single value
stating whether such a row exists.

I typically use IF EXISTS (SELECT 1 FROM ...) not for performance reasons,
just a habit to make it easy to identify subqueries on casual inspection
that are definitely not going to return results.

--
http://www.aspfaq.com/
(Reverse address to reply.)

"Jean-Nicolas BERGER" <j-n.enlevezmoi.berger@club-internet.fr> wrote in
message news:OPluBxCIFHA.2976@TK2MSFTNGP15.phx.gbl...
[quoted text, click to view]

Re: Select Null or Select * ?

Aaron [SQL Server MVP]
3/3/2005 4:00:15 PM

[quoted text, click to view]

Uh, SELECT is not the same as IF EXISTS (SELECT ... ).

A

Select Null or Select * ?

Jean-Nicolas BERGER
3/3/2005 9:03:28 PM

I'm wondering what's the fastest solution (if one is faster than the orther)
between :

if exists (select * from MyTable Where ...)

and

if exists (select Null from MyTable Where ...)

Thx a lot for your answers.
JN.

Re: Select Null or Select * ?

Jean-Nicolas BERGER
3/3/2005 9:49:09 PM

In fact, select * seems to be faster in some cases...

use northwind
set statistics profile on
go

dbcc freeproccache
Select * from categories where categoryname='Produce'

dbcc freeproccache
Select Null from categories where categoryname='Produce'

JN.

"Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> a écrit dans le
message de news: CD876B6C-A50C-4B50-B319-A1AEF91E47FF@microsoft.com...
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.