[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SQL Server Express 2005 does not sort

Jak

3/28/2007 1:52:00 PM

Hi, all,

When I try to open the query in the new query page, the result returned does
not sort as expected, but if I modify and run it, the result would be
sorted. The SQL codes are as follows:

SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
FROM dbo.View_1
ORDER BY MID

the management tool is Microsoft SQL Server Management Studio Express
running under Windows XP x64 + SP2, the SQL Server Express has been patched
with SP2 already.

Regards,

Jak


7 Answers

Uri Dimant

3/28/2007 2:08:00 PM

0

Jak
Yes it is a "new" behaviour in SQL Server 2005. Don't use it as it makes no
sense.
If you have views that have TOP 100 it will not work on SQL Server 2005.





"Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
> Hi, all,
>
> When I try to open the query in the new query page, the result returned
> does not sort as expected, but if I modify and run it, the result would be
> sorted. The SQL codes are as follows:
>
> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
> FROM dbo.View_1
> ORDER BY MID
>
> the management tool is Microsoft SQL Server Management Studio Express
> running under Windows XP x64 + SP2, the SQL Server Express has been
> patched with SP2 already.
>
> Regards,
>
> Jak
>


Hardik Bati

3/28/2007 7:41:00 PM

0


This should work.
use tempdb
go

create table T1(a int, b int, c int)
go

create view V1
as select top(100) percent a, b,c from T1
go

select TOP(100) percent a, b
From V1
order by a
go

Here the output is sorted by a since its explictly asked.

If you had defined the view as such where the order by is within the view
then there is no assurance of the order comming from the view. Unless
explicitly asked for.

create view V2
as select top(100) percent a, b,c from T1
order by a
go

select * from V2
go

Hardik

"Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
> Hi, all,
>
> When I try to open the query in the new query page, the result returned
> does not sort as expected, but if I modify and run it, the result would be
> sorted. The SQL codes are as follows:
>
> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
> FROM dbo.View_1
> ORDER BY MID
>
> the management tool is Microsoft SQL Server Management Studio Express
> running under Windows XP x64 + SP2, the SQL Server Express has been
> patched with SP2 already.
>
> Regards,
>
> Jak
>


Jak

3/29/2007 4:29:00 AM

0

Thank you, Hardik, but I don't think this would work.

Actually, I created the View1 based on a couple of tables, the SQL scripts
are too long to publish, then I created the second view. Here are the
original scripts for View1:

SELECT TOP (100) PERCENT dbo.TMBR.MID, dbo.TSVCDEF.SNAME,
dbo.TPRSLSTDTL.PRS, dbo.TMCPLST.TREF,
dbo.TPRSLST.DV, dbo.TPRSLST.DIV, dbo.TMBRCAT.MTYPE
FROM dbo.TPRSLSTDTL INNER JOIN
dbo.TPRSLST ON dbo.TPRSLSTDTL.PRSNAME =
dbo.TPRSLST.PRSNAME INNER JOIN
dbo.TMBR INNER JOIN
dbo.TMBRCAT ON dbo.TMBR.MBRCAT = dbo.TMBRCAT.TYPEID
INNER JOIN
dbo.TMCPLST ON dbo.TMBRCAT.TYPEID = dbo.TMCPLST.MBRCAT
ON dbo.TPRSLST.PRSNAME = dbo.TMCPLST.PLSTREF INNER JOIN dbo.TSVCDEF ON
dbo.TPRSLSTDTL.SKU = dbo.TSVCDEF.SKU
ORDER BY MID, SNAME

in the last line, I explictly asked the view to sort the record by MID and
SNAME, but I will run into the same error as I stated before. When open it
directly, I mean I would not open it by clicking modify item, the result
would not be sorted as asked at all.

Jak



"Hardik Bati [MS]" <hardikb_removethis_@microsoft.com> wrote in message
news:%23X%23x3DXcHHA.5060@TK2MSFTNGP06.phx.gbl...
>
> This should work.
> use tempdb
> go
>
> create table T1(a int, b int, c int)
> go
>
> create view V1
> as select top(100) percent a, b,c from T1
> go
>
> select TOP(100) percent a, b
> From V1
> order by a
> go
>
> Here the output is sorted by a since its explictly asked.
>
> If you had defined the view as such where the order by is within the view
> then there is no assurance of the order comming from the view. Unless
> explicitly asked for.
>
> create view V2
> as select top(100) percent a, b,c from T1
> order by a
> go
>
> select * from V2
> go
>
> Hardik
>
> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
> news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
>> Hi, all,
>>
>> When I try to open the query in the new query page, the result returned
>> does not sort as expected, but if I modify and run it, the result would
>> be sorted. The SQL codes are as follows:
>>
>> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
>> FROM dbo.View_1
>> ORDER BY MID
>>
>> the management tool is Microsoft SQL Server Management Studio Express
>> running under Windows XP x64 + SP2, the SQL Server Express has been
>> patched with SP2 already.
>>
>> Regards,
>>
>> Jak
>>
>
>


Jak

3/29/2007 4:48:00 AM

0

As Uri said, this might be a 'New' behavior for SQL 2005, I just tested your
scripts, if I ran them in the query pages, they performed properly, but if I
saved them as views and open the views later, it would not be sorted at all.

I am wondering if this would be a bug of SQL 2005.

Jak


"Hardik Bati [MS]" <hardikb_removethis_@microsoft.com> wrote in message
news:%23X%23x3DXcHHA.5060@TK2MSFTNGP06.phx.gbl...
>
> This should work.
> use tempdb
> go
>
> create table T1(a int, b int, c int)
> go
>
> create view V1
> as select top(100) percent a, b,c from T1
> go
>
> select TOP(100) percent a, b
> From V1
> order by a
> go
>
> Here the output is sorted by a since its explictly asked.
>
> If you had defined the view as such where the order by is within the view
> then there is no assurance of the order comming from the view. Unless
> explicitly asked for.
>
> create view V2
> as select top(100) percent a, b,c from T1
> order by a
> go
>
> select * from V2
> go
>
> Hardik
>
> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
> news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
>> Hi, all,
>>
>> When I try to open the query in the new query page, the result returned
>> does not sort as expected, but if I modify and run it, the result would
>> be sorted. The SQL codes are as follows:
>>
>> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
>> FROM dbo.View_1
>> ORDER BY MID
>>
>> the management tool is Microsoft SQL Server Management Studio Express
>> running under Windows XP x64 + SP2, the SQL Server Express has been
>> patched with SP2 already.
>>
>> Regards,
>>
>> Jak
>>
>
>


Tibor Karaszi

3/29/2007 7:00:00 AM

0

> I am wondering if this would be a bug of SQL 2005.

It is not a bug. A table is not ordered, a view is supposed to behave like a table, hence a view
isn't ordered. SQL Server 2000's optimizer was weaker than 2005. In 2005, the optimizer sees TOP 100
PERCENT and ORDER BY, which means it will remove both thereby improving performance.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Jak" <NO-spam_cnlohmann@yeah.net> wrote in message news:%23G4Zp1bcHHA.984@TK2MSFTNGP04.phx.gbl...
> As Uri said, this might be a 'New' behavior for SQL 2005, I just tested your scripts, if I ran
> them in the query pages, they performed properly, but if I saved them as views and open the views
> later, it would not be sorted at all.
>
> I am wondering if this would be a bug of SQL 2005.
>
> Jak
>
>
> "Hardik Bati [MS]" <hardikb_removethis_@microsoft.com> wrote in message
> news:%23X%23x3DXcHHA.5060@TK2MSFTNGP06.phx.gbl...
>>
>> This should work.
>> use tempdb
>> go
>>
>> create table T1(a int, b int, c int)
>> go
>>
>> create view V1
>> as select top(100) percent a, b,c from T1
>> go
>>
>> select TOP(100) percent a, b
>> From V1
>> order by a
>> go
>>
>> Here the output is sorted by a since its explictly asked.
>>
>> If you had defined the view as such where the order by is within the view then there is no
>> assurance of the order comming from the view. Unless explicitly asked for.
>>
>> create view V2
>> as select top(100) percent a, b,c from T1
>> order by a
>> go
>>
>> select * from V2
>> go
>>
>> Hardik
>>
>> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
>>> Hi, all,
>>>
>>> When I try to open the query in the new query page, the result returned does not sort as
>>> expected, but if I modify and run it, the result would be sorted. The SQL codes are as follows:
>>>
>>> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
>>> FROM dbo.View_1
>>> ORDER BY MID
>>>
>>> the management tool is Microsoft SQL Server Management Studio Express running under Windows XP
>>> x64 + SP2, the SQL Server Express has been patched with SP2 already.
>>>
>>> Regards,
>>>
>>> Jak
>>>
>>
>>
>
>


Jak

3/29/2007 2:39:00 PM

0

I am sorry I did not get it, if I was right in understanding, if there were
TOP 100 PERCENT and ORDER BY in the query scripts, it would not be sorted
for a better performance? But how would I do if I would like to get all of
the recordset in descending order?

Jak



"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:eBDfQ$ccHHA.2068@TK2MSFTNGP06.phx.gbl...
>> I am wondering if this would be a bug of SQL 2005.
>
> It is not a bug. A table is not ordered, a view is supposed to behave like
> a table, hence a view isn't ordered. SQL Server 2000's optimizer was
> weaker than 2005. In 2005, the optimizer sees TOP 100 PERCENT and ORDER
> BY, which means it will remove both thereby improving performance.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
> news:%23G4Zp1bcHHA.984@TK2MSFTNGP04.phx.gbl...
>> As Uri said, this might be a 'New' behavior for SQL 2005, I just tested
>> your scripts, if I ran them in the query pages, they performed properly,
>> but if I saved them as views and open the views later, it would not be
>> sorted at all.
>>
>> I am wondering if this would be a bug of SQL 2005.
>>
>> Jak
>>
>>
>> "Hardik Bati [MS]" <hardikb_removethis_@microsoft.com> wrote in message
>> news:%23X%23x3DXcHHA.5060@TK2MSFTNGP06.phx.gbl...
>>>
>>> This should work.
>>> use tempdb
>>> go
>>>
>>> create table T1(a int, b int, c int)
>>> go
>>>
>>> create view V1
>>> as select top(100) percent a, b,c from T1
>>> go
>>>
>>> select TOP(100) percent a, b
>>> From V1
>>> order by a
>>> go
>>>
>>> Here the output is sorted by a since its explictly asked.
>>>
>>> If you had defined the view as such where the order by is within the
>>> view then there is no assurance of the order comming from the view.
>>> Unless explicitly asked for.
>>>
>>> create view V2
>>> as select top(100) percent a, b,c from T1
>>> order by a
>>> go
>>>
>>> select * from V2
>>> go
>>>
>>> Hardik
>>>
>>> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
>>> news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
>>>> Hi, all,
>>>>
>>>> When I try to open the query in the new query page, the result returned
>>>> does not sort as expected, but if I modify and run it, the result would
>>>> be sorted. The SQL codes are as follows:
>>>>
>>>> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
>>>> FROM dbo.View_1
>>>> ORDER BY MID
>>>>
>>>> the management tool is Microsoft SQL Server Management Studio Express
>>>> running under Windows XP x64 + SP2, the SQL Server Express has been
>>>> patched with SP2 already.
>>>>
>>>> Regards,
>>>>
>>>> Jak
>>>>
>>>
>>>
>>
>>
>
>


Tibor Karaszi

3/29/2007 3:16:00 PM

0

> But how would I do if I would like to get all of the recordset in descending order?

Have the ORDER BY in the SELECT statement that read data from the view.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Jak" <NO-spam_cnlohmann@yeah.net> wrote in message news:OsYCu$gcHHA.4684@TK2MSFTNGP06.phx.gbl...
>I am sorry I did not get it, if I was right in understanding, if there were TOP 100 PERCENT and
>ORDER BY in the query scripts, it would not be sorted for a better performance? But how would I do
>if I would like to get all of the recordset in descending order?
>
> Jak
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:eBDfQ$ccHHA.2068@TK2MSFTNGP06.phx.gbl...
>>> I am wondering if this would be a bug of SQL 2005.
>>
>> It is not a bug. A table is not ordered, a view is supposed to behave like a table, hence a view
>> isn't ordered. SQL Server 2000's optimizer was weaker than 2005. In 2005, the optimizer sees TOP
>> 100 PERCENT and ORDER BY, which means it will remove both thereby improving performance.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
>> news:%23G4Zp1bcHHA.984@TK2MSFTNGP04.phx.gbl...
>>> As Uri said, this might be a 'New' behavior for SQL 2005, I just tested your scripts, if I ran
>>> them in the query pages, they performed properly, but if I saved them as views and open the
>>> views later, it would not be sorted at all.
>>>
>>> I am wondering if this would be a bug of SQL 2005.
>>>
>>> Jak
>>>
>>>
>>> "Hardik Bati [MS]" <hardikb_removethis_@microsoft.com> wrote in message
>>> news:%23X%23x3DXcHHA.5060@TK2MSFTNGP06.phx.gbl...
>>>>
>>>> This should work.
>>>> use tempdb
>>>> go
>>>>
>>>> create table T1(a int, b int, c int)
>>>> go
>>>>
>>>> create view V1
>>>> as select top(100) percent a, b,c from T1
>>>> go
>>>>
>>>> select TOP(100) percent a, b
>>>> From V1
>>>> order by a
>>>> go
>>>>
>>>> Here the output is sorted by a since its explictly asked.
>>>>
>>>> If you had defined the view as such where the order by is within the view then there is no
>>>> assurance of the order comming from the view. Unless explicitly asked for.
>>>>
>>>> create view V2
>>>> as select top(100) percent a, b,c from T1
>>>> order by a
>>>> go
>>>>
>>>> select * from V2
>>>> go
>>>>
>>>> Hardik
>>>>
>>>> "Jak" <NO-spam_cnlohmann@yeah.net> wrote in message
>>>> news:elcfIBUcHHA.4516@TK2MSFTNGP04.phx.gbl...
>>>>> Hi, all,
>>>>>
>>>>> When I try to open the query in the new query page, the result returned does not sort as
>>>>> expected, but if I modify and run it, the result would be sorted. The SQL codes are as
>>>>> follows:
>>>>>
>>>>> SELECT TOP (100) PERCENT MID, SNAME, TREF, DTEFT
>>>>> FROM dbo.View_1
>>>>> ORDER BY MID
>>>>>
>>>>> the management tool is Microsoft SQL Server Management Studio Express running under Windows XP
>>>>> x64 + SP2, the SQL Server Express has been patched with SP2 already.
>>>>>
>>>>> Regards,
>>>>>
>>>>> Jak
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>