|
|
Posted by Erland Sommarskog on 07/06/06 21:59
Jim Devenish (internet.shopping@foobox.com) writes:
> Thank you for your warning about the use of Select * . I expect that
> there has been extensive discussion elsewhere as to why 'it does not
> belong in production code' but I was unaware of it. Perhaps you can
> point me in the right direction.
There aree several reasons. One is tracability. Is the column xyz in use
somewhere? It's possible to find via sysdepends it is (although sysdepends
for various reasons isn't always reliable), but then you find that it
is a SELECT *, you cannot tell whether it is use at all. That is,
queries should list columns that are actually used. In any serious system
there are columns that are one point phased out - or could be phased out,
if you could verify that they are no longer in use.
If you add or drop columns, the SELECT * changes, but depending on context
not immediately, so there can be sources of confusion.
> However you say that I would have still got an error had I listed the
> columns explicitly. So I return to my orginal question: why does the
> new view produce the expected output from the Design View but not from
> 'Return all rows'?
I use neither of the tools, but I guess that Design View resubmits the
view definition something Return all Rows have no reason to do. Using
Profiler would reveal what is going on.
> How and where do I use sp_refreshview?
In Query Analyzer where you run other queries. As for how, well, did
you try Books Online?
--
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]
|