<a href="https://bytes.com/topic/sql-server/answers/448922-order-view-not-working">ORDER BY in VIEW not working

Hi,

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" <be*****************@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

Jan 24 '06 #3

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/m...S,SQL.90).aspx
--

Jan 24 '06 #4

Beowulf

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.

Jan 25 '06 #5

--CELKO--

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.

Jan 25 '06 #6

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

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/m...S,SQL.90).aspx
--

Jan 25 '06 #7

Beowulf

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.

Jan 26 '06 #8

Beowulf

--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.

Jan 26 '06 #9

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

Jan 26 '06 #10

Doug

>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.

Jan 30 '06 #11

Doug

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.

Jan 30 '06 #12

--CELKO--

>> 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.

Jan 30 '06 #13

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

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/m...S,SQL.90).aspx
--

Jan 30 '06 #14

> 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--" <jc*******@earthlink.net> wrote in message
news:11**********************@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.

Jan 30 '06 #15

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" <dr*********@hotmail.com> wrote in message
news:11**********************@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.

Jan 30 '06 #16

Doug

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!.

Jan 30 '06 #17

Doug

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.

Jan 30 '06 #18

--CELKO--

>> . 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.

Jan 30 '06 #19

Erland Sommarskog

Doug (dr*********@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, es****@sommarskog.se

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

Jan 30 '06 #20

--CELKO--

>> 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.

Jan 31 '06 #21

Beowulf

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.

Jan 31 '06 #22

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

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/m...S,SQL.90).aspx
--

Jan 31 '06 #23

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--" <jc*******@earthlink.net> wrote in message
news:11*********************@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.

Jan 31 '06 #24

Hugo Kornelis

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

Jan 31 '06 #25

Doug

Hello,

When do you prefer a view?

Thanks!
doug

Feb 1 '06 #26

--CELKO--

>> 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.

Feb 1 '06 #27

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--" <jc*******@earthlink.net> wrote in message
news:11********************@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.

Feb 2 '06 #28

Alexander Kuznetsov

> 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/oracle...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.

Feb 2 '06 #29

Hugo Kornelis

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

Feb 2 '06 #30

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--" <jc*******@earthlink.net> wrote in message
news:11*********************@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.

Feb 3 '06 #31

Doug

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.

Feb 3 '06 #32

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

Feb 3 '06 #33

Alexander Kuznetsov

> 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.

Feb 3 '06 #34

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

Feb 3 '06 #35

--CELKO--

>> 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.

Feb 3 '06 #36

> 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" <sr*****@ca.ibm.com> wrote in message
news:44************@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

Feb 4 '06 #37

Doug

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

Feb 6 '06 #38

Doug

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.

Feb 6 '06 #39

--CELKO--

>> 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.

Feb 7 '06 #40

Doug

>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

Feb 26 '06 #41

RickW

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 ***

Mar 6 '06 #42

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

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/m...S,SQL.90).aspx
--

Mar 6 '06 #43

RickW

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 ***

Mar 6 '06 #44

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

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/m...S,SQL.90).aspx
--

Mar 6 '06 #45

RickW

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 ***

Mar 6 '06 #46

--CELKO--

>> 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.

Mar 7 '06 #47

RickW

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 ***

Mar 7 '06 #48