Problem when changing Views

[Jim Devenish]

I have 3 views, two of which depend on the other:

CREATE VIEW dbo.CustomerLis tQueryAccounts
AS
SELECT dbo.CustomerLis tQuery.*
FROM dbo.CustomerLis tQuery
WHERE (isProspect = 0)

CREATE VIEW dbo.CustomerLis tQueryProspects
AS
SELECT dbo.CustomerLis tQuery.*
FROM dbo.CustomerLis tQuery
WHERE (isProspect = 1)

which depend on:
CREATE VIEW dbo.CustomerLis tQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.* , CAST(dbo.Custom ers.CustomerID AS int) AS
CustomerIDAsNum ber,

dbo.NumberOfJob sPerCustomer.Co untOfJobID,
dbo.NumberOfQuo tesPerCustomer. CountOfQuoteID,

dbo.NumberOfCom plaintsPerCusto mer.CountOfComp laintID,
dbo.NumberOfNot esPerCustomer.C ountOfCustomerN oteID,

dbo.NumberOfEnq uiriesPerCustom er.CountOfEnqui ryID
FROM dbo.Customers

LEFT OUTER JOIN
dbo.NumberOfJob sPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfJob sPerCustomer.Cu stomerID
LEFT OUTER JOIN
dbo.NumberOfQuo tesPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfQuo tesPerCustomer. CustomerID
LEFT OUTER JOIN
dbo.NumberOfNot esPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfNot esPerCustomer.C ustomerID
LEFT OUTER JOIN
dbo.NumberOfCom plaintsPerCusto mer ON
dbo.Customers.C ustomerID = dbo.NumberOfCom plaintsPerCusto mer.CustomerID

LEFT OUTER JOIN
dbo.NumberOfEnq uiriesPerCustom er ON
dbo.Customers.C ustomerID = dbo.NumberOfEnq uiriesPerCustom er.CustomerID

ORDER BY dbo.Customers.A ccountName

These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerLis tQueryShorter
AS
SELECT dbo.Customers.* , CAST(dbo.Custom ers.CustomerID AS int) AS
CustomerIDAsNum ber
FROM dbo.Customers

I now wish to make this one CustomerListQue ry and so rename the
existing one CustomerListQue ryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerLis tQueryOriginal

I then rename CustomerListQue ryShorter to CustomerListQue ry and change
its first line to
CREATE VIEW dbo.CustomerLis tQuery

Now when I 'Return all rows' of CustomerListQue ryAccounts I get an
error message:
'dbo.CustomerLi stQueryAccounts ' 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 CustomerListQue ry. How do I get
the 'Return all rows' output to show the same.

It appears that the orginal version is still being used. How do I get
the system to replace this ?

[Erland Sommarskog]

Jim Devenish (in************ ***@foobox.com) writes:

I have 3 views, two of which depend on the other:
CREATE VIEW dbo.CustomerLis tQuery
AS
SELECT TOP 100 PERCENT
...
ORDER BY dbo.Customers.A ccountName

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 CustomerListQue ryShorter to CustomerListQue ry and change
its first line to
CREATE VIEW dbo.CustomerLis tQuery

Now when I 'Return all rows' of CustomerListQue ryAccounts I get an
error message:
'dbo.CustomerLi stQueryAccounts ' 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 CustomerListQue ry. 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jim Devenish]

Erland Sommarskog wrote:

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.

Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQue ry had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.

Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQue ry Order By AccountName

I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.

Similarly when I look up the syntax of Create View I find, within the
select_statemen t that:
"A Create View statement cannot include Order By clause, unless there
is also a TOP clause in the select list of the Select statement"

It does not say that this is nonsense.

[Jim Devenish]

Erland Sommarskog wrote:

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.

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.

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'?

How and where do I use sp_refreshview?

[Stu]

Hey Jim,

There are a number of issues with your approach, but the primary
reason that you're having the problems you are is that you're using
Enterprise Manager (known by many as Enterprise MANGLER) to edit the
script of your views. EM is a good tool for administration, but you
should be using Query Analzer for editing.

In Query Analyzer, you can right-click on the view and select EDIT, and
it will show you the script of the view, which you can then ALTER to
get to the correct format you need.

As far as the other issues, I think Erland tapped into them:

