Oracle DBA to learn MS SQL: ORDER BY in VIEW not working

I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.

Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,
tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
(tblItem.Type) = 'Route'))
ORDER BY tblItem.NumbYou shouldn't use SELECT TOP 100 PERCENT. Remove that from the view - as
well as the ORDER BY. Then, select from the view and specify the ORDER BY.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Beowulf" <beowulf_is_not_here@.hotmail.com> wrote in message
news:mssBf.23262$Ez3.18813@.trnddc03...
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.

Ordering was only a side effect of the statement in 2000 and 7.0; the ORDER
BY goes with the TOP and the output is order can only be gaurenteed by using
ORDER BY on the view itself as below...

select * from vwRouteReference order by ...

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Beowulf" <beowulf_is_not_here@.hotmail.com> wrote in message
news:mssBf.23262$Ez3.18813@.trnddc03...
>I have the view below and if I use vwRouteReference as the rowsource
> for a combo box in an MS Access form or run "SELECT * FROM
> vwRouteReference" in SQL Query Analyzer, the rows don't come through
> sorted by Numb.
> Everything I've read on the web suggests that including the TOP directive
> should enable ORDERY BY in views. Does someone have an idea why the
> sorting is not working correctly for this particular view? thanks.
> CREATE VIEW vwRouteReference
> AS
> SELECT TOP 100 PERCENT tblItem.ID,
> tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName, tblItem.Numb,
> tblQuestion.DescrPrimary AS Type
> FROM tblItem INNER JOIN tblQuestion
> ON (tblItem.ID = tblQuestion.Item_ID)
> WHERE (((tblItem.Category_ID)>0))
> UNION
> SELECT TOP 100 PERCENT tblItem.ID,
> tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,
> tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
> FROM tblItem
> WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
> (tblItem.Type) = 'Route'))
> ORDER BY tblItem.Numb|||Beowulf wrote:
> I have the view below and if I use vwRouteReference as the rowsource
> for a combo box in an MS Access form or run "SELECT * FROM
> vwRouteReference" in SQL Query Analyzer, the rows don't come through
> sorted by Numb.
> Everything I've read on the web suggests that including the TOP
> directive should enable ORDERY BY in views. Does someone have an idea
> why the sorting is not working correctly for this particular view? thanks.

You are reading the wrong stuff! :-) The order is determined here:

SELECT * FROM vwRouteReference

... and you didn't specify any order, so you get what you asked for -
the ordering is arbitrary. What you put in the view doesn't necessarily
determine the order that is returned by your SELECT statement. Since
the ORDER BY in your view doesn't fix the order to be returned, SQL
Server's optimizer is perfectly within its rights to ignore it.

The solution is:

SELECT *
FROM vwRouteReference
ORDER BY numb ;

Don't use "SELECT *" in real code. It's sloppy, inefficient and hurts
when it comes to reliability and maintenance.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote:
> Beowulf wrote:
>> I have the view below and if I use vwRouteReference as the rowsource
>> for a combo box in an MS Access form or run "SELECT * FROM
>> vwRouteReference" in SQL Query Analyzer, the rows don't come through
>> sorted by Numb.
>>
>> Everything I've read on the web suggests that including the TOP
>> directive should enable ORDERY BY in views. Does someone have an idea
>> why the sorting is not working correctly for this particular view? thanks.
>>
> You are reading the wrong stuff! :-) The order is determined here:
> SELECT * FROM vwRouteReference
> ... and you didn't specify any order, so you get what you asked for -
> the ordering is arbitrary. What you put in the view doesn't necessarily
> determine the order that is returned by your SELECT statement. Since
> the ORDER BY in your view doesn't fix the order to be returned, SQL
> Server's optimizer is perfectly within its rights to ignore it.

So, it's just a coincidence (or perhaps luck) that in other views (see
below) the ORDER BY is respected and the rows returned by the view are
sorted in the specified order?

e.g., this works as a newbie such as myself would expect:
CREATE VIEW qryAnnotated_Item
AS
SELECT TOP 100 PERCENT getdate() AS FormVersion,
tblCategory.Mnemonic, tblCategory.Numb AS Category_Numb,
tblCategory.Descr,
tblItem.ID, tblItem.Numb as Item_Numb,
tblItem.Type, tblItem.Notes,
CASE
WHEN (tblItem.RevisionDate > tblItem.CreationDate)
THEN tblItem.RevisionDate
ELSE tblItem.CreationDate
END AS ChangeDate
FROM tblCategory INNER JOIN tblItem
ON (tblCategory.ID=tblItem.Category_ID)
WHERE ((tblCategory.ID>0)
ORDER BY tblCategory.Numb, tblItem.Numb

> The solution is:
> SELECT *
> FROM vwRouteReference
> ORDER BY numb ;
> Don't use "SELECT *" in real code. It's sloppy, inefficient and hurts
> when it comes to reliability and maintenance.

Yeah, it's just what I was running in the QA window to test out sorting.
Thanks for the feedback.|||Someone was asleep in their RDBMS class! What is the most basic
property of a Table? It has no ordering by definition. To get an
ordering, you have to have a cursor.

T-SQL dialect is simply forgiving about the extra clause. What can
happen, however, is that when indexing or stats change, you will force
a needlessly expensive sort in queires using this VIEW under the
covers.

Also, why did you have SELECT TOP 100 PERCENT instead of a plain old
SELECT?

Next, stop putting those silly prefixes on data element names. They
scream out "Newbie who doesn't know data modeling or SQL!" to the
world. Then learn that names like "type", "class", "category", etc.
are too vague to be data element names. They beg the question "of
what??" Read a summary of ISO-11179 metadata rules for help.|||Beowulf wrote:

> So, it's just a coincidence (or perhaps luck) that in other views (see
> below) the ORDER BY is respected and the rows returned by the view are
> sorted in the specified order?

You could call it luck, yes. SQL Server 2000 usually seems to respect
the order defined in the view, even though there is no formal guarantee
that it always will. SQL Server 2005 added some engine improvements
that seem to make it less likely that the view order will be preserved
- no problem as long as you observe the documented convention that a
SELECT without ORDER BY is unordered.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote:
> Beowulf wrote:
>> So, it's just a coincidence (or perhaps luck) that in other views (see
>> below) the ORDER BY is respected and the rows returned by the view are
>> sorted in the specified order?
> You could call it luck, yes. SQL Server 2000 usually seems to respect
> the order defined in the view, even though there is no formal guarantee
> that it always will. SQL Server 2005 added some engine improvements
> that seem to make it less likely that the view order will be preserved
> - no problem as long as you observe the documented convention that a
> SELECT without ORDER BY is unordered.

Thanks for the information. It's good to know what the formal standards
are.|||--CELKO-- wrote:
> Someone was asleep in their RDBMS class! What is the most basic
> property of a Table? It has no ordering by definition. To get an
> ordering, you have to have a cursor.

Tables are unordered, but a view's not a table, as far as I understand
it. It is a stored pre-compiled select statement that allows you to
view table data the way you want. Coming from Acess, I expect to be
able to have views be ordered if I include an ORDER BY clause. As it
turns out, I can't expect that from SQL Server. Now I know.

> T-SQL dialect is simply forgiving about the extra clause. What can
> happen, however, is that when indexing or stats change, you will force
> a needlessly expensive sort in queires using this VIEW under the
> covers.

Good to know. Thanks for the information.

> Also, why did you have SELECT TOP 100 PERCENT instead of a plain old
> SELECT?

If you are as knowledgeable about T-SQL as you're representing then you
know exactly why I had to use SELECT TOP 100 PERCENT.

> Next, stop putting those silly prefixes on data element names. They
> scream out "Newbie who doesn't know data modeling or SQL!" to the
> world. Then learn that names like "type", "class", "category", etc.
> are too vague to be data element names. They beg the question "of
> what??"

I don't see any problem at all with disambiguating the elements of my
select statement with the table name qualifier and I'm not going to stop
what I consider a good practice just because you think it is something
newbies do. Do you have a substantive reason for why I shouldn't be
explicit about where I'm selecting my data from?

I'd probably agree with you about the field names, but I'm not going to
completely rewrite this application because a handful of the field names
are bad. It was in production for over 3 years before I even started
working on it and as vague as you think the field names are, they have
to come to have definite meaning for the users of the application. So,
going through and renaming all the fields to "less vague" names would
take up my time, surely introduce bugs, and end up confusing the users.
Sadly, I have to develop this application in the real world, where
sometimes I have to put up with less than ideal code created by someone
else.

> Read a summary of ISO-11179 metadata rules for help.

Thanks for the reference. I'm going to look it up now.|||Beowulf wrote:
> Tables are unordered, but a view's not a table, as far as I understand
> it. It is a stored pre-compiled select statement that allows you to
> view table data the way you want. Coming from Acess, I expect to be
> able to have views be ordered if I include an ORDER BY clause. As it
> turns out, I can't expect that from SQL Server. Now I know.
The result of any relational operation (UNION, EXCEPT, SELECT...) is a
table. Just a derived table as opposed to a base table.
It is the transitive closure that's part of SQL's power.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab|||>Someone was asleep in their RDBMS class! What is the most basic
property of a Table? It has no ordering by definition. To get an
ordering, you have to have a cursor.

Ummm, sort of. A table in and of itself does not have an order.
However, you can definately get the data contained within that table
returned to you in pretty much order you want.|||you guys all missed it.

his first example is two ordered tables that are then unioned. In his
business problem, the final result is not ordered.

In his second example, he is ordering a table.

In order to solve his businss problem, the solution is:

CREATE VIEW vwRouteReference
AS
select * from
(
SELECT tblItem.ID,
tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT tblItem.ID,
tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS
FullName,
tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
(tblItem.Type) = 'Route'))
)
ORDER BY .Numb

This is the brute force way.
Fundamentally, you are unioning two data sets, then ordering them, then
returning them.|||>> Tables are unordered, but a view's not a table, as far as I understand it.<<

Someone here NEVER had a single SQL class. VIEWs are virtual tables.
In fact, the only data structure in SQL is a table. Next yhou will be
calling a coumn, a FIELD! Duh! You did that!!

>> don't see any problem at all with disambiguating the elements of my select statement with the table name qualifier and I'm not going to stop what I consider a good practice just because you think it is something newbies do. <<

In a data model. we design a data dictionary. It defines the meaning
of each datra element (see ISO-11179) .. These names have nothing to
do with the PHYSICAL storage used, the table in which the elemetn
appears, etc.

>> but I'm not going to completely rewrite this application because a handful of the field [sic] names are bad. It was in production for over 3 years before I even started .. <<

I seem to get a call in about 3-5 years into production. The system
was falling apart in year one, but people could patch it. Then they
wanted to port it to larger platform. Theyn try. Then it falls on it
face when people depend on it.

Then I re-write the system at ana insanely large fee.

>> Sadly, I have to develop this application in the real world, where sometimes I have to put up with less than ideal code created by someone else. <<

The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.|||Doug wrote:

> you guys all missed it.
> his first example is two ordered tables that are then unioned. In his
> business problem, the final result is not ordered.
> In his second example, he is ordering a table.
> In order to solve his businss problem, the solution is:
> CREATE VIEW vwRouteReference
> AS
> select * from
> (
> SELECT tblItem.ID,
> tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
> tblItem.Numb, tblQuestion.DescrPrimary AS Type
> FROM tblItem INNER JOIN tblQuestion
> ON (tblItem.ID = tblQuestion.Item_ID)
> WHERE (((tblItem.Category_ID)>0))
> UNION
> SELECT tblItem.ID,
> tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS
> FullName,
> tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
> FROM tblItem
> WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
> (tblItem.Type) = 'Route'))
> )
> ORDER BY .Numb
> This is the brute force way.
> Fundamentally, you are unioning two data sets, then ordering them, then
> returning them.

We didn't miss it but you did. Did you test the code you posted? Even
if you correct it, SQL Server is NOT guaranteed to repect an ORDER BY
in the view unless you use ORDER BY also when you *query* the view.
ORDER BY is therefore redundant in views as far as ordering the data is
concerned. Attempting to use it that way is an unsupported,
undocumented trick and may work only intermittently. Books Online is
quite explicit about this:

http://msdn2.microsoft.com/en-us/library/ms188723.aspx

<quote
ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:

CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName

Then query the view:

SELECT * FROM TopView

Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:

SELECT * FROM TopView
ORDER BY LastName

</quote
--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||> Someone here NEVER had a single SQL class. VIEWs are virtual tables.
> In fact, the only data structure in SQL is a table. Next yhou will be
> calling a coumn, a FIELD! Duh! You did that!!

Yes, but the view is still not a table or are you saying virtual tables are
materialised in some form. The virtualisation is logical.

