Posted by Steve Jorgensen on 10/20/05 08:32

On Thu, 13 Oct 2005 22:30:33 -0400, "serge" <sergea@nospam.ehmail.com> wrote:

>Using "SELECT * " is a bad practice even
>when using a VIEW instead of a table?

....

I've read through this thread and compared it to the practices I've evolved
over time, and I think I have a somewhat different take on it.

1. I find that, in my applications, most tables don't have a very large number
of columns, and most of the columns in a table are likely to be useful in
cases where any of them are.

2. Most stored procedures and views end up getting used from multiple places
in the client code, each with similar, but not identical needs. Most of thems
need most columns, but not exactly the same ones.

3. I add fields to tables relatively often and freely, but remove them
sparingly and with consideration.

4. I (mostly) have unit tests in the client code that will fail if dependent
columns go missing from server query results. Where unit tests are not
present, I sill try to write code with little waste, so most all the code gets
executed in most cases, so breakages will be found early.

5. I almost never write client-side code that will care if extra, unexpected
fields are present in a result.

Under these circumstances, ...

1. I usually find it a good practice to go ahead and select <table>.* for the
most significant table in a select, and select specific fields from other
tables as needed.

2. I don't find that transferring an average of a few extra fields is a
performance burden because the effects of that are completely swamped by other
unavoidable bottlenecks that occur in real-world applications. If there is
one query that is called very often and returns a large number of rows, then
it makes sense to optimize the columns returned for -that- case, not for all
cases just on GP.

3. Limiting the columns to just what's needed in a specific case leads to a
proliferation of views and procedures, mostly the same, but differing in a few
details. That's more server-side objects to maintain when the schema does
change, more obsolete objects being left laying around, and more objects to
paw through to find the one you're looking for.

4. If the issue does exist that a large number of unused fields is present in
a high fraction of queries, that's a strong indication that the schema design
needs some work, not that column output of each query should be better
constrained.

[Back to original message]