[thelist] Select *

Scott Dexter sgd at ti3.com
Thu Oct 12 12:02:25 CDT 2000


> -----Original Message-----
> From: Ashish [mailto:india_design at yahoo.com]

> Hi,
>    I remember some time back there was some mention about not 
> using 'select
> *' in a statement but using the exact columns. Went through 
> the archives,
> but am not able to find the exact reference. I did find Scott telling
> someone not to use it but nothing more. I did some research 

Well isn't that enough? ;)

> and found this :
> http://www.adopenstatic.com/faq/selectstarisbad.asp
> However, are there any other reasons besdides the one given 
> in the above
> article ? I need to enforce this and have to do some hard 

That's a great article that covers 99.9% why you don't do Select *. Those
reasons are hard enough (#1, and #2 can be HUGE impacts). If your team (or
boss) scoffs at those, I'd get a new team (boss) ....

One I'd like to add:

if you change the db schema (add columns to a table anyone? I've *never*
done that ;) ) you don't blow up your code. --Basically it allows you to
code independent of changes to the db (for the most part)

a corollary: 
<tip type="SQL stored procedures">
Explicitly set stored procedure parameters, like so:

Exec procDoSomething @var1='foo', @var2=1234

Why?
1) You won't be restricted to listing the parameters in the order the stored
proc is expecting them
2) If the stored proc has default values (¿what? get them in there!) you can
just skip the parameter entirely, instead of typing "DEFAULT" in your
statement
3) if the stored procedure changes, it don't blow up your code =)
4) the person you pass off the code to likes you because they don't hafta
look up the stored proc to see what vars you're populating
</tip>

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/





More information about the thelist mailing list