> of each datra element (see ISO-11179) .. These names have nothing to
> do with the PHYSICAL storage used, the table in which the elemetn
> appears, etc.

Do not try and directly implement the logical model! It doesn't work!!! You
keep missing the implementation phase where you add surrogates and gain
scalability because you have to consider the platform you are implementing
for.

You need to take into consideration the rdbms behaviours too - note, the
difference in default locking behaviour between oracle and sql server.

> I seem to get a call in about 3-5 years into production. The system
> was falling apart in year one, but people could patch it. Then they
> wanted to port it to larger platform. Theyn try. Then it falls on it
> face when people depend on it.

I doubt that very much. I bet is only happened a couple of times and that
was 10 years ago!

> Then I re-write the system at ana insanely large fee.

Yes, bloat code tend to costs more; and bloat code is what you get when you
follow the portability regime.

> The fifth labor of Hercules was to clean the stables of King Augeas in
> a single day. The Augean stables held thousands of animals and were
> over a mile long. This story has a happy ending for three reasons: (1)
> Hercules solved the problem in a clever way (2) Hercules got one tenth
> of the cattle for his work (3) At the end of the story of the Labors of
> Hercules, he got to kill the bastard that gave him this job.

No answer to 'developing in the real world', probably because you've limited
experience at doing that with todays platforms and tools!

It takes more than an install and play to understand real world problems and
the best solutions for them.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138588889.961132.217380@.g43g2000cwa.googlegr oups.com...
>>> Tables are unordered, but a view's not a table, as far as I understand
>>> it.<<
> Someone here NEVER had a single SQL class. VIEWs are virtual tables.
> In fact, the only data structure in SQL is a table. Next yhou will be
> calling a coumn, a FIELD! Duh! You did that!!
>>> don't see any problem at all with disambiguating the elements of my
>>> select statement with the table name qualifier and I'm not going to stop
>>> what I consider a good practice just because you think it is something
>>> newbies do. <<
> In a data model. we design a data dictionary. It defines the meaning
> of each datra element (see ISO-11179) .. These names have nothing to
> do with the PHYSICAL storage used, the table in which the elemetn
> appears, etc.
>>> but I'm not going to completely rewrite this application because a
>>> handful of the field [sic] names are bad. It was in production for over
>>> 3 years before I even started .. <<
> I seem to get a call in about 3-5 years into production. The system
> was falling apart in year one, but people could patch it. Then they
> wanted to port it to larger platform. Theyn try. Then it falls on it
> face when people depend on it.
> Then I re-write the system at ana insanely large fee.
>>> Sadly, I have to develop this application in the real world, where
>>> sometimes I have to put up with less than ideal code created by someone
>>> else. <<
> The fifth labor of Hercules was to clean the stables of King Augeas in
> a single day. The Augean stables held thousands of animals and were
> over a mile long. This story has a happy ending for three reasons: (1)
> Hercules solved the problem in a clever way (2) Hercules got one tenth
> of the cattle for his work (3) At the end of the story of the Labors of
> Hercules, he got to kill the bastard that gave him this job.|||Hi Doug,

Once you use ORDER BY the query results fail to be a set and instead are
linear data which then makes columns fields of data and rows records of
data!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Doug" <drmiller100@.hotmail.com> wrote in message
news:1138583869.933814.248480@.g43g2000cwa.googlegr oups.com...
> >Someone was asleep in their RDBMS class! What is the most basic
> property of a Table? It has no ordering by definition. To get an
> ordering, you have to have a cursor.
> Ummm, sort of. A table in and of itself does not have an order.
> However, you can definately get the data contained within that table
> returned to you in pretty much order you want.|||huh. I never knew that ordering a view was unsupported. It always has
worked, I bet it always will, but it is good to know.

OTOH, if you take the EXACT same code, stuff it into a stored
procedure, it is guaranteed to work.

yet another reason not ot use views.

thanks!.|||celko writes
>In a data model. we design a data dictionary. It defines the meaning
of each datra element (see ISO-11179) .. These names have nothing to
do with the PHYSICAL storage used, the table in which the elemetn
appears, etc.

huh. maybe that is a fundamental difference to how we approach data. I
am looking for solutions to business problems.

you are looking for something entirely different.|||>> . I am looking for solutions to business problems. You are looking for something entirely different. <<

What you seem to be looking for is a quick kludge to get over a hump
rather than a systematic, mature process that insures you still have a
business a few years down the road.|||Doug (drmiller100@.hotmail.com) writes:
> huh. I never knew that ordering a view was unsupported. It always has
> worked, I bet it always will, but it is good to know.

Some people have found that "always" does not include SQL 2005.

> OTOH, if you take the EXACT same code, stuff it into a stored
> procedure, it is guaranteed to work.
> yet another reason not ot use views.

Yep!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Not the way to bet. Are you old enough to remember when GROUP BY did a
sort and T-SQL dialect-only programmers wrote code that depended on
that single-processor sort model? Things blew up after 6.5 because
they wanted to save a few keystrokes and to violate standards (or as I
call it "Speaking Hillbilly SQL").

The advantage of following standards is that you can escape the
contigous files/ contigous records physical model for MUCH better
implementations, parallelism, etc.|||Doug wrote:
> huh. I never knew that ordering a view was unsupported. It always has
> worked, I bet it always will, but it is good to know.
> OTOH, if you take the EXACT same code, stuff it into a stored
> procedure, it is guaranteed to work.
> yet another reason not ot use views.
> thanks!.

I was under the impression that one cannot select from a stored
procedure or do joins with it (the way one can treat MS Access querydefs
as virtual tables).

Unfortunately, I have a large number of views that need to be joined
with physical tables and other views. As far as I know (entirely
possible I'm wrong), my options are views or user-defined functions that
return tables.|||Beowulf wrote:

> Unfortunately, I have a large number of views that need to be joined
> with physical tables and other views. As far as I know (entirely
> possible I'm wrong), my options are views or user-defined functions that
> return tables.

Yes but in that case it would make no sense to use an "ordered" view
even if such a thing were possible. Forcing SQL Server to order the
data before you join it in a query might restrict the server from
utilizing a better query plan that implied a different ordering. This
is exactly why the optimizer is and should be free to ignore sorts in
views.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||A better method --
R T F M

I'll let you work out the letters for yourself.

You should always read the product specs for features you need to use, that
is the same in the standard and also the same for vendor features.

The changes from 6.5 to 7.0 where fully documented, there is a compatibility
level if you want to get the behaviour back; although not recomended but it
does give you time to modify your application.

Can the true be same of the ANSI standard? What happens when items are
discontinued? You are stuffed, worse still - you get NO warning!

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138670415.669208.42200@.g49g2000cwa.googlegro ups.com...
>>> I never knew that ordering a view was unsupported. It always has
>>> worked, I bet it always will, but it is good to know. <<
> Not the way to bet. Are you old enough to remember when GROUP BY did a
> sort and T-SQL dialect-only programmers wrote code that depended on
> that single-processor sort model? Things blew up after 6.5 because
> they wanted to save a few keystrokes and to violate standards (or as I
> call it "Speaking Hillbilly SQL").
> The advantage of following standards is that you can escape the
> contigous files/ contigous records physical model for MUCH better
> implementations, parallelism, etc.|||On Tue, 31 Jan 2006 01:26:51 GMT, Beowulf wrote:

>Doug wrote:
>> huh. I never knew that ordering a view was unsupported. It always has
>> worked, I bet it always will, but it is good to know.
>>
>> OTOH, if you take the EXACT same code, stuff it into a stored
>> procedure, it is guaranteed to work.
>>
>> yet another reason not ot use views.
>>
>> thanks!.
>I was under the impression that one cannot select from a stored
>procedure or do joins with it (the way one can treat MS Access querydefs
>as virtual tables).
>Unfortunately, I have a large number of views that need to be joined
>with physical tables and other views. As far as I know (entirely
>possible I'm wrong), my options are views or user-defined functions that
>return tables.

Hi Beowulf,

That's correct.

Comparing stored procedurs to views is like comparing a hammer to a saw:
they are designed for entirely different purposes, and a good carpenter
knows how to use them both.

--
Hugo Kornelis, SQL Server MVP|||Hello,

When do you prefer a view?

Thanks!
doug|||>> When do you prefer a VIEW? <<

1) For access control. A particular development team sees the schema
thru VIEWs that make it look as if the database was built just for
their project. This can mean some WITH CHECK OPTION clauses and
INSTEAD OF triggers.

2) When the same query is used by multiple users at the same time. For
example, Oracle will mateiralize and share a VIEW, so everyone can use
the same "end of the month" summary results without re-computing them
over and over.

3) When the same query is used in multiple places in a single query.
Now that we have CTEs, this does not apply anymore.|||Put embedded SQL into an application is dangerous, if the user has access,
word or any other modern day application they can connect to the database
and start writing their own queries!

It is a best practice to use stored procedures for access control, you can
permission on the stored procedure and do not need to give lower level
permissions to the views or base tabls, doing so increases your security
periminter that needs defending.

SQL Server has Indexed Views which I believe are better than the Oracle
materialised because once set up they are dynamically maintained so are
always up-to-date; before responding celko - make sure you read the product
spec aka books online.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138835222.845811.6020@.g44g2000cwa.googlegrou ps.com...
>>> When do you prefer a VIEW? <<
> 1) For access control. A particular development team sees the schema
> thru VIEWs that make it look as if the database was built just for
> their project. This can mean some WITH CHECK OPTION clauses and
> INSTEAD OF triggers.
> 2) When the same query is used by multiple users at the same time. For
> example, Oracle will mateiralize and share a VIEW, so everyone can use
> the same "end of the month" summary results without re-computing them
> over and over.
> 3) When the same query is used in multiple places in a single query.
> Now that we have CTEs, this does not apply anymore.|||> SQL Server has Indexed Views which I believe are better than the Oracle
> materialised because once set up they are dynamically maintained so are
> always up-to-date

Tony,
I don't think so. Oracle can refresh an MV on commit if you wish so,
which gives you an always up-to-date MV.

http://www.csee.umbc.edu/help/oracl.../a67775/ch5.htm

Refresh on commit, as opposed to refresh as part of every DML
statement, as SQL Server does,
is also in some cases better, as locks on MV are held for shorter time.

Also Oracle can store only the relevant changes and let you
incrementally (=quickly) refresh the MV later on.
This is convenient in OLTP situations when it's better to display
slightly stale data
than to deal with lock contention on MVs. Besides, Oracle have some
convenient features such as check constraints on MVs.

I've worked with indexed views/materialized views/DB2's materialized
query tables,
and I think Oracle's implementation is the best.|||On 31 Jan 2006 17:44:29 -0800, Doug wrote:

>Hello,
>When do you prefer a view?
>Thanks!
>doug

Hi Doug,

That's a pretty broad question!

In addition to Joe Celko's list, here are some more:

* If the same logic has to be used in many different queries, a regular
view is great.

* If a lot of queries use aggregated data of a large table, an indexed
view can speed up performance tremendously.

This is just of the top of my head. I'm sure there are more situations
where a view is a good solution.

--
Hugo Kornelis, SQL Server MVP|||Little use if after the months of toil your business has been overtaken, put
out of business and even bought up by your competitor - i think you have
first hand knowledge of that (funny that) <<insert rant: bloatware code
following portability costs money and takes longer>
You need to cut to the chase, give a good maintainable solution that can be
expanded (if the business needs expandability), a lot of the time stuff is
one off.

Even if it isn't, you are there to support the business, its not the other
way round! All too often IT seems to think the business is there to support
it, I see it so often, IT cripples a company with over burden.

Tony Rogerson SQL Server MVP.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138649321.187285.42500@.g44g2000cwa.googlegro ups.com...
>>> . I am looking for solutions to business problems. You are looking for
>>> something entirely different. <<
> What you seem to be looking for is a quick kludge to get over a hump
> rather than a systematic, mature process that insures you still have a
> business a few years down the road.|||hmmmm. Great discussions.

I have slipped into the mindset of never using a view, and I am
reexamining that mindset.

Everywhere I could use a view, I use a stored procedure. It seems to
me a sproc can have more access control then a view.

If I have users that want access to normalized data, I create a table
on a scheduled basis with all the data they need. It seems like there
are frequent times users want "report" data, and often it seems like
they would just as soon have it not change under them. On rare occasion
they want up to the minute data, and again I use a sproc to gather it
for them.

Again, a mindset. Could someone help me with why you would prefer a
view over a stored procedure? I can almost see the indexed views for
the right application, but there has to be a lot of overhead to
maintain that indexed view for large tables.|||Doug wrote:
> Again, a mindset. Could someone help me with why you would prefer a
> view over a stored procedure? I can almost see the indexed views for
> the right application, but there has to be a lot of overhead to
> maintain that indexed view for large tables.
I think a core question is:
Do you believe in writing complex SQL (say a 5 way join...).
If you do, then why would you not want to macro-ize common combinations
of joins you do over and over again?
If you prefer very simple SQL, full control, and cursors then, by all
means.. views may not be for you.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab|||> why would you not want to macro-ize common combinations of joins you do over and over again?

