Order By Update Error

Hi Every1,
I'm getting the following error
Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.
what I'm trying to do is to update a table based on the select criteria
I have. In that I'm using ORDER BY Clause & this is giving me error.
Here is my sql:
SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Is it possible to fix this error?
Thanks in advance for your help.Tony,
The query you posted is not an UPDATE statement, View or Function.
I don't see any problems with the query you posted. However, it is not
valid as a view definition, because of the ORDER BY clause, missing
column names for the resultset and maybe more.
In what way to you think that the order is important when updating a
table? What is the actual UPDATE statement you are trying to use?
Gert-Jan
Tony Schplik wrote:
> Hi Every1,
> I'm getting the following error
> Server: Msg 1033, Level 15, State 1, Line 13
> The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.
> what I'm trying to do is to update a table based on the select criteria
> I have. In that I'm using ORDER BY Clause & this is giving me error.
> Here is my sql:
> SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
> from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
> WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
> AND A.EFF_STATUS = 'A'
> AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
> WHERE A.SETID = AX.SETID
> AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
> AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
> GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
> HAVING COUNT(*) > 1
> ORDER BY SUBSTRING(A.DESCR,1,10)
> Is it possible to fix this error?
> Thanks in advance for your help.|||Gert-Jan,
Thanks for your response. Sorry I forgot to put the update statement.
Here it is
UPDATE Name1
SET NAME1 = 'Z' FROM PS_MEMBER_PERSON WHERE NAME1 =(SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)
Even if I take out the order by clause in the update, then it will give
me a different error for the subquery
Do you have any suggestions for that ...
Thanks in advance for your help|||Tony,
Have you looked into the syntax of the UPDATE statement in SQL Server Books
Online?
--
Anithsql

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal ?
code below
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEW
Hi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal ?
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>
|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
sql

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
> >I am creating 3 views from the linked server
> > and then I have a question at the bottom returning results.
> > If I exclude the ORDER BY from my question the query will take about 40
> > seconds,
> > with the ORDER BY it takes about an hour to finish.
> >
> > Is this normal '
> >
> > code below
> > --
> >
> > create view xxxyyy1 as
> > SELECT INSATSID, STATUS
> > FROM HACTAR..EKOP2.INSATS
> >
> > go
> >
> > create view xxxyyy2 as
> > SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> >
> > go
> >
> > create view xxxyyy3 as
> > select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> > 'INK905_STATUS'
> > from xxxyyy1 A left outer join xxxyyy2 B
> > on a.insatsid = b.insats
> > where a.status <> b.status
> > go
> >
> > SELECT
> > A.INSATSID,
> > A.STATUS_OLD,
> > A.STATUS_NEW,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > B.INSATSID,
> > B.STATUS,
> > B.INK905_INSATS,
> > B.INK905_STATUS
> > FROM HACTAR..EKOP2.INSATSLOG A
> > right outer join xxxyyy3 B
> > on A.INSATSID = B.INSATSID
> > WHERE A.ANDR_DATUM < 20060225
> > ORDER BY
> > A.INSATSID,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > A.STATUS_OLD,
> > A.STATUS_NEW
> >
>
>

ORDER BY in views - difference in 2005 vs 2000?

I am no SQL wizard, so if I have a flawed basic understanding please be
blunt so I will understand...
In 2000, we set up a lot of views with ORDER BY to get records in an
expected order. If I had ViewA and it contained an order by for Field1, then
if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
sorted by Field1. In SQL Server 2005, it does not. If I get the definition
of the veiew and submit that as SQL, it works, but that seems like a
rediculous workaround as it costs an extra round trip and bypasses
precompilation. The documentation says "ORDER BY guarantees a sorted result
only for the outermost SELECT statement of a query."
Any help or suggestions are appreciated."Andy Barnhart" <andyDOTbarnhart@.ciincDOTcom> wrote in message
news:OyEX3Vc9FHA.1224@.TK2MSFTNGP12.phx.gbl...
> I am no SQL wizard, so if I have a flawed basic understanding please be
> blunt so I will understand...
> In 2000, we set up a lot of views with ORDER BY to get records in an
> expected order. If I had ViewA and it contained an order by for Field1,
> then if I did "SELECT * from ViewA" in SQL Server 2000, the records came
> back sorted by Field1. In SQL Server 2005, it does not. If I get the
> definition of the veiew and submit that as SQL, it works, but that seems
> like a rediculous workaround as it costs an extra round trip and bypasses
> precompilation. The documentation says "ORDER BY guarantees a sorted
> result only for the outermost SELECT statement of a query."
> Any help or suggestions are appreciated.
There have been a few posts lately on this.
Seems that SQL server has never guaranteed the order when using ORDER BY in
a view.
This works: SELECT TOP 5 ... FROM table ORDER BY
SQL guarantees that the correct TOP 5 will be returned, but does not
guarantee the order.
There was not an issue with SQL 2000 but there is with 2005.
The only suggestion that I can give you is: stop using ORDER BY in views
unless you want something like my example.
You should NEVER has used ORDER BY in views in the way you described...
sorry.
You should create a view with no ordering and then: Select ... from yourView
ORDER BY ...|||Andy Barnhart wrote:
> if I did "SELECT * from ViewA" in SQL Server 2000, the records came back
> sorted by Field1.
Maybe, maybe not. The resulting order of a SELECT statement without an
ORDER BY clause is always undefined - it's dangerous to assume any
predictable ordering.

