|
|
Posted by David Portas on 09/17/07 21:11
"Danny Burton" <no@email> wrote in message
news:Uv-dnaiBfeHjf3PbRVnyugA@giganews.com...
>I know that in order to reduce network traffic you should always select
>only the columns you require but I have a stored procedure which must
>**always** return all columns from a specific table.
>
> When returning all columns from a table is there any performance gain (or
> any other reason) for using SELECT col1, col2 etc... instead of just
> SELECT * ?
>
> I can immediately think of a disadvantage which is that if you include
> named columns in your SELECT then you must update the stored procedure
> each time a column is added to the table, using SELECT * gets around this
> so the stored procedure will never need to be modified.
>
> Would anyone care to share their thoughts on the above?
>
> Thanks,
>
> Clive
>
>
>
>
>
When doing maintenance or making changes it's often useful to do a search
over your code to find exactly where a column is being referenced. If you
use * rather than column names then you will be less likely to find
potential dependencies in your code. That can make debugging a much harder
task.
In some environments it can be difficult to maintain total control over the
logical column order returned by *. For example it's easy to re-create a
table in development (maybe even by accident) but it's probably totally
unacceptable to do that in production. So if you use SELECT * you may get
columns returned in some unexpected order, which could be a problem
depending on how your client process consumes that data. By the way, I don't
recommend that data be processed based on fixed column order anyway, but
based on your remark about not wanting to make code changes when you add new
columns I assume that must be what you are doing.
Some code may break or give the wrong results due to column order
dependencies or new or missing columns. For example UNION or CHECKSUM(*) or
INSERT without a column list. Regarding INSERT, how will you populate your
table if you don't know what columns exist in it?.
It is also a foundation of relational database design, that columns are
identified by name and not position. The compromise you are suggesting is
only possible at all because SQL violates that principle.
In summary, experience suggests it is much less trouble to maintain code
with column names than without them.
One other consideration. One day someone else will review or even inherit
your code. Using SELECT * in your code just guarantees an oppotunity for
someone to point the finger and tut-tut about best practices and coding
standards...
--
David Portas
Navigation:
[Reply to this message]
|