Serge,

In certain cases (not too often) I would rather not deal with a view,
for instance:
let's say you have a rather complex frequently used query, much more
complex than a simple 5 way join. You wrap it up as a view, and try to
use it like this:

select <columns> from my_view
where <a very selective SARGEable criteria on an indexed column in
table1
It runs slowly, so you look at the plan and see that that very
selective criteria on an indexed column is not applied first. You try
to push the condition down manually, instead of

select ... from(
select ...
from table1
...)
where <a very selective SARGEable criteria on an indexed column in
table1
you write

select ...
from (select ... from table1
where <a very selective SARGEable criteria on an indexed column in
table1>
) table1
...

and it runs fast. I would not say that's a very common scenario, but it
does happen from time to time.|||Alexander Kuznetsov wrote:
>>why would you not want to macro-ize common combinations of joins you do over and over again?
>
> Serge,
> In certain cases (not too often) I would rather not deal with a view,
> for instance:
> let's say you have a rather complex frequently used query, much more
> complex than a simple 5 way join. You wrap it up as a view, and try to
> use it like this:
> select <columns> from my_view
> where <a very selective SARGEable criteria on an indexed column in
> table1>
> It runs slowly, so you look at the plan and see that that very
> selective criteria on an indexed column is not applied first. You try
> to push the condition down manually, instead of
> select ... from(
> select ...
> from table1
> ...)
> where <a very selective SARGEable criteria on an indexed column in
> table1>
> you write
> select ...
> from (select ... from table1
> where <a very selective SARGEable criteria on an indexed column in
> table1>
> ) table1
> ...
> and it runs fast. I would not say that's a very common scenario, but it
> does happen from time to time.
That is hard to imagine to happen on newer releases of the major
commercial DBMS'. Either way throwing out views due to an isolated bad
plan is throwing out the baby with the bathwater.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab|||>> Could someone help me with why you would prefer a view over a stored procedure? <<

1) Portable declarative code versus proprietary, procedural code.
2) Security via DCL (GRANT, DENY and REVOKE) that most programmers do
not know about.
3) The WITH CHECK OPTION that most programmers do not know about
4) Possible for in-line expansion of code to help the optimizer whenteh
VIEW is invoked.
5) Possible sharing of data among queries when the VIEW is
materialized. Oracle is good about that.|||> If you prefer very simple SQL, full control, and cursors then, by all
> means.. views may not be for you.

Why do you associate not using views with cursors?

Views can encapsulate query logic - yes, but you then end up (usually)
nesting views and its then a real bitch to do any performance
tuning/optimisation; i've had this problem a number of times where the
client has made extensive use of views. And to make matters worse they tend
to embed their SQL into the application as well - perhaps its a combined
mindset thing.

If you are continually writing the same 5 join query then you need to
consider if your design is right anyway.

Don't get me wrong - I do use VIEWS in some situations, but I ***NEVER***
allow them to be called directly from the client application, a reporting /
BI tool yes - but thats a different model and you don't usually need to give
end users permissions directly onto the views that make up the model.

Tony Rogerson, SQL Server MVP
http://sqlserverfaq.com

"Serge Rielau" <srielau@.ca.ibm.com> wrote in message
news:44hkvuF284scU1@.individual.net...
> Doug wrote:
>> Again, a mindset. Could someone help me with why you would prefer a
>> view over a stored procedure? I can almost see the indexed views for
>> the right application, but there has to be a lot of overhead to
>> maintain that indexed view for large tables.
> I think a core question is:
> Do you believe in writing complex SQL (say a 5 way join...).
> If you do, then why would you not want to macro-ize common combinations of
> joins you do over and over again?
> If you prefer very simple SQL, full control, and cursors then, by all
> means.. views may not be for you.
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> DB2 UDB for Linux, Unix, Windows
> IBM Toronto Lab|||>let's say you have a rather complex frequently used query, much more
complex than a simple 5 way join. You wrap it up as a view, and try to
use it like this

sql server 2000 does this.
besides, do you really want the same exact set of columns in a lot of
different cases, or are you really shotgunning and returning 50 columns
you really don't need?|||so celko, if you use totally views, then where does your sql calls go?
do you do selects against views buried in your code?

that is before you killed off all your patients.|||>> so celko, if you use totally views, then where does your sql calls go? do you do selects against views buried in your code? <<

No, I tend to use procedure calls with or without parameters. VIEWS
are in the Database side of the house.

>> that is before you killed off all your patients. <<

Not fun. I am very tired of burying children who were killed by bad
programmng that screwed up medication delivery to Africa, illegal bomb
delivery in Cambodia, etc. My adopted daughter is going to die in her
30's because of incompetence in the foster care system. I have a
sarcastic sense of wit that has no relgious taboos, but you are close
to the line.|||>I have a sarcastic sense of wit that has no relgious taboos, but you are close
to the line.

My apoligies. I did not mean to make fun of your religious beliefs.
My intent was rather to make fun of your views.

(pun intended.)