> The documentation says "ORDER BY guarantees a sorted result
> only for the outermost SELECT statement of a query."
>
That's correct.

> Any help or suggestions are appreciated.
Try:
SELECT * FROM ViewA ORDER BY col
which works equally well in SQL Server 2000 and 2005. :-)
David Portas
SQL Server MVP
--

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde84
1.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:

> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be th
e
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde
841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
>|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
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
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/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
Regards
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentati
on
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause
.
> Cheers, Peter
> "John Bell" wrote:
>|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDE
R
> BY in a view definition"
> John
> "Peter Jones" wrote:
>|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
[vbcol=seagreen]
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
>|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
[vbcol=seagreen]
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the co
de
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
>|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905
-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...[vbcol=seagreen]
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>

ORDER BY in view does not execute in SQL Server 2005

Hi,
I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
which was converted to: "Select Top (100) Percent..." when I recreated my
views.
However any ORDER BY's in my views are not executed until I remove the
keyword Percent, e.g. Select Top (100)... works fine,
Is this a known difference between 2000 and 2005. If so is there a list of
these differences?
PeterHi Peter
Have you checked the backward compatibility sections in books online
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
John
"Peter Jones" wrote:
> Hi,
> I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> which was converted to: "Select Top (100) Percent..." when I recreated my
> views.
> However any ORDER BY's in my views are not executed until I remove the
> keyword Percent, e.g. Select Top (100)... works fine,
> Is this a known difference between 2000 and 2005. If so is there a list of
> these differences?
> Peter
>|||Hello John,
Thanks - I hadn't done that but now that I have I haven't found anything. I
did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
same from 2000 to 2005. There is certainly nothing in the 2005 documentation
that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> Have you checked the backward compatibility sections in books online?
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> John
>
> "Peter Jones" wrote:
> > Hi,
> >
> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > which was converted to: "Select Top (100) Percent..." when I recreated my
> > views.
> >
> > However any ORDER BY's in my views are not executed until I remove the
> > keyword Percent, e.g. Select Top (100)... works fine,
> >
> > Is this a known difference between 2000 and 2005. If so is there a list of
> > these differences?
> >
> > Peter
> >|||Peter Jones wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
>
Views are not ordered in 2000 or in 2005 so nothing changed. Sort order
is determined by the queries that are issued against the view, not by
the view definition itself. If you query the view using a SELECT
statement that doesn't include ORDER BY then the ordering of the result
is undefined.
The reason ORDER BY is permitted in views at all is to support the TOP
operator, which uses ORDER BY to select a subset of rows not to order
the view (yes, I agree that the TOP syntax is not a very user-friendly
one).
Here's the example given in Books Online:
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
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/ms130214(en-US,SQL.90).aspx
--|||Hi Peter,
a known issue about Order By in a view in SQL 2005 is that theres is no
guarantee for the ordering in a view even though you are using selct top
100. Well it is kind of odd but actually it wasn't guaranteed in SQL 2K
either. But it has always worked. Now in SQl 2005 it doesn't work anymore.
Confused ? Well it's like when MS is stating that you should avoid the
direct use of the system tables because it not guaranteed to work in future
version but it worked fine for a lot of version. But suddenly they changed
it. But they will state that they warned us :) So I guess this is the same
regarding views and order by. Only way to solve it is to sort the output
from the view :
Select col1, col2 from view
order by col1
Maby this will help :
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Regards :)
Bobby Henningsen
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:5C2EFE1A-6D04-4604-AB3E-4D8EA11F86EB@.microsoft.com...
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything.
> I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be
> the
> same from 2000 to 2005. There is certainly nothing in the 2005
> documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY
> clause.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> Have you checked the backward compatibility sections in books online?
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> John
>>
>> "Peter Jones" wrote:
>> > Hi,
>> >
>> > I've noticed that ORDER BY in views does not work in SQL Server 2005 as
>> > it
>> > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > Percent...."
>> > which was converted to: "Select Top (100) Percent..." when I recreated
>> > my
>> > views.
>> >
>> > However any ORDER BY's in my views are not executed until I remove the
>> > keyword Percent, e.g. Select Top (100)... works fine,
>> >
>> > Is this a known difference between 2000 and 2005. If so is there a list
>> > of
>> > these differences?
>> >
>> > Peter
>> >|||Hi Peter
The issue David and Bobby refer to is documented at "Breaking Changes to
Database Engine Features in SQL Server 2005"
http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
BY in a view definition"
John
"Peter Jones" wrote:
> Hello John,
> Thanks - I hadn't done that but now that I have I haven't found anything. I
> did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > Have you checked the backward compatibility sections in books online?
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> >
> > John
> >
> >
> > "Peter Jones" wrote:
> >
> > > Hi,
> > >
> > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > views.
> > >
> > > However any ORDER BY's in my views are not executed until I remove the
> > > keyword Percent, e.g. Select Top (100)... works fine,
> > >
> > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > these differences?
> > >
> > > Peter
> > >|||Gentlemen,
Thanks for your input. All is clear although it is perculiar that removing
the keyword PERCENT made things work.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> The issue David and Bobby refer to is documented at "Breaking Changes to
> Database Engine Features in SQL Server 2005"
> http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> BY in a view definition"
> John
> "Peter Jones" wrote:
> > Hello John,
> >
> > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > Have you checked the backward compatibility sections in books online?
> > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > >
> > > John
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hi,
> > > >
> > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > views.
> > > >
> > > > However any ORDER BY's in my views are not executed until I remove the
> > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > >
> > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > these differences?
> > > >
> > > > Peter
> > > >|||Hi Peter
I am not sure why it put that in for you, I prefer to recompile all the code
(stored procedure/functions/views) from source when doing an upgrade which
have avoided this. You may want to consider using a version control system
and using that as the source of the code.
John
"Peter Jones" wrote:
> Gentlemen,
> Thanks for your input. All is clear although it is perculiar that removing
> the keyword PERCENT made things work.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > The issue David and Bobby refer to is documented at "Breaking Changes to
> > Database Engine Features in SQL Server 2005"
> > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > BY in a view definition"
> >
> > John
> >
> > "Peter Jones" wrote:
> >
> > > Hello John,
> > >
> > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > Have you checked the backward compatibility sections in books online?
> > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > >
> > > > John
> > > >
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > views.
> > > > >
> > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > >
> > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > these differences?
> > > > >
> > > > > Peter
> > > > >|||Hi John,
No - the conversion was fine - my original code had "Top 100 Percent"
already there. It was my understanding (in SQL Server 2000) that an ORDER BY
would not work without it.
My comment related to the fact that when the keyword "Percent" was removed
the ORDER BY worked in SQL Server 2005.
Cheers, Peter
"John Bell" wrote:
> Hi Peter
> I am not sure why it put that in for you, I prefer to recompile all the code
> (stored procedure/functions/views) from source when doing an upgrade which
> have avoided this. You may want to consider using a version control system
> and using that as the source of the code.
> John
>
> "Peter Jones" wrote:
> >
> > Gentlemen,
> >
> > Thanks for your input. All is clear although it is perculiar that removing
> > the keyword PERCENT made things work.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > Database Engine Features in SQL Server 2005"
> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > BY in a view definition"
> > >
> > > John
> > >
> > > "Peter Jones" wrote:
> > >
> > > > Hello John,
> > > >
> > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > Have you checked the backward compatibility sections in books online?
> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > >
> > > > > John
> > > > >
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > views.
> > > > > >
> > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > >
> > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > these differences?
> > > > > >
> > > > > > Peter
> > > > > >|||> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
Peter,
Please be aware that it is dangerous to rely on this ordering behavior. It
is unpredictable and your code may break in future service packs or
releases, as it did from SQL 2000 to SQL 2005. SQL Server is free to return
results in any sequence unless ORDER BY is specified in the *query that
selects from the view*.
Here's the relevant info from the SQL 2005 Books online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm">
Note:
When ORDER BY is used in the definition of a view, inline function, derived
table, or subquery, the clause is used only to determine the rows returned
by the TOP clause. The ORDER BY clause does not guarantee ordered results
when these constructs are queried, unless ORDER BY is also specified in the
query itself.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:3EF77828-3DE9-4E8A-B757-A46AF7505343@.microsoft.com...
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER
> BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
>> Hi Peter
>> I am not sure why it put that in for you, I prefer to recompile all the
>> code
>> (stored procedure/functions/views) from source when doing an upgrade
>> which
>> have avoided this. You may want to consider using a version control
>> system
>> and using that as the source of the code.
>> John
>>
>> "Peter Jones" wrote:
>> >
>> > Gentlemen,
>> >
>> > Thanks for your input. All is clear although it is perculiar that
>> > removing
>> > the keyword PERCENT made things work.
>> >
>> > Cheers, Peter
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi Peter
>> > >
>> > > The issue David and Bobby refer to is documented at "Breaking Changes
>> > > to
>> > > Database Engine Features in SQL Server 2005"
>> > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under
>> > > "ORDER
>> > > BY in a view definition"
>> > >
>> > > John
>> > >
>> > > "Peter Jones" wrote:
>> > >
>> > > > Hello John,
>> > > >
>> > > > Thanks - I hadn't done that but now that I have I haven't found
>> > > > anything. I
>> > > > did check BOL for syntax changes for TOP and ORDER BY and they seem
>> > > > to be the
>> > > > same from 2000 to 2005. There is certainly nothing in the 2005
>> > > > documentation
>> > > > that I've seen that say don't use PERCENT if a view has an ORDER BY
>> > > > clause.
>> > > >
>> > > > Cheers, Peter
>> > > >
>> > > > "John Bell" wrote:
>> > > >
>> > > > > Hi Peter
>> > > > >
>> > > > > Have you checked the backward compatibility sections in books
>> > > > > online?
>> > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm
>> > > > > or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
>> > > > >
>> > > > > John
>> > > > >
>> > > > >
>> > > > > "Peter Jones" wrote:
>> > > > >
>> > > > > > Hi,
>> > > > > >
>> > > > > > I've noticed that ORDER BY in views does not work in SQL Server
>> > > > > > 2005 as it
>> > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100
>> > > > > > Percent...."
>> > > > > > which was converted to: "Select Top (100) Percent..." when I
>> > > > > > recreated my
>> > > > > > views.
>> > > > > >
>> > > > > > However any ORDER BY's in my views are not executed until I
>> > > > > > remove the
>> > > > > > keyword Percent, e.g. Select Top (100)... works fine,
>> > > > > >
>> > > > > > Is this a known difference between 2000 and 2005. If so is
>> > > > > > there a list of
>> > > > > > these differences?
>> > > > > >
>> > > > > > Peter
>> > > > > >|||Peter,
When you realize why you can use TOP xxx ORDER BY in a view, you will
understand.
If your view definition does a TOP selection, the ORDER BY will
determine which rows will be part of the result.
If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
my_table ORDER BY my_primary_key_column" and you select from the view,
the query plan will show the TOP operator. The same is true if you
specify SELECT TOP 100.
If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
will be eliminated from the resultset, regardless of any sorting, so it
does not add a TOP operator to the query plan, nor does it see need to
retrieve the table rows in an ordered fashion (unless you add an ORDER
BY clause to the SELECT that selects from the view). So from an
optimizer perspective it makes perfect sense to disregard the TOP 100
PERCENT .. ORDER BY from the view.
HTH,
Gert-Jan
Peter Jones wrote:
> Hi John,
> No - the conversion was fine - my original code had "Top 100 Percent"
> already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> would not work without it.
> My comment related to the fact that when the keyword "Percent" was removed
> the ORDER BY worked in SQL Server 2005.
> Cheers, Peter
> "John Bell" wrote:
> > Hi Peter
> >
> > I am not sure why it put that in for you, I prefer to recompile all the code
> > (stored procedure/functions/views) from source when doing an upgrade which
> > have avoided this. You may want to consider using a version control system
> > and using that as the source of the code.
> >
> > John
> >
> >
> >
> > "Peter Jones" wrote:
> >
> > >
> > > Gentlemen,
> > >
> > > Thanks for your input. All is clear although it is perculiar that removing
> > > the keyword PERCENT made things work.
> > >
> > > Cheers, Peter
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi Peter
> > > >
> > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > Database Engine Features in SQL Server 2005"
> > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > BY in a view definition"
> > > >
> > > > John
> > > >
> > > > "Peter Jones" wrote:
> > > >
> > > > > Hello John,
> > > > >
> > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > >
> > > > > Cheers, Peter
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi Peter
> > > > > >
> > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > >
> > > > > > John
> > > > > >
> > > > > >
> > > > > > "Peter Jones" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > views.
> > > > > > >
> > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > >
> > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > these differences?
> > > > > > >
> > > > > > > Peter
> > > > > > >|||Hi Gert-Jan,
Thank you for taking the time to add to this thread. I was having trouble
understanding why ORDER BY was even permitted as part of a view - your
comment makes it clear.
Thanks - Peter
"Gert-Jan Strik" wrote:
> Peter,
> When you realize why you can use TOP xxx ORDER BY in a view, you will
> understand.
> If your view definition does a TOP selection, the ORDER BY will
> determine which rows will be part of the result.
> If you specify "CREATE VIEW my_view AS SELECT TOP 50 PERCENT * FROM
> my_table ORDER BY my_primary_key_column" and you select from the view,
> the query plan will show the TOP operator. The same is true if you
> specify SELECT TOP 100.
> If you specify TOP 100 PERCENT, the optimizer recognizes that no rows
> will be eliminated from the resultset, regardless of any sorting, so it
> does not add a TOP operator to the query plan, nor does it see need to
> retrieve the table rows in an ordered fashion (unless you add an ORDER
> BY clause to the SELECT that selects from the view). So from an
> optimizer perspective it makes perfect sense to disregard the TOP 100
> PERCENT .. ORDER BY from the view.
> HTH,
> Gert-Jan
>
> Peter Jones wrote:
> >
> > Hi John,
> >
> > No - the conversion was fine - my original code had "Top 100 Percent"
> > already there. It was my understanding (in SQL Server 2000) that an ORDER BY
> > would not work without it.
> >
> > My comment related to the fact that when the keyword "Percent" was removed
> > the ORDER BY worked in SQL Server 2005.
> >
> > Cheers, Peter
> >
> > "John Bell" wrote:
> >
> > > Hi Peter
> > >
> > > I am not sure why it put that in for you, I prefer to recompile all the code
> > > (stored procedure/functions/views) from source when doing an upgrade which
> > > have avoided this. You may want to consider using a version control system
> > > and using that as the source of the code.
> > >
> > > John
> > >
> > >
> > >
> > > "Peter Jones" wrote:
> > >
> > > >
> > > > Gentlemen,
> > > >
> > > > Thanks for your input. All is clear although it is perculiar that removing
> > > > the keyword PERCENT made things work.
> > > >
> > > > Cheers, Peter
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi Peter
> > > > >
> > > > > The issue David and Bobby refer to is documented at "Breaking Changes to
> > > > > Database Engine Features in SQL Server 2005"
> > > > > http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx under "ORDER
> > > > > BY in a view definition"
> > > > >
> > > > > John
> > > > >
> > > > > "Peter Jones" wrote:
> > > > >
> > > > > > Hello John,
> > > > > >
> > > > > > Thanks - I hadn't done that but now that I have I haven't found anything. I
> > > > > > did check BOL for syntax changes for TOP and ORDER BY and they seem to be the
> > > > > > same from 2000 to 2005. There is certainly nothing in the 2005 documentation
> > > > > > that I've seen that say don't use PERCENT if a view has an ORDER BY clause.
> > > > > >
> > > > > > Cheers, Peter
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi Peter
> > > > > > >
> > > > > > > Have you checked the backward compatibility sections in books online?
> > > > > > > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/10de5ec6-d3cf-42ef-aa62-1bdf3fbde841.htm or at http://msdn2.microsoft.com/en-us/library/ms143532.aspx
> > > > > > >
> > > > > > > John
> > > > > > >
> > > > > > >
> > > > > > > "Peter Jones" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I've noticed that ORDER BY in views does not work in SQL Server 2005 as it
> > > > > > > > did in SQL Server 2000. My old syntax was: "Select Top 100 Percent...."
> > > > > > > > which was converted to: "Select Top (100) Percent..." when I recreated my
> > > > > > > > views.
> > > > > > > >
> > > > > > > > However any ORDER BY's in my views are not executed until I remove the
> > > > > > > > keyword Percent, e.g. Select Top (100)... works fine,
> > > > > > > >
> > > > > > > > Is this a known difference between 2000 and 2005. If so is there a list of
> > > > > > > > these differences?
> > > > > > > >
> > > > > > > > Peter
> > > > > > > >
>sql

Order by in sql 2005 views = don't use?

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

Order by in sql 2005 views = don't use?

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
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|||Some additional info and viewpoints:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/11/28/sorted-views.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"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
> 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
>|||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
>
>> 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
>|||"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...
>> 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
>|||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|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23UxTeLBdIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "John Rivers" <first10@.btinternet.com> wrote in message
> news:18fd596c-8f7e-40e5-96a3-0c41124cb941@.62g2000hsn.googlegroups.com...
>.
> And I suggest some people need to read up on database fundamentals.
>
That is both sound and perverse advice. Sorta like water, water everywhere
and not a drop to drink:)
www.beyondsql.blogspot.com