|
|
Posted by Erland Sommarskog on 09/17/07 21:12
Danny Burton (no@email) writes:
> 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.
Unless this is a temp table created within the procedure, I would
question the wise in this. A question that I often have reason to
ask about our database is "is this column actually used for something".
It is not uncommon that I find a couple of procedures that return this
column, but I also see that they return of columns that are of no
interest in that context, so it looks like "let's select all columns
while we're at it, in case we need them later".
A stored procedure should in my opinion only return the column that
the caller actually requests.
> 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 * ?
No.
> 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.
As I said, I question the wise in this. And what if the colunms in the
tables are rearranged, renamed or a column is dropped? With SELECT *,
the procedure will continue to run, but the client may be utterly
confused. With listed columns, you would get an error if columns are
renamed or dropped. And if they are merely rearranged, the client
will not even notice.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|