doug miller|||We've hit this Order By problem as well. I don't mind exactly that it
changed from SQL Server 2000. I am a bit torqued that at MSDN the
article Behavior Changes to Database Engine Features in SQL Server 2005
(http://msdn2.microsoft.com/en-us/library/ms143359.aspx) doesn't say
anything remotely like, "This used to work but this way, but not
anymore." I'm also a bit miffed that in the conversion process nothing
says to you (according to our DBA) "Oh, those Order By clauses? Forget
about them."

I would be only too happy for someone to point to the document that
would have alerted us (without having to read Books Online end-to-end)
to this problem. And yes, it is a problem.

Here's the thing I really don't get: If by definition (as always) and
now in fact (with SS2005) you simply cannot return an ordered set from a
view, how exactly are you supposed to cope with that in an application?

I'm using Access, looking at data via linked tables with views as their
sources. All of a sudden, these don't return ordered sets. Fine, I'll
just go through and rewite all my views as stored procedures or
functions.

(By the way, don't anybody utter a word about SQL Server being "within
its rights", or any other such theoretical rubbish. It's a tool. It
has no rights. It is convenient, or it is not. Right now, it is not.)

Oh, wait. I forgot! I can't link to functions or stored procedures
from Access. (Another 'by the way': No prissy "oooohh, why are you
using Access?" nonsense either.)

Hmmmmmmm...

We even tried setting compatibility back a version, and still no joy.

I would love nothing more than to be told that I've missed a good
workaround or that we goofed in the compatibility setting or that
somewhere here I have just boneheadedly overlooked a simple way around
all of this. Modifying all of my view-based data-consuming objects in
the application does not appeal to me, but it's looking inevitable right
now.

Another thought: We were about half way to converting all of our local
Access queries to views. Maybe that's not such a great idea after all.
At the moment, it looks like to only way I'm going to get an ordered set
out of SQL Server is with a local query or a report (same thing under
the hood).

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> Here's the thing I really don't get: If by definition (as always) and
> now in fact (with SS2005) you simply cannot return an ordered set from a
> view, how exactly are you supposed to cope with that in an application?

Use a proc. Or use ORDER BY in whatever other query accesses the view.

I could sympathise with a low budget, part-time developer who has to
deal with this but apparently you have little excuse. If your shop is
large enough to have a DBA and develops as much code as you seem to
imply then programming standards, best practices and peer reviews ought
to be in place to prevent screw ups. Your business has a right to
expect that from you. How about setting someone to document some better
programming standards?

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I sort of expected this kind of response, but since it serves utterly no
useful purpose whatsoever, I'm going to move on to a question more along
the lines suited to the kind of answers that predominate here.

Let's take a quick inventory.

Table - by definition, no sort order allowed. No problem.

View - by definition, no sort order allowed. Workaround previously
available, now withdrawn, but let's allow it for discussion. (Would
have been nice to have some warning, but what the hell.)

Table-returning Function - Testing shows that it also does not preserve
the specified order in a result set. OK. This is obvious. It returns
a table. No sort allowed.

Stored Procedure - Testing shows that ... it DOES return an ordered data
set. Wait a minute. That can't be right. It is returning...a table!
And, by definition, no sort allowed on a table.

And yet no one is griping about that. In fact, DP's own answer begins
with "Use a proc." But that can't be good. A proc, returning a table,
should not preserve order.

If we followed this line of reasoning to the limit, there would simply
be no way to return an ordered result set by using any native SS object.
That would have to depend on using a query outside SS. But wait a
minute, writing SQL in code is a sign of amateurishness. It's all
supposed to be done with parameters fed to stored procedures. Now
what!?

The plain fact is that SQL Server is a tool. Nothing more. It is a
compromise to allow a stored procedure to return an ordered set, unless
the hallowed standard being referred to in other posts has somewhere in
it an exception for SQL-Server-specific objects such as stored
procedures.

Preserving order in a result set from a stored procedure is a
compromise. It's a nice compromise. I like it. But I also liked the
compromise whereby a view could also return an ordered set. I don't see
a reason on earth to believe that the stored procedure compromise is
fine, but the view compromise is unacceptable.

I would, honestly, love to read the rationale for saying the it simplay
cannot be allowed in a view, but it is just fine (even okay to recommend
to someone as a solution) to allow it in a stored procedure.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> I sort of expected this kind of response, but since it serves utterly no
> useful purpose whatsoever

I'm sorry you think that. Maybe you didn't read my reply too well. I
gave at least two practical suggestions: Use a proc; Improve your
standards.

One of the problems with SQL is that it doesn't fully support the
property of Relational Closure. In a true relational database we would
indeed expect every query to return a relation rather than an ordered
result set. However your statement about SQL queries returning tables
isn't quite correct. SELECT statements don't always return results that
qualify as a table even by SQL's definition. Neither SQL tables nor SQL
query results can always qualify as relations.

However, standard SQL and in general also SQL Server DO define the
results of queries as unsorted. The only time when ordering works is
when data is returned to the client because in that case the query
result is exposed as a cursor for row-bound operations at the client
side. It is the cursor-based result set that is ordered, not the query.
In well-designed systems it is usually the function of stored
procedures to return results to the client so that is where ORDER BY
clauses should typically be located.

There is perfectly good rationale why views should be unordered like
tables. I suggest you read a book on relational fundamentals for the
full explanations but meantime I'll just restate one obvious benefit
among many. Suppose you want to query the view in some way that filters
the results and returns only a small subset of the rows in the original
view. To force the view to sort the entire result before selecting from
it would be extremely inefficient, especially if you then wanted to
sort the query result in some different order. Since SQL Server has no
way to determine at creation time how a view will be used it follows
that views should be unsorted to support the most efficient query plan
in each case.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Well, I appreciate the reply, and I see the truth of some of the things
you say, but the summary is that "there is a perfectly good reason why
we should do it sometimes and not others." Stored procedures are
somehow granted an exemption on grounds that are not clear.

Besides, your own statement ("Neither SQL tables nor SQL query results
can always qualify as relations") sounds to me like an excellent reason
to leave a very useful function in the tool, since it's being removed
for a theoretical reason that doesn't even necessarily apply properly to
all the results that it affects.

How about having the tool parse a view that reads other views, and
ignore the ordering in the underlying views. There's your conservation
of resources, which is the true reason to do it, not agreement with a
definition studied in relational theory. Then, have the tool be a sport
when it returns its own result set and apply the ordering to the result
set.

Then, if you want to live purely, you just never put Order By in your
views. You would live this way. If you want to use the tool to push
the sort to the machine with the muscle, you put the Order By where you
want it. I would live this way. SQL Server could even still issue a
little warning that says "you shouldn't do this if you want to live
purely."

You and I only disagree on where exactly the boundary for compromise
should be drawn when creating the tool. I want it a little bit back
towards where it was. You want it further over somewhere, but not
really all the way. And none of the supporting arguments are improved
by the condescending tone you adopt when you make huge and unfounded
assumptions about how I would like to be doing my job.

Thanks for the very detailed replies. They are interesting and serve as
terrific refresher courses. Clears out a few cobwebs. On that account,
lots of fun here.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||>> We've hit this Order By problem as well. I don't mind exactly that it changed from SQL Server 2000. I am a bit torqued that at MSDN the article Behavior Changes to Database Engine Features in SQL Server 2005doesn't say anything remotely like, "This used to work but this way, but not anymore." I'm also a bit miffed that in the conversion process nothing says to you (according to our DBA) "Oh, those Order By clauses? Forget about them." <<

This is how God, Microsoft and your own ignornace punish you baaaaad
programmers who ignored ANSI/ISO Standards and wrote crappy proprietary
code to a particular release of a particular product.

>> you simply cannot return an ordered set from a view, how exactly are you supposed to cope with that in an application? <<

By writing good code in the first place. DUH!!! Have you read any of
Tony Rogerson's postings praising proprietary code to get speed? Have
you read any of my postings about avoiding proprietary code to get
portability? Gee, sure sounds like I was right, doesn't it?

A VIEW with an ordering is absurd; a VIEW is a table and **by
definition** has no ordering. DUH! And it always has been that way
since 1986. This is no surprise to REAL SQL programmers.

If you want to REALLY fix your problems, contact me off line. Or stay
on Newsgroup and collect more Kludges until your company disappears. I
am not available in May; I will be teaching SQL in Brazil for two weeks
in the hope of helping a chemical company not get to wher you are.|||Ignoring the rude, ego-inflated hot air that blasts off the page, I
reply with a few details.

I don't think our company is going to disappear because 31 views had an
Order By clause in them. In about two hours I moved about half the
Order By clauses to the SQL-building routines in the front end of the
application, and the rest will finish tomorrow. I was hoping to leave
the sorting on the machine with the muscle, but too bad for me.

I have not read any of Tony Rogerson's postings, and if your tone in the
responses in this thread are a sample of yours, I don't plan to rush to
find them. Come to think of it, if becoming a REAL SQL PROGRAMMER means
having to talk to people as you do, I may not worry too much about
taking that path, either.

You said: "A VIEW with an ordering is absurd; a VIEW is a table and **by
definition** has no ordering. DUH! And it always has been that way since
1986. This is no surprise to REAL SQL programmers."

Well DUH, and DUH again. (If we had better graphics, you could put
little frownie faces as the dots on your i's, too.) Your friend DP will
tell you that a view does not always return a table, properly speaking.
You say it does. If so, then doesn't a stored procedure also return a
table? And if so, why is it okay to sort a table returned by a stored
procedure?

And I'm not surprised at all by any of what anyone has said here. I'm a
bit ticked that a very useful feature, in place for almost a decade, was
taken away without more reasonable notice. I would have thought it
would show up on the MSDN page I quoted in an earlier reply.

I will assume that your suggestion to contact you offline is an attempt
at humor. If so, it's a pretty good one. You can't possibly think that
anything you said in your reply would make working with you seem
appealing.

Let me think...Wouldn't I love to hire someone who loves to insult
people, who writes like an angry teenager, and who overrates his own
understanding and abilities with every pronouncement? And who wraps
exactly one informative sentence ("a VIEW is a table and **by
definition** has no ordering") in 10 ranting insults? Hmmmm. Tough
call.

Come to think of it, I'm curious: Why exactly did you bother to reply?
Did you accomplish what you intended? What exactly was that?

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> The plain fact is that SQL Server is a tool. Nothing more. It is a
> compromise to allow a stored procedure to return an ordered set, unless
> the hallowed standard being referred to in other posts has somewhere in
> it an exception for SQL-Server-specific objects such as stored
> procedures.
A procedure returns a _resultset_, a view and a table function are
_derived_tables_.
A _resultset_ (typically processed by a cursor) is an interface to the
procedural language while a _derived_table_ is part of the transitive
closure of SQL. (table in => table out)

May I ask what's so bad about placing the ORDER BY in the cursors
selecting from the view? Can't be any harder than replacing the queries
with stored procedure invocations and allows the app to customize the
order to its needs, so if you want to display, order it. If all you want
is move the data around: don't and save the DBMS some CPU cycles.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Serge - Thanks for the info. Very concise and relevant.

If you're asking me what's so bad about placing the ORDER BY in the
cursors selecting from the view, nothing really. I assume you're
referring to my Access queries in this case. I've moved about half the
Order By clauses and will finish the other half tomorrow.

I used SS to write out the view scripts, chucked the rows of text in a
table in Access, located the ones with ORDER BY clauses, started looking
in code for uses of those views, and pasted or typed the order by in.
Had to remove some table qualifiers, and in a couple of them had to sort
by a field returned in the view instead of a field available but not
returned in the view. (Trying to get the covering index to kick in
instead of sorting on a calculated column.)

At our level of data, I don't even see an appreciable performance hit.
We only have about 1.5 GB total, and we're never looking at a
significant portion of it at any one time.

The recovery itself has proven pretty painless.

And you're absolutely right, it's nothing compared to rewriting
everything as stored procedures etc etc etc.

On saving CPU cycles: I would absolutely agree that a view based on
other views should be parsed and processed so that the ordering in all
underlying views is ignored. Unquestionably beneficial. I just didn't
agree that the ability to retrieve an ordered set from a real-world tool
was an unforgiveable sin against the theoretical definition of the view.
Some do not share this libertine view.

Thanks again.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:

> I'm a
> bit ticked that a very useful feature, in place for almost a decade, was
> taken away without more reasonable notice. I would have thought it
> would show up on the MSDN page I quoted in an earlier reply.

Your unwillingness to learn is so amazing that I wonder why you
bothered to ask at all. There was no notice in the docs because no
feature was removed. It's just that you assumed the existence of a
feature that never was there. Even in SQL Server 2000 queries against
views containing ORDER BY don't always respect any predetermined order.
In many cases ORDER BY in views will be ignored. I believe that has
been true of every version of SQL Server. Your elementary mistake
reminds me of the joke about the economist who sees a brown cow and
therefore assumes all cows are brown.

I'd second Joe's suggestion: get some training. Trial and error is the
most expensive form of education.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||RickW wrote:
> On saving CPU cycles: I would absolutely agree that a view based on
> other views should be parsed and processed so that the ordering in all
> underlying views is ignored. Unquestionably beneficial. I just didn't
> agree that the ability to retrieve an ordered set from a real-world tool
> was an unforgiveable sin against the theoretical definition of the view.
> Some do not share this libertine view.
Well, don't mistake my impassionate response for sympathy ;-)

You are correct that your DBMS is a tool, but when the tool starts
violating the very principles upon which it is built that may result in
a tactical raise in customer satisfaction, but causes longterm grief by
pasting the architecture into a corner it can't get out of.
FWIW DB2 behaves exactly the same way for the exact same reasons.
"United we stand" ;-)

Now, lets take a look at the business requirement here you have against
"the tool":
From what I understand you have these views and you'd want that when an
application selects from it it should be able to get results back in an
order which has been defined by the creator of the view. Correct?
If so the question that should be asked (from a language point of view) is:
Does the concept of a "default" or "natural" order violate the
foundation of relational algebra? I think no, as long as this default
order is _explicitly_ requested just like you can explicitly SET c1 =
DEFAULT.

Example:
CREATE TABLE T(pk NOT NULL PRIMARY KEY, c1);
SELECT * FROM T ORDER BY _ORDER_OF_T_;
This could e.g. mean: Please pick up the order defined by the primary
key index.
For "SELECT * FROM V ORDER BY ORDER OF V" this could mean to pick up teh
ORDER BY clause in the view. If you omit this the ORDER BY you get what
you get (no order).

We (IBM) have been mulling this over as an SQL standard submission.
Would be interesting to read the thoughts of Microsoft folks.

As language folks we have to be perceptive to the practical usages of
the language.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||I'm not troubled at all by your lack of sympathy for my position. I
learn far more from people who disagree with me than from those who
agree, if the exchanges focus on topic-related information, as yours do.

If I understand correctly, you're mulling over an addition to the
standard, motivated a consideration of actual use of the data. Not that
it should matter, but you absolutely have my vote for that.

Clearly, at some point in the process users will need to see data in a
particular order. In these postings I've seen explicit endorsement for
allowing ordering in a stored procedure, but no viable presentation as
to why it's okay to allow it there but not in a view. Falling back on
"by definition" strikes me as mistaking the map for the territory. In
fact, it's more like believing that the map dictates what the territory
can be.

I have an alternate suggestion for the _order_of_v_ proposal. It seems
to me that taking that approach requires you to create a view B in order
to specify that you want in fact to return a result set ordered by the
specification in view A. Without any new definition element, I could
apply any orering I wanted in a hypothetical view B outside the database
tool. So, although I gain the function, the price is another view.

Would it be just as explicit, and save a lot of view creation, to add an
explicit element following all other SQL, such as [RESULTSORDERED]?
(brackets indicate optional element)

If not present, results would be unordered. That leaves all properly
constructed views working exactly as currently prescribed. If present,
being an explicit element of the definition, it violates nothing but
allows for ordering of a result set. This allow people like me to
continue to bypass creating yet another query outside the tool whenever
possible.

In fact, what I think Microsoft should have done was in effect the
solution I propose above. Following their paradigm for things, that
would show up as a new property on views. By default it would be set to
false. If you wanted to return the results ordered, you would have to
set the property explicitly on each view where it mattered.

Is there is a chance in hell of getting such an element added to the
definition of a view? If so, where do I sign up for the campaign?

Thanks for another post that contributes something to the discussion and
points in an interesting direction. I'm eager to see the responses
myself.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||> Falling back on
> "by definition" strikes me as mistaking the map for the territory. In
> fact, it's more like believing that the map dictates what the territory
> can be.

Rick,

In general I could agree to that. Yet this is a SQL Server newsgroup,
and specifically for SQL Server (as well as for every other RDMBS I
worked with ;) ) it is not the best practice to sort each and every
result set on the server. In many cases sorting could be done just as
easily on the client, and moving some sorts to the client/midle tier in
many cases leads to much better performance. You can read more about it
here:

http://www.devx.com/dbzone/Article/30149|||Happy to report, Techniques 1 & 2 in place.

Technique 3: I don't remember having any case statements in order by.
I would check, but I suppose it doesn't matter anyway, since I'm
removing all the order by clauses from the views except where merited by
proper use of Top. In any event, it's a clever tip. Hope I remember it
when the occasion arises.

Technique 4: Very handy, especially as I'm new in .NET and this is a
clearly useful tip. I probably would have by habit put the Order By in
the SQL that goes up to the server. I can imagine that if a have a very
large return (rare in our case), a hefty server compared to the load,
and rather wimpy client machines, I might send the Order By up the line
to let the server do it, but staying mindful of your tip I have the
option to do it only when merited.

Thanks so much for the link.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||Rick,

I don't get your logic forcing the extra view.

Example:
CREATE TABLE T(c1);
CREATE VIEW V AS SELECT * FROM T ORDER BY c1 ASC;

-- I don't care:
SELECT * FROM V => no order

-- I want "the" order:
SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING
Note that the clause is rather brain dead. Your .NET/JDBC/PHP client
could tack it on under the covers if you always want it.

-- I want order and I'm going to decide what it is:
SELECT * FROM V ORDER BY c1 DESC => c1 DESCENDING

The problem with your statement level switch is that it is - eh -
statement level.
That is what if you have a nested view somewhere embedded.
You probably don't want the DBMS to sort unless the order finds it's way
to the top.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||>> Clearly, at some point in the process users will need to see data in a particular order. <<

My first response was "Which particular order? Why?", but then I
realized that this is not true. I can design a parallel language which
uses no ordering in the outputs.

>> Is there is a chance in hell of getting such an element added to the definition of a view? If so, where do I sign up for the campaign? <<

You can sign up for the ANSI Standards committee and write a proposal.
But you will need to defend it. And since we have looked at this issue
before, you will have to work at the level of a PhD to get around the
objections and proofs of contradiction that other people found in it.

Let's throw out the foundations of the Relational Model and assume
that we can write crap like

SELECT ..
FROM ..
WHERE ..
[GROUP BY ..]
[HAVING BY ..]
[ORDER BY ..];

If I have ordering on VIEWs, I must have ordering on TABLEs. Or you
have to change the RM to have both ordered and unordered tables with
all the rules for moving data between them.

A. What is the meaning of sorts by things not in the select clause?
Gee, I guess we need to carry extra data on each row for the sort, even
if it is not used.

SELECT a, b
FROM Foobar
ORDER BY x;

Even worse, what is the output of this? Remember that a GROUP BY
destroys the original table to produce a grouped table, so x does not
even exist anymore.

SELECT a, SUM(b)
FROM Foobar
GROUP BY a
ORDER BY x;

B. What is the meaning of sorts on nested subqueries? Which sort has
priority?

SELECT a, c
FROM (SELECT x, y
FROM Barfoo
ORDER BY x) AS XX(a, c)
ORDER BY c, a;

C. What is the meaning of sorts on UNION, INTERSECT and EXCEPT?
Remember that (A UNION B) = (B UNION A), or do you want to throw out
all of Set Theory along with the Relational Model?

SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY x;

What if I add an ORDER BY to the whole thing?

SELECT r, s
FROM (SELECT a, b
FROM Foobar
ORDER BY a
UNION ALL
SELECT x, y
FROM Barfoo
ORDER BY y)
AS XX(r, s)
ORDER BY r, s;

D. What do UPDATE, INSERT and DELETE with ordered table mean? For
example:

INSERT INTO Foobar -- asc ordering
SELECT .. FROM Barfoo; -- desc ordering

E. Another problem here is that there are two kinds of sorts, stable
and non-stable. Now the Standard has to pick an implementation method.
We hate doing that.

Non-stable sorts are much faster than stable (aka "sequence
preserving"); look up QuickSort versus Bubble sort. With the current
cursor model, I have one and only one sort which can be done any way
the optimizer thinks will work best.

If you want non-stable sorting, then only the last sort applied to the
result table matters. But which one is it?? Well, now we need to
specify the absolute order of execution - no optimizer changes,
please.

I also cannot parallelize my code because it would not have an
ordering.

Why is this ordering worth destroying any possible way of having
parallelism or optimization? One of the best things about a VIEW is
that the optimizer can "cut & paste" the VIEW into a query and
fetch the rows in whatever order is required. Your model has to
materialize and then order the table, even if it screws up
optimization.

The advantage of a single ORDER BY in the cursor is that it can be
factored into the optimizer by looking at indexes, or saved for the
middle tier.|||--CELKO-- wrote:
<snip>
Joe,

There is a difference between an ordered table and an object (table,
view, table-function, ..) with which you associate a "suggested order
for your viewing pleasure"
BTW, DB2 has shipped nested ORDER BY in V8 and we have not seen any issues.

What's wrong with allowing the definer of an object to propose such an
order and the user to say: "Hey whatever you say man, I want order, but
I trust you thought about how it looks best."

This is no more dangerous than asking for the special at your local
restaurant. No one forces you to eat it and you don't get it by default
either.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||>>You probably don't want the DBMS to sort unless the order finds it's
way to the top.

Definitely.

What I thought you were setting up was this:

View A could specify an Order By clause, but it would not guarantee an
ordered result set.

Then View B could either SELECT x, y, z... FROM A and not invoke the
ordering in the result set or SELECT x, y, z...FROM A ORDER BY
_ORDER_OF_A_, and that would force the result set to return ordered by
the specification in View A.

I think the "SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING" view
is what I'm calling View B, the second one that's needed in order to
invoke the ordering.

I was thinking my suggestion would avoid needing B to force the
ordering.

For you is the thing I call View B a query from outside the database
tool, such as a SQL statement passed in from a client, like in the
example you pointed to with your link to the 4 techniques?

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> >>You probably don't want the DBMS to sort unless the order finds it's
> way to the top.
> Definitely.
> What I thought you were setting up was this:
> View A could specify an Order By clause, but it would not guarantee an
> ordered result set.
> Then View B could either SELECT x, y, z... FROM A and not invoke the
> ordering in the result set or SELECT x, y, z...FROM A ORDER BY
> _ORDER_OF_A_, and that would force the result set to return ordered by
> the specification in View A.
> I think the "SELECT * FROM V ORDER BY ORDER OF V => C1 ASCENDING" view
> is what I'm calling View B, the second one that's needed in order to
> invoke the ordering.
> I was thinking my suggestion would avoid needing B to force the
> ordering.
> For you is the thing I call View B a query from outside the database
> tool, such as a SQL statement passed in from a client, like in the
> example you pointed to with your link to the 4 techniques?

Views don't return any data. Only the queries issued against a view can
return data to the client. That means either queries in a stored
procedure or in a client application. So those queries would still have
to be the place for your "ORDER BY _ORDER_OF_A_" clause.

There is a problem. The predefined view order can only work if the
column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
list of the *query* (not just the view). Otherwise it may not be
possible to determine the correct order and perform the sort. For this
reason, standard SQL (and other SQLs too in many cases) will raise an
error if the ORDER BY columns aren't also included in the SELECT list.
The implication is that the person writing the _ORDER_OF_A _ clause in
the query or proc must A) know which columns determine the order, and
B) include those columns in the SELECT list. So given those constraints
why wouldn't he or she just type the column names in the ORDER BY list
as well?

Remember also that SELECT * is very bad practice and isn't generally
used at all in production code so SELECT * is no good excuse for not
typing an ORDER BY clause. If your syntax is based on the assumption
that SELECT * will be used then it isn't likely to gain wide support
because most developers wouldn't be able or willing to take advantage
of it.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote:
> Views don't return any data. Only the queries issued against a view can
> return data to the client. That means either queries in a stored
> procedure or in a client application. So those queries would still have
> to be the place for your "ORDER BY _ORDER_OF_A_" clause.
Correct. The need for the query was there before. My proposal does not
change that.

> There is a problem. The predefined view order can only work if the
> column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
> list of the *query* (not just the view). Otherwise it may not be
> possible to determine the correct order and perform the sort. For this
> reason, standard SQL (and other SQLs too in many cases) will raise an
> error if the ORDER BY columns aren't also included in the SELECT list.
I don't think this is the case in standard SQL (but I don't have the
time to dig through it now. FWIW DB2 does not require the columns to be
specified in the select list.

> The implication is that the person writing the _ORDER_OF_A _ clause in
> the query or proc must A) know which columns determine the order, and
> B) include those columns in the SELECT list. So given those constraints
> why wouldn't he or she just type the column names in the ORDER BY list
> as well?
Without relaxing the limitation you encountering that would be correct
and indeed undesirable. Given that views get expanded into the query
there is no technical reason for the presence of the order by columns in
the view signature (the columns exposed by the view).
I would guess that SQL Server must know the internal concept of
invisible columns (such as row-id) which is used to pull through order
without infesting the exposed select list.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Serge Rielau wrote:

