Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
particular order of data if I in fact query that view via some sql? Is this
correct?
Eg:
Select * from myView
Is my assumption correct? And this is a notable change from sql 2000 in
which the order of a view was respected? Note that I am well aware this
practice in sql 2000 NEVER should have been widely used, but it was (and I
suppose it just been pure luck that views kept their order of data
returned).
I should WELL note that I perfectly understand that a rdbms is a un-ordered
hunk of data. If you need data in some order, you add an order by clause to
the sql request. This is *perfectly* clear, and perfectly normal to me. This
issue of setting order of data comes up a lot in the access newsgroups also.
However, I was NOT aware that a view is ALSO simply considered a table, and
as such an un-order hunk of data also! Does this mean again that the
developer MUST specify the order when retrieving data, *even* from a view,
and EVEN when that view has a order by clause? I also note that views also
"now" require the obligatory top 100 command, and again this requirement
supports the idea that order by in the view does not make sense!
Is the above a correct view (pun intended) that setting a order by clause in
a saved view don't amount to a hill of beans and cannot be relied upon?
I ask the above, because in a lot of vb6 projects that connected to SQL
server 2000, it was very common to use a view as a way to store tons and
tons of SQL statements. In other words this approach was used to simply not
have inline SQL in the application. I should point out that there is a
significant difference in using server views for saving a whole bunch of SQL
statements you hope to execute in the future, as Opposed to that of a
actually needing a real database view. This "view" feature was Obviously
abused in the past.
In many cases we developers simply used those views to store our SQL, and
we're
not really interested in the actual technical details of what a TRUE
database view is supposed to represent. In retrospect, it probably would
have been far more intelligent to develop one's own "sql" data store that
saves the sql for use with code in some data store.
I apologize for the lengthy post, but it seems to me that the conceptual
concept of views used in SQL server 2005 means that we developers should not
use views as a dumping ground in which to save all kinds of SQL select
statements. This was a Common practice in the past, it seems to me now that
this approach should be avoided in the future.
Am I reading this correct? (or can I still risk relying on a view to
return ordered data for me?).
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:eYfl1oIcIHA.1376@.TK2MSFTNGP02.phx.gbl...
>I shudder to bring this issue up, but in very simple terms:
> Saving a "view" in sql 2005 with a order by clause WILL NOT guarantee any
> particular order of data if I in fact query that view via some sql? Is
> this
> correct?
> Eg:
> Select * from myView
>
[snip]
It is correct that the ordering of rows returned by ANY query is undefined
unless that query includes ORDER BY. Obviously the fact that a tabular
result is displayed on the screen means that some kind of ordering is
shown - but you cannot and should not rely on it always being the same. That
is true irrespective of whether the query references tables or views and it
applies equally to all versions: 7.0, 2000, 2005, 2008.
> And this is a notable change from sql 2000 in
> which the order of a view was respected?
It is not a change. There is no such thing as the "order of a view".
> However, I was NOT aware that a view is ALSO simply considered a table,
> and
> as such an un-order hunk of data also! Does this mean again that the
> developer MUST specify the order when retrieving data, *even* from a view,
> and EVEN when that view has a order by clause?
In relational terms a view and a table are one and the same - they are both
relations and relations are unordered sets of tuples.
SQL Server 2000 introduced a very silly syntax for the TOP clause, which
used ORDER BY to specify the criteria used to select a subset of n or n%
rows. Unfortunately this has caused endless confusion and led some people to
assume that ordering of a view was possible - even though Microsoft never
claimed any such thing and even though the concept of an ordered view is
alien to anyone familiar with the fundamentals of SQL or the relational
model.
> I also note that views also
> "now" require the obligatory top 100 command
That is false. There is no such requirement. I expect you are using the view
designer "feature" that does insert TOP 100 PERCENT in some cases.
Personally I would never use the view designer. The fact that it messes with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
There are many previous discussions on this topic, all reiterating much the
same points.
David Portas
|||In addition to David's notes, SQL Server 2005 Books Online has this
summarized very well in one note (under CREATE VIEW):
"The ORDER BY clause is used only to determine the rows that are returned by
the TOP clause in the view definition. The ORDER BY clause does not
guarantee ordered results when the view is queried, unless ORDER BY is also
specified in the query itself."
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> That is false. There is no such requirement. I expect you are using the
> view designer "feature" that does insert TOP 100 PERCENT in some cases.
> Personally I would never use the view designer. The fact that it messes
> with your query syntax and disallows certain valid constructs is reason
> enough to avoid the designer altogether IMO.
Thank you very much for your comments on this matter.
Ouch!! I understand perfectly your respsone, and agree. However, advoid the
desinger is a ouch for me!!!
As I commemned, many a developers relied on those views to return data in
particular order, and as you commented it should never been done that way
(but, it *was* common despite the fact that we should not been doing it this
way).
However the comments about avoiding the designer seems another kind of
difficult pill to swallow. If I am not to use the query designer as crutch
to have "fun" to build quiers by dragging and dropping my fields and
dropping in tables, then what am I supposed to use here? In other words I
love those query designers, and to be honest as a general rule it makes
sense to use tools that do most of the work for me.
I have relied on query builders to do most the work for me in just about a
every development environment that I used sql in (and it been a LOT of
systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this list
is RATHER long).
It just seems to me that building SQL by hand is like going back to
developing web pages by hand coding your HTML. You might tweak the HTML (or
sql) by hand, but you likey should start with some type of development and
building tool here. This is really a cost of human time versus machine
computer time.
I now kind of wonder what direction most developers are taking when they are
developing applications with many many SQL queries, and where do most people
store and place those queries? As I mentioned in the past it was common to
use those views as a storage mechanism -- and I well know it's been
rightfully pointed out that views should never have been used as a
repository for those SQL statements.
However, typing in freeform sql and saving that sql in some text system
makes little sense without some tools to aid in the desing and syntax of
that sql. It is just not productive, and I am not quite sure what direction
I should take now in this regards. (I suppose with visual studio there is
linQ, but that's another matter again).
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
|||Hi Albert
It seems that you code is most likely to have been based on single statement
queries rather than stored procedures that contain multiple statements?
You should find that after a while using the query window or some other tool
that you wonder why you ever used the query builder, expecially as there are
templates and the drag and drop features of the object explorer (where you
can pull database/tables/column names across into the window.
You may want to watch a few of the shorts on jumpstarttv.com such a Brian's
intoduction to management studio
http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos by
Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:u7tNbiOcIHA.1376@.TK2MSFTNGP02.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>
> Thank you very much for your comments on this matter.
> Ouch!! I understand perfectly your respsone, and agree. However, advoid
> the desinger is a ouch for me!!!
> As I commemned, many a developers relied on those views to return data in
> particular order, and as you commented it should never been done that way
> (but, it *was* common despite the fact that we should not been doing it
> this way).
> However the comments about avoiding the designer seems another kind of
> difficult pill to swallow. If I am not to use the query designer as crutch
> to have "fun" to build quiers by dragging and dropping my fields and
> dropping in tables, then what am I supposed to use here? In other words I
> love those query designers, and to be honest as a general rule it makes
> sense to use tools that do most of the work for me.
> I have relied on query builders to do most the work for me in just about a
> every development environment that I used sql in (and it been a LOT of
> systems). (SQL, FoxPro, ms-access, enterprise tools, MySql tools, this
> list is RATHER long).
> It just seems to me that building SQL by hand is like going back to
> developing web pages by hand coding your HTML. You might tweak the HTML
> (or sql) by hand, but you likey should start with some type of development
> and building tool here. This is really a cost of human time versus machine
> computer time.
> I now kind of wonder what direction most developers are taking when they
> are developing applications with many many SQL queries, and where do most
> people store and place those queries? As I mentioned in the past it was
> common to use those views as a storage mechanism -- and I well know it's
> been rightfully pointed out that views should never have been used as a
> repository for those SQL statements.
> However, typing in freeform sql and saving that sql in some text system
> makes little sense without some tools to aid in the desing and syntax of
> that sql. It is just not productive, and I am not quite sure what
> direction I should take now in this regards. (I suppose with visual studio
> there is linQ, but that's another matter again).
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
> Hi Albert
> It seems that you code is most likely to have been based on single
> statement queries rather than stored procedures that contain multiple
> statements?
It can go both ways. However, in the case were we need to execute
several sql things, then a stored proc is the way to go.
However, even then, it would be nice to be able to "use" saved sql
statements that one has designed (with a nice query builder) in those
stored procs (or have the client execute that saved sql).I guess I
looking for "where" do people save that sql code if they want to
keep it out of the client side of the application. A stored proc
is one place, but then you don't get the benefits of query designer
for that sql code created *in* the proc...
> You may want to watch a few of the shorts on jumpstarttv.com such a
> Brian's intoduction to management studio
> http://www.jumpstarttv.com/Media.aspx?vid=58 or more generic T-SQL videos
> by Kathi Kellenberger http://www.jumpstarttv.com/Media.aspx?vid=335
>
nice little video. I quite up to speed with the sql tools now.
I only got on "what tools" bandwagon since the other poster seemed to
suggest that one should not bother using the sql studio tools to create sql.
the quote was:
>Personally I would never use the view designer. The fact that it messes
>with
your query syntax and disallows certain valid constructs is reason enough to
avoid the designer altogether IMO.
So, I not sure if that suggestion was the "widely" accepted practice for
most
sql people to avoid the sql builder or not...
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
|||Hi Albert
By their nature the tools and wizards can only take you to a certain level,
I suspect most of the very experienced DBAs would have worked out their own
methods/templates/scripts etc and would not even think about using the
wizards and query designer!
John
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ONzDYRycIHA.4016@.TK2MSFTNGP03.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uNaFtdVcIHA.3484@.TK2MSFTNGP06.phx.gbl...
> It can go both ways. However, in the case were we need to execute
> several sql things, then a stored proc is the way to go.
> However, even then, it would be nice to be able to "use" saved sql
> statements that one has designed (with a nice query builder) in those
> stored procs (or have the client execute that saved sql).I guess I
> looking for "where" do people save that sql code if they want to
> keep it out of the client side of the application. A stored proc
> is one place, but then you don't get the benefits of query designer
> for that sql code created *in* the proc...
>
> nice little video. I quite up to speed with the sql tools now.
> I only got on "what tools" bandwagon since the other poster seemed to
> suggest that one should not bother using the sql studio tools to create
> sql.
> the quote was:
> your query syntax and disallows certain valid constructs is reason enough
> to
> avoid the designer altogether IMO.
> So, I not sure if that suggestion was the "widely" accepted practice for
> most
> sql people to avoid the sql builder or not...
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
|||if you do this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
ORDER BY RECORDID
what you actually get is this:
CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
AS
SELECT TOP 100 PERCENT *
FROM DBO.TABLE
this means that any ordering has to be done a level above the view
which means millions of applications and setups all over the world
are now broken
for no good reason
and things that should never be part of an application (ie: knowledge
of internal table structure)
now have to be
of course you can use a stored procedure instead
BUT CAN YOU?
what about all those applications that link to a SQL view
like Microsoft Access Linked Tables ?
now you have to create a new Access query that has intimate knowledge
of the internal workings of the remote database !!!!
HOLD ON!
and what if the columns used to order the view are not in the select
clause ?
then the Access query HAS NO WAY of ordering the data correctly unless
you also
REWRITE THE SQL QUERIES
this pointless management-commitee-style decision by Muppetsoft is a
disaster for many applications
there are too many people working at microsoft - a big cull is
necessary before the sickness spreads too far
there is a temporary workaround here:
http://support.microsoft.com/kb/926292
but read the article carefully
|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
> if you do this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> ORDER BY RECORDID
> what you actually get is this:
> CREATE VIEW DBO.MICROSOFT_ARE_DUMBOS
> AS
> SELECT TOP 100 PERCENT *
> FROM DBO.TABLE
> this means that any ordering has to be done a level above the view
Correct.
> which means millions of applications and setups all over the world
> are now broken
Which is why they should have followed the SQL standard from day one.
In addition, I believe Itzak Ben-Gan has an example of where ORDER BY won't
work correctly in SQL 2000 in the first place.
> for no good reason
Other than the fact that by definition tables don't have order and views are
logically the same thing as tables.
> and things that should never be part of an application (ie: knowledge
> of internal table structure)
This makes no more sense then if you were talking about a table itself.
> now have to be
> of course you can use a stored procedure instead
> BUT CAN YOU?
Yes.
> what about all those applications that link to a SQL view
> like Microsoft Access Linked Tables ?
What about them? They should not and never assumed an order.
> now you have to create a new Access query that has intimate knowledge
> of the internal workings of the remote database !!!!
> HOLD ON!
> and what if the columns used to order the view are not in the select
> clause ?
> then the Access query HAS NO WAY of ordering the data correctly unless
> you also
> REWRITE THE SQL QUERIES
So do it right the first time.
> this pointless management-commitee-style decision by Muppetsoft is a
> disaster for many applications
>
This pointless management-commitee-style (sic) decision by Microsoft brings
it into compliance with ISO SQL definitions.
> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
And I suggest some people need to read up on database fundamentals.
>
> there is a temporary workaround here:
> http://support.microsoft.com/kb/926292
> but read the article carefully
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"John Rivers" <first10@.btinternet.com> wrote in message
news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
>.
> there are too many people working at microsoft - a big cull is
> necessary before the sickness spreads too far
>.
Well don't get a culinary
There already was a cull in the sql server group to stop the bleeding. To
MSs chagrin they still must put up with problems in legacy languages
www.beyondsql.blogspot.com
No comments:
Post a Comment