Query Issues with Sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brett_A - 26 Mar 2008 16:18 GMT

I have a view created that sorts by 3 fields.

Field 1 has a data type of "int"
Field 2 has a data type of "nvarchar"
Field 3 has a data type of "nvarchar"

Here is my code - pulled directly from SQL Server Management Studio
Express

SELECT TOP (100) PERCENT test_cat, test_cat_order, test_name,
test_company, test_rlb, test_rlb_blog, test_rl, test_ke, test_ke_blog,
test_graphic, test_audio, test_video, test_text, quote_id
FROM dbo.tbl_testimonials
WHERE (test_ke = N'Yes')
ORDER BY test_cat, test_cat_order, test_name

The results as show in the view design window sort the records
correctly. See screen shot:

http://brettatkin.com/sql1.gif

If I look at the results by selecting "Open View" for the view, the
records are sorted in reverse. See screen shot:

http://brettatkin.com/sql2.gif

What is happening, why is it happening and what do I do to fix it?

Thanks!

Brett

Reply to this Message

Plamen Ratchev - 26 Mar 2008 16:42 GMT

The view is unordered set. To return the correct order use ORDER BY when
querying the view.

The ORDER BY clause in the view is used only to determine the rows that are
returned by the TOP clause. This does not guarantee ordered results when the
view is queried.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Rubén Garrigós - 26 Mar 2008 16:54 GMT

Creating "ordered views" are not a good thing and it is a typical mistake.
Views are unordered in SQL Server, you have to consideer views as sets of
data without order. You should use the order clause in each query against
the view if you need ordering. You can "cheat" SQL Server using a TOP
(99.999999999) PERCENT while you change your queries to add the order clause
but DON'T RELY on that. It is a bad practice and it should be used as an
emergency solution while the correct one is implemented.

Signature

Rubén Garrigós
Solid Quality Mentors

>I have a view created that sorts by 3 fields.
>
[quoted text clipped - 27 lines]
>
> Brett

Reply to this Message

Brett_A - 26 Mar 2008 17:24 GMT

Okay, I'm confused..

I thought the point of creating Views (or queries in my Access days)
was to make the requesting of data faster - have the desired
(filtered) sub-set of data ready.

I can plug that SQL into my ASP call on the web server and get it to
work correctly, but won't that be slower?

If it is bad practice to use Views to create "ordered" data sets, then
what is the point of offering that functionality?

Thanks for the help and comments.

Brett

Reply to this Message

Tom Moreau - 26 Mar 2008 17:37 GMT

Views are good. Using SELECT TOP 100 PERCENT is not. It buys you nothing
and the optimizer is smart enough to ignore it.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Reply to this Message

Plamen Ratchev - 26 Mar 2008 17:39 GMT

You can still use views to provide filtered result sets. However, to
guarantee order of the data you have to use ORDER BY when querying the view:

SELECT <columns>
FROM View
ORDER BY <columns>

A good practice would be to encapsulate this in a stored procedure and call
the stored procedure in your ASP page, rather than plugging SQL on the page.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Tibor Karaszi - 26 Mar 2008 18:08 GMT

In addition tot he other posts:

Views do nothing for performance. There's no notion of "pre-filtering" or something like that. You
can think of a view as a macro, if you wish.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Okay, I'm confused..
>
[quoted text clipped - 11 lines]
>
> Brett

Reply to this Message