> Given that views get expanded into the query
> there is no technical reason for the presence of the order by columns in
> the view signature (the columns exposed by the view).

So what would you expect to be the order of rows output by ORDER BY
_ORDER_OF_v1_ in the following example? Are you sure you can define
that order for all possible queries? Does it make a difference if I
leave Z in the view or not?

I suspect that any deterministic logic you define is going to be so
obscure that it would outweigh any apparent attraction of the syntax
shortcut you are proposing. It would cause more confusion and
unjustified assumptions about what the "correct" order should be. In
other words it would add nothing in terms of clarity or concision.

CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));

INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);

CREATE VIEW v1 AS
SELECT x,z
FROM tbl
ORDER BY z /* note: not legal SQL */

SELECT x
FROM v1
GROUP BY x
ORDER BY _ORDER_OF_v1_ ;

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote:
> Serge Rielau wrote:
>> Given that views get expanded into the query
>> there is no technical reason for the presence of the order by columns in
>> the view signature (the columns exposed by the view).
> So what would you expect to be the order of rows output by ORDER BY
> _ORDER_OF_v1_ in the following example? Are you sure you can define
> that order for all possible queries? Does it make a difference if I
> leave Z in the view or not?
> I suspect that any deterministic logic you define is going to be so
> obscure that it would outweigh any apparent attraction of the syntax
> shortcut you are proposing. It would cause more confusion and
> unjustified assumptions about what the "correct" order should be. In
> other words it would add nothing in terms of clarity or concision.
> CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
> (x,z));
> INSERT INTO tbl (x,z) VALUES (100,1);
> INSERT INTO tbl (x,z) VALUES (100,2);
> INSERT INTO tbl (x,z) VALUES (200,0);
> INSERT INTO tbl (x,z) VALUES (200,4);
> CREATE VIEW v1 AS
> SELECT x,z
> FROM tbl
> ORDER BY z /* note: not legal SQL */
> SELECT x
> FROM v1
> GROUP BY x
> ORDER BY _ORDER_OF_v1_ ;
Very good example. Now this of course cannot preserve the order.
One might argue that it should, in fact, return an error.
But does a feature of convenience need to work under all circumstances?
This query also is not updatable, or deletable yet SQL clearly supports
updatable cursors. It simply slaps your wrists when you ask for semantic
nonsense.
Here the user chose to further process the rows of the view in way that
cannot preserve order. That does not invalidate the usefulness of the
proposal IMHO.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Serge Rielau wrote:
> Here the user chose to further process the rows of the view in way that
> cannot preserve order. That does not invalidate the usefulness of the
> proposal IMHO.

So here's the problem. There are many, many examples using joins,
unions, aggregations and projections where ordering cannot sensibly be
permitted based on the underlying view(s). So do you want to detect
these at design time and raise an error or do you just give a warning
and allow creation of a query with the ORDER BY clause that won't in
fact be ordered?

If you allow the creation of queries and views that have these invalid
ORDER_OF_? clauses then what if the ORDER BY clause on the base view
changes? The person creating the view will presumably see a warning but
what about the users of queries against that view? They will see
nothing wrong. The users' queries will still have the same ORDER_OF_?
clauses and they will even appear to work normally. They may even
display the same ordering sometimes but then at other times they may
show a different order because in reality no logical ordering is
actually taking place. Now if the users are anything like RickW they
will be saying "My view has an ORDER BY but it isn't ordered. It used
to work in the old version. IBM stinks. They did this to me without any
advance warning."

I'd say the only reasonable solution is to validate the ORDER_OF_?
clause EVERY TIME a query is created or executed and to disallow the
query if the ordering is a non-permitted one. This would surely be
incredibly frustrating to the user unless he knows the column names
being used in the ordering or has access to that information. Only if
the user developing the query knows what the order is can he know
whether or not it is appropriate to use ORDER BY ORDER_OF_?.

So haven't we come full circle? Not only does the user have to know
which views contain ORDER BY clauses he also has to know what the order
is. If the user KNOWS what the ordering is and has to take that into
account when designing the query then what do we gain from the
ORDER_OF_? clause? Is this really only about saving a few keystrokes
and does that really justify the added complexity of validating views
and queries?

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote "... then what if the ORDER BY clause on the base
view changes?"

If it changes, it changes, and you (or at least I) would hope that the
designer in charge of the base view would understand that design changes
of that nature should come in response to user request, formal review,
and acceptance of the effect, not in silence just because the designer
felt like it.

I don't think that this possibility supports your argument.

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> David Portas wrote "... then what if the ORDER BY clause on the base
> view changes?"
> If it changes, it changes, and you (or at least I) would hope that the
> designer in charge of the base view would understand that design changes
> of that nature should come in response to user request, formal review,
> and acceptance of the effect, not in silence just because the designer
> felt like it.
> I don't think that this possibility supports your argument.
> *** Sent via Developersdex http://www.developersdex.com ***

I agree with you. My principal objection is not that ordering views
makes change management harder (although it must do to some degree). My
argument is that for it to work the users must be aware of what
ordering exsits in the view and must take account of that when
designing their queries. So in my opinion there is no usability benefit
to be had from an ordered view other than saving the time it takes to
type in the column names.

If saving keystrokes is your goal then more benefit could be obtained
from creating a better development environment. I'm talking about
features like the object browser in Management Studio that save you
from typing lists of column names.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas wrote:
> Serge Rielau wrote:
>> Here the user chose to further process the rows of the view in way that
>> cannot preserve order. That does not invalidate the usefulness of the
>> proposal IMHO.
>>
> So here's the problem. There are many, many examples using joins,
> unions, aggregations and projections where ordering cannot sensibly be
> permitted based on the underlying view(s). So do you want to detect
> these at design time and raise an error or do you just give a warning
> and allow creation of a query with the ORDER BY clause that won't in
> fact be ordered?
Actually not UNION.
SELECT * FROM (SELECT c1 FROM T1 UNION ALL SELECT c1 FROM T2) AS X
Does not allow you to reference T1 or T2.
If your view contains the UNION then of course it can order the result
of the union.

There are to orthogonal pieced here:
Defining a "suggested" ordering and consuming that ordering.
Let's assume I have created a view with an order by.
Let's assume I have written some queries that consume this order by
correctly (no aggregation, funny join).
Now I alter the view and I change the order by clause.
Nothing will change. all queries will continue to work using the new
order. this is no different than if you alter the DEFAULT of a column.
It doesn't break any INSERT or UPDATE statements.

Let's go back to the original problem:
The OP wanted to _encapsulate_ the order inside of an object (preferably
a view). This is absolutely achieved.
If the query succeeded against the object once it will always succeed,
because the only way to break the order by is through bad specification
of the query itself.
If you screw up the ORDER BY for a result set in a procedure exactly the
same implications arise. Of course the app will see the difference and
presumably this is the whole idea.

W.r.t. what happens in the bad case: Yes, of course the DBMS should
raise and error if it can't do what the user requests.
I see no point, btw., in raising an error when the underlying object has
NO ORDER BY to begin with.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||David Portas wrote: My argument is that for it to work the users must
be aware of what ordering exsits in the view and must take account of
that when designing their queries. So in my opinion there is no
usability benefit to be had from an ordered view other than saving the
time it takes to type in the column names.