1. Don't use SELECT * in production code (or at least don't do so
wihout commenting). There was a recent discussion in another group
about it that you may want to read.
http://groups.google.com/group/micro...efe874da82b7ef
2. The TOP...ORDER BY embedded in a view is, at best, flaky. It's
also one of the behaviors that was changed in SQL 2005, so if you can
avoid using it, I'd recommend that you do so. The only way to ensure
an order to your results is to use ORDER BY in your final outer SELECT
statement (e.g., SELECT columnlist FROM dbo.CustomerLis tQueryAccounts
ORDER BY AccountName).

HTH,
Stu

Jim Devenish wrote:

I have 3 views, two of which depend on the other:

CREATE VIEW dbo.CustomerLis tQueryAccounts
AS
SELECT dbo.CustomerLis tQuery.*
FROM dbo.CustomerLis tQuery
WHERE (isProspect = 0)

CREATE VIEW dbo.CustomerLis tQueryProspects
AS
SELECT dbo.CustomerLis tQuery.*
FROM dbo.CustomerLis tQuery
WHERE (isProspect = 1)

which depend on:
CREATE VIEW dbo.CustomerLis tQuery
AS
SELECT TOP 100 PERCENT
dbo.Customers.* , CAST(dbo.Custom ers.CustomerID AS int) AS
CustomerIDAsNum ber,

dbo.NumberOfJob sPerCustomer.Co untOfJobID,
dbo.NumberOfQuo tesPerCustomer. CountOfQuoteID,

dbo.NumberOfCom plaintsPerCusto mer.CountOfComp laintID,
dbo.NumberOfNot esPerCustomer.C ountOfCustomerN oteID,

dbo.NumberOfEnq uiriesPerCustom er.CountOfEnqui ryID
FROM dbo.Customers

LEFT OUTER JOIN
dbo.NumberOfJob sPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfJob sPerCustomer.Cu stomerID
LEFT OUTER JOIN
dbo.NumberOfQuo tesPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfQuo tesPerCustomer. CustomerID
LEFT OUTER JOIN
dbo.NumberOfNot esPerCustomer ON
dbo.Customers.C ustomerID = dbo.NumberOfNot esPerCustomer.C ustomerID
LEFT OUTER JOIN
dbo.NumberOfCom plaintsPerCusto mer ON
dbo.Customers.C ustomerID = dbo.NumberOfCom plaintsPerCusto mer.CustomerID

LEFT OUTER JOIN
dbo.NumberOfEnq uiriesPerCustom er ON
dbo.Customers.C ustomerID = dbo.NumberOfEnq uiriesPerCustom er.CustomerID

ORDER BY dbo.Customers.A ccountName

These work well but I have an alternative version of this latter one
which has fewer columns. At present it is:
CREATE VIEW dbo.CustomerLis tQueryShorter
AS
SELECT dbo.Customers.* , CAST(dbo.Custom ers.CustomerID AS int) AS
CustomerIDAsNum ber
FROM dbo.Customers

I now wish to make this one CustomerListQue ry and so rename the
existing one CustomerListQue ryOriginal (just to get is out of the way)
and change its first line, using the View's properties (in Enterprise
Manager) to :
CREATE VIEW dbo.CustomerLis tQueryOriginal

I then rename CustomerListQue ryShorter to CustomerListQue ry and change
its first line to
CREATE VIEW dbo.CustomerLis tQuery

Now when I 'Return all rows' of CustomerListQue ryAccounts I get an
error message:
'dbo.CustomerLi stQueryAccounts ' 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 CustomerListQue ry. How do I get
the 'Return all rows' output to show the same.

It appears that the orginal version is still being used. How do I get
the system to replace this ?

[Erland Sommarskog]

Jim Devenish (in************ ***@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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Jim Devenish (in************ ***@foobox.com) writes:

Thank you for your helpful advice. I had not appreciated that using
Order By within a View was nonsense. I am in the process of converting
the back-end of my database from Access to SQLServer and so am new to
the latter. CustomerListQue ry had been an Access query used as the
RecordSource of a Form but in order to speed things up I made into a
View.

Are now suggesting that I remove Order By from the View and then make
the RecordSource into:
Select * From CustomerListQue ry Order By AccountName

Yes.

I have just looked at BOL for 'Order By' and find that it says:
"The Order By clause is invalid in Views ... unless TOP is also
specified"
This implies that it can be used in this way but it does not say that
it fails to carry out the ordering.

TOP 10 PERCENT ORDER BY is meaningful, because it picks the the top 10
percent according to the ORDER BY clause.

TOP 100 PERCENT ORDER BY is meaningless, because 100 percent is
everything, so it does not matter what you order by. You still get
everything.

That is, the purpose of ORDER BY in views in combination of TOP is to
determine which rows that are selected by the view.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx