|
|
Posted by Erland Sommarskog on 07/05/06 22:03
Jim Devenish (internet.shopping@foobox.com) writes:
> I have 3 views, two of which depend on the other:
> CREATE VIEW dbo.CustomerListQuery
> AS
> SELECT TOP 100 PERCENT
>...
> ORDER BY dbo.Customers.AccountName
Remove this TOP 100 PERCENT and ORDER BY nonsense. It serves no
purpose. Yes, you may feel that when you do a SELECT on the view
that you get back the rows in the same order as the ORDER BY clause,
but that is due to mere chance. Many people who had this sort of
views found that they no longer the result they expected when they
moved to SQL 2005.
There is only one way to get an ordered result from a query, and that
is to add ORDER BY to the query itself.
> I then rename CustomerListQueryShorter to CustomerListQuery and change
> its first line to
> CREATE VIEW dbo.CustomerListQuery
>
> Now when I 'Return all rows' of CustomerListQueryAccounts I get an
> error message:
> 'dbo.CustomerListQueryAccounts' has more column names specified than
> columns defined.
>
> If however, I go into the design and then select Run, I get the correct
> output reflecting the new version of CustomerListQuery. How do I get
> the 'Return all rows' output to show the same.
You should stop using SELECT *. SELECT * is great for ad-hoc queries,
but it does not belong in production code. One reason for that is what
you experienced. Had you listed the columns explicitly, you would still
have gotten an error, but at least the error would have been apparent.
If you insist on using SELECT *, you need to learn to use sp_refreshview,
because you will need it a lot.
--
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]
|