Some of the back-and-forth in this thread stems from a difference in
what is meant by users. From where I sit, users do not design queries.
In your sense, I am the user, the only user. I will always know what
columns are available.

Since I design the views and also consume them in my application front
end, there is usability benefit to be had in knowing that I have a
single, ordered source of a particular kind of data on the server, and
that I do not have to re-create the SQL for that anywhere in the
application, not even as a constant or in a config file.

I haven't disagreed with the definition-driven arguments, insofar as
I've understood them. I only disagree with the idea that, when building
a tool, such a definition is the only factor to consider. Especially a
definition that takes no notice of a major reason for collecting and
presenting data: to show an application user something in a certain
order.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW,

Another solution is to go back to SQL Server 2000.

In Access, can you ask "Access" to return your result sets in order?
Perhaps that MIGHT force the SQL getting the data from the view to
encourage a sort order. Can you put that code into the link getting the
data?

This is an interesting little hole.

I will submit that we have indeed found a "bug." If order by is not
"legal" for a view, then why does it compile, and not raise an error
when you save it off?

SQL Server is a product. It is being sold to CUSTOMERS. Customers do
not care whether something is intrinsicly internally blah blah blah.
Customers care that the product solves a need. Is it better to be
"right" and broke, or is it better to have happy customers?

I would further suggest this demonstrates views on the whole are not
used in a production environment.

I hate views. They are inefficient, they are slow, and they do not
allow flexibility.

What is a table? I think of it as a set of data. What is a view? A
view is a derived extremely slow set of data.
What does a stored procedure return? Why, it returns very quickly the
specific set of data that I am interested in|||> Is it better to be "right" and broke, or is it better to have happy customers?

Neither - it's better to be right and prosperous, and have happy broke
customers ;)|||Doug wrote: Another solution is to go back to SQL Server 2000.

Not merited by this little hiccup.

Doug wrote: In Access, can you ask "Access" to return your result sets
in order?

Absolutely. We already have hundreds of queries in Access, and about 60
places in code where we are setting SQL up for lists. Dumping the Order
By spec in 31 views was a minor nuisance, not much more. Because Access
is sufficiently object oriented to use it on itself, I wrote some code
to look through query SQL, form recordsources, and list control
rowsources to find any reference to one of the affected views without an
accompanying Order By.

Not so terribly painful, just hacked me off that something I know is
used, documented or not, would be dropped without more noise.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||Doug wrote:
> I will submit that we have indeed found a "bug." If order by is not
> "legal" for a view, then why does it compile, and not raise an error
> when you save it off?

ORDER BY is valid in views only when used in conjunction with TOP. Its
purpose in a view or derived table is solely to filter the set of rows
selected with the TOP clause. It does NOT determine the order of rows
returned from a query issued against that view. So in fact ORDER BY is
doing exactly what it is supposed to do and there is no bug.

Microsoft's choice of syntax for the TOP modifier is certainly poor. It
is confusing that ORDER BY serves two purposes. That problem is
remedied somewhat in SQL Server 2005 by the addition of the SQL OLAP
functions that have their own dedicated ORDER BY clause. So bad design,
yes. Bug, no.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>ORDER BY is valid in views only when used in conjunction with TOP

but if i have an order by in a view, without the TOP, it compiles.

Therefore, it is a bug.

As an example, If I use "sum" without a group by phrase, it won't
compile.|||>> I hate views. They are inefficient, they are slow, and they do not allow flexibility. <<

Actually they can be VERY efficient in most products. If the text of
the VIEW definition is copied as an "in-line macro", it works just like
a CTE or derived table. That is rather fast.

If the VIEW definition is materialized, several products will share
that materialized table in cache. Oracle has this feature, for
example.|||Doug wrote:
> >ORDER BY is valid in views only when used in conjunction with TOP
> but if i have an order by in a view, without the TOP, it compiles.

That would indeed be a bug but I'm not aware of it. Can you post some
code to reproduce that behaviour and state what version, edition and
service pack you are using? What you should see is an error message.
Following example tested on Dev Ed. SQL Server 2000 - 8.00.2039 SP4:

CREATE TABLE T1 (x INT);
GO
CREATE VIEW v1 AS SELECT x FROM T1 ORDER BY x
GO

Result:

Server: Msg 1033, Level 15, State 1, Procedure v1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||"RickW" <user=wannall at=@. other=sbcglobal final=.net> wrote in message
news:o_7Pf.71$TQ2.7262@.news.uswest.net...
> Ignoring the rude, ego-inflated hot air that blasts off the page, I
> reply with a few details.
>
> Let me think...Wouldn't I love to hire someone who loves to insult
> people, who writes like an angry teenager, and who overrates his own

Damn that's funny. Joe didn't even take out the big guns to insult you and
you are crying. Generally speaking, if you want to ask an SQL question,
listen to Joe. If you want to feel good about yourself, ask some else
offline. Joe and a few others have been INCREDIBLY helpful in finding SQL
solutions. Just check your ego at the door. Besides, Joe is a teddy bear
if you meet him in person and pretty funny if you get him drunk.

> understanding and abilities with every pronouncement? And who wraps
> exactly one informative sentence ("a VIEW is a table and **by
> definition** has no ordering") in 10 ranting insults? Hmmmm. Tough
> call.
> Come to think of it, I'm curious: Why exactly did you bother to reply?
> Did you accomplish what you intended? What exactly was that?
>
>
> Science is organized common sense where many a beautiful theory was
> killed by an ugly fact. -- Thomas Huxley
> *** Sent via Developersdex http://www.developersdex.com ***|||Thanks to everyone who posted to this very informative thread. I agree
with RickW that this is a very annoying change in SQL Server's behavior.
Microsoft has a reputation for being very careful of protecting
backwards compatibility by not changing software behavior that
developers have come to rely on, whether that behavior was documented or
not. At the very least this should be included in the migration white
paper as an issue to look out for.

To the SQL theorists and purists: SQL Server is a tool that is being
effectively used by a lot of people for a lot of different purposes.
Some of these people aren't Ph.D.s; some of them have (gasp) no formal
training at all. Not every organization has a professional DBMA to do
database architecture for them, and they shouldn't be required to.

Take me: I am a web developer. I use SQL server to store content for
small websites. I'm 100% self-taught, and my company is in no danger of
failing as a result, thank you very much. I don't give a rat's ass about
query optimization or performance; CPU cycles are cheap and my datasets
are small. I want to push my grouping/selecting/ordering logic into SQL
as much as possible; so for instance I might create a base view that
selects out content that is approved to display on the website and puts
it in the correct order, and then create other views that select subsets
of that view for specific pages, perhaps performing joins or what have
you along the way. This way the select & sort logic lives in one place,
and if it changes I only have to change it once. This might turn Mr.
Celko's hair green because it won't scale to a dataset with ten million
records, or because it violates some aspect of relational set theory
that he learned in RDBMS class - but it worked fine for my purposes.

Now, because ivory-tower purists like Celko apparently won an internal
debate at Microsoft, I'm going to have to either (1) take the same
sorting logic and reproduce it fifteen different places, because my SQL
code can't be relied on to return ordered data; or (2) rewrite the .NET
objects that talk to my database so that they use stored procedures
instead of views. Terrific. You guys win.

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> Now, because ivory-tower purists like Celko apparently won an internal
> debate at Microsoft, I'm going to have to either (1) take the same
> sorting logic and reproduce it fifteen different places, because my SQL
> code can't be relied on to return ordered data; or (2) rewrite the .NET
> objects that talk to my database so that they use stored procedures
> instead of views. Terrific. You guys win.

I doubt that there was any internal debate about purism. This is simply
about improving the product. SQL Server 2005 adds enhancements to the
optimizer that allow it to make better query plans so that it can run
queries more efficiently. As it happens those enhancements have the
effect that some queries without ORDER BY may be sorted differently
relative to 2000 in some cases.

You could argue that MS should have included the ability to disable
those enhancements and revert to the old engine behaviour but that
could be a very hard thing to accomplish. How is Microsoft supposed to
know exactly which undefined row orderings were important to developers
in past versions? Guaranteeing unchanged row order in every case would
be a practical impossibility I suspect.

So Microsoft listens to its customers when deciding where to invest in
product development. Most of Microsoft's customers DO demand more
performance and better scalability from each new release. Many of those
customers wouldn't want to see other improvements compromised in an
effort to second-guess developers by making some previously unreliable
behaviour into a new product feature.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Herb Caudill (herb@.caudillweb.com) writes:
> Take me: I am a web developer. I use SQL server to store content for
> small websites. I'm 100% self-taught, and my company is in no danger of
> failing as a result, thank you very much. I don't give a rat's ass about
> query optimization or performance; CPU cycles are cheap and my datasets
> are small.

I can assure you that very many users of SQL Server do care about
performance.

Being 100% self-taught is admireable, not the least because it includes
learning some things the hard way, even the basics. As for instance that the
only way to get a certain order out of a query is to use ORDER BY in it.
I'm sorry, but that is way SQL works, not only SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Of course lots of people care about performance, and they should. My
point is that performance is not the only thing that matters, something
that seems lost on many contributors to this newsgroup. If you have a
one huge database and lots of developers, it makes sense to focus on
performance. If you have lots of small databases and just a couple of
developers, which is my situation, it makes better business sense to
focus on developer productivity instead. For me database performance and
scalability are simply non-issues. On the other hand, code reuse,
encapsulation of logic, and things like that are absolutely paramount.
(Here's a horrifying example for you: We deliberately use SELECT * where
possible, because it means that we can add a field to an application and
only have to touch our code in three places, instead of twenty.)

In the past Microsoft has been very good about balancing performance
against developer productivity, and about preserving backward
compatibility as well. There are a number of solutions that they could
have come up with here that would have been preferable to breaking
existing applications that depended on an application's previous
behavior, even when that behavior was undocumented.

The fact of the matter is that SQL server used to work the way most
nave users would expect it to work - that is, views and functions with
an ORDER BY clause returned ordered results. This worked 100% of the
time in my experience, and I depended on it. The fact that there was a
footnote in an RFC or a technical specification somewhere the explained
that this was not guaranteed is beside the point; as far as I'm
concerned this is a breach of backward compatibility, and your
explanation that the new approach gives me better query optimization
does not interest me at all.

Even if it did, as RickW has pointed out, it makes no sense - even from
a theoretical perspective - that SQL would lack an intrinsic object able
to return ordered data. If a stored procedure can, then why can't a view
or a function? Most intelligent programmers approaching SQL for the
first time would assume that ordering of data is a service that a
database language should be able to provide - indeed, one that is best
left to the database as opposed to the client code - and that this
service could be incorporated into objects (views/functions/procedures)
that can in turn be used by other objects. I understand the performance
issues you raise, but there are any number of ways that Microsoft could
have pleased me *and* the massive-scalability crowd: whether with more
intelligent and flexible optimization, or using a global switch, or a
specifier that could be added to a view's definition - or simply by
favoring performance if there is no ORDER BY clause present, and
respecting the ORDER BY clause if it is there. As they say, it's a
simple matter of programming. I think this is a rare instance of simple
laziness on Microsoft's part.

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> The fact of the matter is that SQL server used to work the way most
> nave users would expect it to work - that is, views and functions with
> an ORDER BY clause returned ordered results. This worked 100% of the
> time in my experience

In that case your experience is very limited. It does not work anything
like 100% of the time and it never did.

> Even if it did, as RickW has pointed out, it makes no sense - even from
> a theoretical perspective - that SQL would lack an intrinsic object able
> to return ordered data. If a stored procedure can, then why can't a view
> or a function?

You didn't read this thread very well. A view or a function does NOT
return any data so ordering is not something that it can control. It is
queries against the view or function that return data and those queries
determine the order of the results returned. Those queries might reside
in a stored procedure or in client code but in EVERY case it is the
execution plan for the query that determines the order of rows
returned, not the view(s) that are referenced.

> issues you raise, but there are any number of ways that Microsoft could
> have pleased me *and* the massive-scalability crowd: whether with more
> intelligent and flexible optimization, or using a global switch, or a
> specifier that could be added to a view's definition - or simply by
> favoring performance if there is no ORDER BY clause present, and
> respecting the ORDER BY clause if it is there. As they say, it's a
> simple matter of programming. I think this is a rare instance of simple
> laziness on Microsoft's part.

It is not a simple matter at all. The problem is that this behaviour is
totally undefined. I suggest it would have been an almost
insurmountable challenge for Microsoft to reproduce every scenario on
SQL Server 2000, determine what the row order might be and then ensure
that order could be reproduced in 2005. Even if you limited the
requirement to a small number of possible scenarios I don't think there
would be any customer enthusiasm for time spent on such a thing at the
expense of other features. Of course you are welcome to suggest
potential new features for future versions.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>> You didn't read this thread very well. A view or a function does NOT
return any data <<

Whatever. That seems to me a very abtruse point to make in response to a
practical question.

Maybe the problem here is that I want SQL to be something more than it
is. As an object-oriented programmer, I expect to be able to encapsulate
logic at the lowest possible level. In this situation, "logic" includes
sorting logic. The conclusion on this thread is that there's no way to
do that - that in a chain of nested views/functions, the sorting logic
has to take place at the very last minute - which means that it has to
be reproduced and maintained in many different places. That's
unsatisfactory.

A similar problem having to do with encapsulation - perhaps a topic for
another thread - is that I can't write a generic function to perform
consistent operations against multiple tables. For example: Most of my
applications have a number of tables that are all subject to the same
versioning and approval logic, which is a little complex. A table-valued
function like this would be very useful to me:

Create Function GetApprovedItems(TableName as varchar(50)) returns Table

Of course, I can't do that because it would require building and
executing a command that contains the name of the table, and you can't
have an EXEC statement in a function. I could do something like this in
a stored procedure, but I cant perform any selects or joins on a stored
procedure, so that does me no good. I'm sure all you relational data
theorists can all jump in right now and lecture me about why functions
can't include non-deterministic blah-blah-blah, but I don't care. The
point is that I don't have any good options. Here they are:

1. Write the same function many times, just varying the table names
(GetApprovedArticles, GetApprovedProjects, etc). This is my current
solution, but it's unsatisfactory. If I need to tweak the approval
logic, I then have to do it in several different places.

2. Build the SQL functions programmatically within my application. This
is an approach I considered, but it makes things complicated in other
ways.

3. Push all of this logic to the application (e.g. pull in raw data and
perform all the selecting and sorting using business objects in C# or
VB). This has two problems: (a) I lose all of the SQLs power and
elegance for selecting subsets, joining related tables, and so on; and
(b) I lose a lot of flexibility in terms of adapting my framework to new
data architectures. As it is, adding a new field or even a new table
for holding website content is relatively easy. Adding a whole new
layer of plumbing to maintain would eliminate that flexibility. I had
just one big, stable database to worry about, this is probably the
approach I would take; but the strength of my website framework is that
I can easily adapt it to a new customers information structure with
relatively little work, which keeps prices reasonable for my customers
and gives me a viable business model.

Is it unreasonable of me to think that at this late date, we ought to
have a superset of SQL that provides some of the benefits of an
object-oriented approach (abstraction, encapsulation etc.)? Or is that
just crazy talk?

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> Maybe the problem here is that I want SQL to be something more than it
> is. As an object-oriented programmer, I expect to be able to encapsulate
> logic at the lowest possible level. In this situation, "logic" includes
> sorting logic. The conclusion on this thread is that there's no way to
> do that - that in a chain of nested views/functions, the sorting logic
> has to take place at the very last minute - which means that it has to
> be reproduced and maintained in many different places. That's
> unsatisfactory.
> A similar problem having to do with encapsulation - perhaps a topic for
> another thread - is that I can't write a generic function to perform
> consistent operations against multiple tables. For example: Most of my
> applications have a number of tables that are all subject to the same
> versioning and approval logic, which is a little complex. A table-valued
> function like this would be very useful to me:
> Create Function GetApprovedItems(TableName as varchar(50)) returns Table

Why would you want to parameterize the table name in this case? I can't
be sure without seeing your table structure but one mistake commonly
made by OO programmers is to use tables like an object model - as
containers for distinct subsets of the same type of data. That gives
poor results in the relational model.

The relevant design principle is that of Orthogonal Design. Loosely
speaking we sometimes say that no two tables should share the same key
and and non-key attributes. If you follow that principle then I don't
know why you would want the same approvals process to apply to two
tables. The tables should not "overlap" so there should be no processes
that they have in common. Maybe what's missing from your model is
another table called "Approvals" - but there I'm just guessing.

Every software development discipline has its own design patterns and
practices. In the case of the relational model we are fortunate that
these are very well developed and documented. If you follow the right
design pattern then SQL should have all you need to encapsulate logic
and enable code re-use. To implement an inappropriate design and then
say that it demonstrates deficiencies of the underlying model isn't a
productive course to take whether in SQL or in an OO language or in any
other environment.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Herb Caudill (herb@.caudillweb.com) writes:
> Maybe the problem here is that I want SQL to be something more than it
> is. As an object-oriented programmer, I expect to be able to encapsulate
> logic at the lowest possible level.

An error which is not uncommon to make when you have a very good hammer,
is to also use it to draw screws.

The object-oriented model is very elegant, and has its benefits.

But a relational database is not in Kansas, and applying too much O-O
thinking in a relational database is going to lead you into problems.
Not because O-O is inferior or relational is better, but just because it's
different. Object-oriented databases have seen the light of day, but they
never took off, for reasons unknown to me. The reason relational databases
have become so dominating is that they have proven to be able to handle
huge volumes of data with good performance to reasonable development costs.

> A similar problem having to do with encapsulation - perhaps a topic for
> another thread - is that I can't write a generic function to perform
> consistent operations against multiple tables. For example: Most of my
> applications have a number of tables that are all subject to the same
> versioning and approval logic, which is a little complex. A table-valued
> function like this would be very useful to me:
> Create Function GetApprovedItems(TableName as varchar(50)) returns Table

I don't know about your databases, but usually when people want to
parameterise on the table name, there is a design flaw. Many tables should
really have been one table, with one more key column.

It's important to understand that in a relational database, tables are
very unique entities. If you have two functions that have exactly the
same code, save the table name, that does not mean that they will execute
the same. In fact, even if the table name is the same, but the databases
are different, the query plan will be different.

Here is a very important difference to traditional programming, including
O-O. Here the program code is a very detailed instruction on what operations
to do, in which order to access data etc. There is an optimizer, but it
mainly manages smaller parts.

But in SQL you basically only describe what result you want, the optimizer
will find out the best way to compute that result. All tables in SQL Server
has statistics, and the statistics are unique to the table, because the
statistics describe the data in table, and different statistics gives
different query plans.

An implementation or table-name parameters would certainly lead to that
the same module could have multiple query plans, and it could be quite
confusing for pprogrammer that do not understand this.

Again, while you don't care about performance, most users of SQL Server do,
and focus on performance will always be very stronng in SQL Server.

> can't include non-deterministic blah-blah-blah, but I don't care. The
> point is that I don't have any good options. Here they are:

There is one more that you did not mention: you can use a pre-processor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

SQL provides data to applications. This guy is saying SQL could do a
better job of it.|||Herb Caudill (herb@.caudillweb.com) writes:
> The fact of the matter is that SQL server used to work the way most
> nave users would expect it to work - that is, views and functions with
> an ORDER BY clause returned ordered results. This worked 100% of the
> time in my experience, and I depended on it. The fact that there was a
> footnote in an RFC or a technical specification somewhere the explained
> that this was not guaranteed is beside the point; as far as I'm
> concerned this is a breach of backward compatibility, and your
> explanation that the new approach gives me better query optimization
> does not interest me at all.

Permit me to point that it is a matter of a "footnote", but a very
basic property of SQL. queries return tables, and tables are unordered
sets. If you need a certain order from a query, you need to use ORDER BY.
This apply to all RDBMS products.

This is no stranger that in a C program you should initialize all your
variables, although you in some cases can assume that will have a certain
value.

By the wau, I would like to express the hope that you in some few years
will start to care about performance. Because that would mean that your
business has taken off.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland wrote <If you need a certain order from a query, you need to use
ORDER BY.
We all know that you need to use ORDER BY. We just thought it should be
available in a query stored as a view in SQL Server.

By the way, across 8 years of SQL Server 7/2000 use, I never had an
instance of using ORDER BY in a view that failed to return an ordered
record set.

Obviously I need someone to explain why, if it's okay to use ORDER BY in
a query to get an ordered data set, it's not okay to store that query as
a view in a tool like SQL Server and see that ordered result in my
application, without having to write a query from the application.

I agree with Herb. It was lazy of Microsoft to omit a solution to this.
I'm lucky. I have one application of 60K lines of VB, and only about 30
views relied on ORDER BY to present ordered data to the application.
Moving the ORDER BY to the application side was not a big deal for our
app. I can sure see how a lot of developers could be in a much worse
situation.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW wrote:
> By the way, across 8 years of SQL Server 7/2000 use, I never had an
> instance of using ORDER BY in a view that failed to return an ordered
> record set.
> Obviously I need someone to explain why, if it's okay to use ORDER BY in
> a query to get an ordered data set, it's not okay to store that query as
> a view in a tool like SQL Server and see that ordered result in my
> application, without having to write a query from the application.

This has been answered several times now. It is the QUERY that you make
against the view that determines the order of the result, not the view
itself. Views cannot possibly "fail to return an ordered record set"
because views don't return anything at all. The only way to return data
from a view is to query the view:

SELECT ...
FROM your_view
WHERE ... etc ;

If that query has an ORDER BY then the result is sorted accordingly. If
it doesn't then the order is undefined. This has been the case in all
versions of SQL Server.

You mentioned that you are using Access as a front end. If you linked
the views in an Access database then Access will generate a query for
you. I don't know whether Access gives you the option to specify the
order or not. Perhaps it doesn't or maybe that option is poorly
implemented or maybe you just didn't know it was there. I expect Access
just does "SELECT * FROM your_view ;" by default. In any case, SQL
Server will do exactly what is specified by the query generated by
Access.

As I tried to show with the example I posted in reply to Serge, it
isn't always possible to define a sensible "natural" order for a query
against a view. That's why your expectations that queries against views
should always be sorted are unreasonable unless you want to disallow
certain types of queries.

If you want to propose a new feature that views should specify sorts
for *sone* queries then someone will have to create a whole
specification for which queries are sorted, which aren't and what
errors or warnings are generated for the ones that aren't. The
consequences for the user or developer are terrible. Either the
developer has to cope with his old queries now generating error
messages (even if he didn't want them ordered). Or alternatively, you
don't issue any error messages for queries that can't be sorted and the
developer has to read the documentation to work out which ones will be
sorted and which won't (in other words RTFM - no different to the
situation we have today in fact!)

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||RickW (user=wannall at=@. other=sbcglobal final=.net) writes:
> Obviously I need someone to explain why, if it's okay to use ORDER BY in
> a query to get an ordered data set, it's not okay to store that query as
> a view in a tool like SQL Server and see that ordered result in my
> application, without having to write a query from the application.

A view is not a query. If you want stored queries, use stored procedures.
The purpose of a view is gather information from one or more tables, to
make it easier for end users to work with the data, or to hide information
they are not supposed to see. But it is still an unordered set of
information.

> I agree with Herb. It was lazy of Microsoft to omit a solution to this.

There are stored procedures, and thus there is a solution.

What was a mistake was to supply a syntax that lead you to believe that
you could do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am absolutely fascinated by the complete lack of ability of the
theory-side respondents to differentiate between a thing in SQL Server
that accidentally has the object name "view", and the theoretical
construct defined by the term <VIEW>.

Amusingly, the accidental fact that there doesn't happen to be a
contravening definition of a construct named <STORED PROCEDURE> causes
them to accept without noticing any contradiction that it therefore
makes sense for an object in SQL Server with object name "stored
procedure" to return an ordered set.

Theory absolutely rules, right up to that layer where the tool meets the
real world. Right there, at that interface, you start accommodating how
a thing must be used (user interface), by adapting the things that must
work a certain way (underlying mechanisms).

Failure to do so leads to the kind of nonesense that just happened with
ORDER BY.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***|||RickW (user=wannall at=@. other=sbcglobal final=.net) writes:
> I am absolutely fascinated by the complete lack of ability of the
> theory-side respondents to differentiate between a thing in SQL Server
> that accidentally has the object name "view", and the theoretical
> construct defined by the term <VIEW>.
> Amusingly, the accidental fact that there doesn't happen to be a
> contravening definition of a construct named <STORED PROCEDURE> causes
> them to accept without noticing any contradiction that it therefore
> makes sense for an object in SQL Server with object name "stored
> procedure" to return an ordered set.
> Theory absolutely rules, right up to that layer where the tool meets the
> real world. Right there, at that interface, you start accommodating how
> a thing must be used (user interface), by adapting the things that must
> work a certain way (underlying mechanisms).
> Failure to do so leads to the kind of nonesense that just happened with
> ORDER BY.

When you want a query against a view to return data in a order as
defined in a view, you are not asking for a view, you are asking for
a stored query. Which is essentially is a stored procedure.

But, yes, if you insist on that to equat views with stored procedures
it will lead to nonsense.

Each trade has its fundamentals, one fundamental in SQL is that if you
want an order from query, you must use ORDER BY. You can call it theory
if you like. But it's really more like "that's the way it works".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I am absolutely fascinated by the complete lack of ability of the theory-side respondents to differentiate between a thing in SQL Server that accidentally has the object name "view", and the theoretical construct defined by the term <VIEW>. <<

And I am absolutely fascinated by people who how no idea about
foundations and basic terms. "Caesar: Pardon him, Theodotus. He is a
barbarian and thinks the customs of his tribe and island are the laws
of nature." - Caesar and Cleopatra; George Bernard Shaw 1898.

Ghod! Next thing you know, we ANSI/ISO Industry standard Mathematical
types will want 2+2 = 4 and that Humpty Dumpty cannot make up things on
the fly.

>> Amusingly, the accidental fact that there doesn't happen to be a contravening definition of a construct named <STORED PROCEDURE> causes them to accept without noticing any contradiction that it therefore makes sense for an object in SQL Server with object name "stored procedure" to return an ordered set. <<

Actually, the Standards have SQL/PSM for stored procedures and it does
not make "ordered sets" -- whatever the heck that might be.|||>> Microsoft has a reputation for being very careful of protecting backwards compatibility by not changing software behavior that developers have come to rely on, whether that behavior was documented or not. At the very least this should be included in the migration white paper as an issue to look out for. <<

Actually, SQL Server has been pretty about getting in line with
ANSI.ISO Standards -- OUTER JOINs, the ALL() predicate, etc.

No obligation to document on the part of a vendor? I have mixed
feeling about that .. and a lawyer! And a user group!

But any developer who codes based on needless proprietary features is
a an amatuer or a hillbilly who cannot speak the language properly.
Anyone who depends on undocumented proprietary features is a dangerous
moron. And, yes, those bad programmers will have to clean up their
crap in dozens of places. This is how Ghod punishes you for your bad
code.

Or how Satan punishes the next guy for YOUR mistakes when he has to
maintain it. A professional would have written code for the next guy.
The amateur amuses himself.|||>> Of course lots of people care about performance, and they should. My point is that performance is not the only thing that matters, omething that seems lost on many contributors to this newsgroup. <<

I agree!

80%+ of the cost of a system is in maintaining it over its lifetime.
Ergo, following standards so that any programmer who speaks the
language can read and work with the code base is the biggest source of
total improvement. Read any SEI, DoD etc. paper for the last 30 years
about the total cost.

I feel like I am teaching a freshman SE class again ..

>> that SQL server used to work the way most nave users would expect it to work - that is, views and functions with an ORDER BY clause returned ordered results. <<

So, if most people think 2 + 2 = 5 then the accounting package should
change? Why, of course! We want to have the most imcompetent people
writing code and that the programs produce crap, run slow, etc. because
it might hurt their feelings if their invincible ignorance is not made
law.

>> there are any number of ways that Microsoft could have pleased me *and* the massive-scalability crowd: <<

No, not really. If you mean VLDB (with few users -- DW), then the use
of physically contigous storage has frozen SQL Server at a certain
level. You then go to Teradata and SAND and some other products.

If you mean lots of users with a relatively small RDBMS like a
website, then this is another game Much of that work has to be done in
a middle tier, mirrors, etc.

It is not a simple matter of ORDER BY being turned on or off. It is a
matter of parallelism versus sequential processing. We are not talking
about 1-2 orders of magnitude -- there can be at 7-8 orders of
magnitude difference as it tries to preserve ordering.
..|||>>> incompetent people ... ignorance ... amatuer or a hillbilly ...
dangerous moron ... clean up their crap ... This is how Ghod punishes
you for your bad code ... Satan ... etc. <<<

My goodness, Celko. Where is this anger coming from? A little out of
proportion to the subject matter, if you ask me. I'd genuinely like to
learn from you, since you obviously know more than I do, but the
bile-to-wisdom ratio of your posts makes it tempting to just ignore you.
That would be a shame, wouldn't it?

Here's a simple real-life situation; let's see if we can work together,
keep our blood pressure down, and come up with a solution that is both
practical and theoretically pure.

1. You have a SQL table containing website content.
2. 25 different parts of the website display 25 different subsets of
this content.
3. The content on every page should be sorted in exactly the same way.
The sorting logic is relatively complex and subject to change.

Here's how I might have handled this on SQL Server 2000:
1. Create a base view that incorporates the sorting logic: Select top
100 percent ... order by ... (the sound you hear is Celko calling down
divine wrath upon me and my children and my children's children)
2. Create 25 child views to return subsets of the base view.

Of course, now that I've been punished by the almighty for my crimes
against humanity, this is no longer an option. Some suggest moving the
sorting logic to the client - which would require the same exact "order
by" clause 25 places in my .NET code. This is unsatisfactory because I
have to maintain the same logic in 25 different places. Can we do
better?

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> >>> incompetent people ... ignorance ... amatuer or a hillbilly ...
> dangerous moron ... clean up their crap ... This is how Ghod punishes
> you for your bad code ... Satan ... etc. <<<
> My goodness, Celko. Where is this anger coming from? A little out of
> proportion to the subject matter, if you ask me. I'd genuinely like to
> learn from you, since you obviously know more than I do, but the
> bile-to-wisdom ratio of your posts makes it tempting to just ignore you.
> That would be a shame, wouldn't it?
> Here's a simple real-life situation; let's see if we can work together,
> keep our blood pressure down, and come up with a solution that is both
> practical and theoretically pure.
> 1. You have a SQL table containing website content.
> 2. 25 different parts of the website display 25 different subsets of
> this content.
> 3. The content on every page should be sorted in exactly the same way.
> The sorting logic is relatively complex and subject to change.
> Here's how I might have handled this on SQL Server 2000:
> 1. Create a base view that incorporates the sorting logic: Select top
> 100 percent ... order by ... (the sound you hear is Celko calling down
> divine wrath upon me and my children and my children's children)
> 2. Create 25 child views to return subsets of the base view.
> Of course, now that I've been punished by the almighty for my crimes
> against humanity, this is no longer an option. Some suggest moving the
> sorting logic to the client - which would require the same exact "order
> by" clause 25 places in my .NET code. This is unsatisfactory because I
> have to maintain the same logic in 25 different places. Can we do
> better?

Use a stored proc. I don't know why that would be a problem for you.
Best practice is to use procs for ALL data access. Don't ask why procs
can sort and views can't. I've no intention of repeating myself again
:-).

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||But stored procedures are a dead end - you can't refer to them in any
other objects. I can't select subsets of the results of a stored
procedure, I can't join against it, I can't count its results without
using cursors (not in SQL, anyway).

So if I replace my base view with a stored procedure, I can't do
anything further with it in SQL, and the client has to do everything.
The .NET code for each section of the website has to request the entire
set of content items, and then choose the ones it wants. That's no good
for two reasons. One, each request is pulling far more data than it
really needs. Two, SQL is far better at selecting subsets than .NET is.

Surely we can do better.

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> But stored procedures are a dead end - you can't refer to them in any
> other objects. I can't select subsets of the results of a stored
> procedure, I can't join against it, I can't count its results without
> using cursors (not in SQL, anyway).

But if you wanted to do more stuff in SQL then you'd put that in a proc
as well.

> So if I replace my base view with a stored procedure, I can't do
> anything further with it in SQL, and the client has to do everything.
> The .NET code for each section of the website has to request the entire
> set of content items, and then choose the ones it wants. That's no good
> for two reasons. One, each request is pulling far more data than it
> really needs. Two, SQL is far better at selecting subsets than .NET is.

So use parameterized procs that only return exactly what is needed.

There are lots of excellent reasons why we use procs for all things
you've described. Here are some links:

http://msdn.microsoft.com/library/d...tun_1a_6x45.asp
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Herb Caudill (herb@.caudillweb.com) writes:
> But stored procedures are a dead end - you can't refer to them in any
> other objects. I can't select subsets of the results of a stored
> procedure, I can't join against it, I can't count its results without
> using cursors (not in SQL, anyway).
> So if I replace my base view with a stored procedure, I can't do
> anything further with it in SQL, and the client has to do everything.
> The .NET code for each section of the website has to request the entire
> set of content items, and then choose the ones it wants. That's no good
> for two reasons. One, each request is pulling far more data than it
> really needs. Two, SQL is far better at selecting subsets than .NET is.

I can't really get this. If you make your view and then make further
selections from it. What reason do you have to expect that there will
be some inherent sorting into it?

The purpose of TOP n PERCENT ORDER BY in a view is to define a subset
by some criteria - not to specify how the data is created. You've taken
the feature and tied your own definition to it. That usually comes back
and bite you one day.

Maybe you should look into the row_number function. This permits you
to number rows in a query according to some criteria. The final queries
that output data would need to order by this column, but the definition of
the row-number column would have the complex sorting logic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

OK, OK, OK. Got it. Point is it used to work that way and all of us
stupid schlubs who failed to read the fine print took advantage of the
way it worked in practice.

I'm trying to arrive at some sort of best practice now.

>>> Maybe you should look into the row_number function <<<

Thanks - I'll look into this. At first glance, it seems like a very
complicated solution to (what used to be) a very simple problem.

*** Sent via Developersdex http://www.developersdex.com ***|||The problem is that the criteria for what subset to be return might be
simple or complicated - but at any rate it's not just a matter of
passing one or two parameters. For instance, in the past I might have
had:

view v_ArticlesBase -- in SS2000, provided ordered results
procedure GetAllArticlesByProgram(@.ProgramID)
procedure GetRecentArticlesByProgram(@.ProgramID)
procedure GetArticlesByArticle(@.ArticleID) -- related articles
procedure GetArticlesByProject(@.ProjectID)

... and so on. At the very least, the ordering logic needs to be
repeated in each of these stored procedures.

An alternative would be to create a single monstrous procedure that
consolidates the ordering logic but that uses a lot of conditional paths
to provide all of the subsets listed above - but that seems really
inelegant. Some of these are doing simple matches, while others are
having to traverse one or two linked tables to determine if an article
should be included in the subset.

*** Sent via Developersdex http://www.developersdex.com ***|||Herb Caudill wrote:
> The problem is that the criteria for what subset to be return might be
> simple or complicated - but at any rate it's not just a matter of
> passing one or two parameters. For instance, in the past I might have
> had:
> view v_ArticlesBase -- in SS2000, provided ordered results
> procedure GetAllArticlesByProgram(@.ProgramID)
> procedure GetRecentArticlesByProgram(@.ProgramID)
> procedure GetArticlesByArticle(@.ArticleID) -- related articles
> procedure GetArticlesByProject(@.ProjectID)
> .. and so on. At the very least, the ordering logic needs to be
> repeated in each of these stored procedures.
> An alternative would be to create a single monstrous procedure that
> consolidates the ordering logic but that uses a lot of conditional paths
> to provide all of the subsets listed above - but that seems really
> inelegant. Some of these are doing simple matches, while others are
> having to traverse one or two linked tables to determine if an article
> should be included in the subset.

Well if your logic is as varied as you are making it sound then it's
possibly unlikely that the equivalent SELECTs out of a view would
always respect the TOP 100 PERCENT ORDER BY in a view - even in 2000.
Not unless you use SELECT * in every query perhaps, and we know that
SELECT * is bad practice. Contrary to what you believed, the results in
2000 won't always follow the ORDER BY included in a view.

Also, if you are putting all this logic client-side today then it seems
like you must be constructing a lot of dynamic SQL strings. You said
you are a website developer so I hope you are aware of the SQL
injection issue and what precautions you have to take to prevent
injection from user-supplied parameters. In most cases the simplest and
most reliable solution is to use parameterized procs.

--
David Portas, SQL Server MVP

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Herb Caudill (herb@.caudillweb.com) writes:
> OK, OK, OK. Got it. Point is it used to work that way and all of us
> stupid schlubs who failed to read the fine print took advantage of the
> way it worked in practice.

An analogy: you build houses, and you use bricks and stone from some
place, and you think they are really great. Then one day someone tells
you that there is a lot of radon in the material...

> Thanks - I'll look into this. At first glance, it seems like a very
> complicated solution to (what used to be) a very simple problem.

But it has the distinct advantage of being a solution on which you
actually can rely.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Hey I thought the "Satan line" would register with anyone who has hs
to maintain code

Why do you think this is the right tool for websites? Not portable,
never meant to do this, etc.|||Why are you talking about .NET, which has nothing to do with RDBMS?
You cna JOIN agianst VIEWS, not Stored Procedures. You still think in
terms of Objects (which do not exist in set-oriented SQL) and
sequential processes via cursors.

Monday, March 26, 2012

ORDER BY in VIEW not working

No comments:

Post a Comment

Oracle DBA to learn MS SQL

Blog Archive

About Me