March 8, 2011 at 4:58 pm
Hi All,
I am unable to sort the following table, which is viewed through a view, by column C.
Table:
Create table Table1
(a varchar (10) null,
b varchar (10) null,
c varchar (10) null,
d varchar (10) null,
e datetime null,
f datetime null,
e varchar (10) null,
g int null,
h varchar (10) null)
I have added a index that consists of a,b,c,e
Then there is a stored Proc which populates this table such as:
1. New data is added as new rows and
2. if existing data is updated, it is added as a whole new row.
Now, the user wants to see the data sorted by column c and the order of the columns is b,a,c,f,g,e
SO, I've created a view for them to the data in the order requested (with the hint to the index); however, even after creating the index, it is still not displaying the data sorted by "c". and I want to avoid telling the user to sort it on his end.
I also want to avoid creating another table where i can then store the data in the specific order.
I've tried both clustered and non clustered indexes; but it doesn't work. pls help?
March 8, 2011 at 5:37 pm
The only way to guarantee sort order is to issue an ORDER BY in your query...this is a fundamental concept of set theory.
This may look familiar: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Your end user should sort the results using an ORDER BY...or you can explore the workaround alluded to in the above error message and look into using TOP within your view.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 5:47 pm
Yes, i've even tried the top 100 percent, but still no good.
So, just confirming - Index is only for performance. It has no hand in sorting the data, right? I thought if the index is clustered, then it should bring the result also in the same order that it is stored in.
March 8, 2011 at 5:51 pm
TK-487581 (3/8/2011)
Yes, i've even tried the top 100 percent, but still no good.So, just confirming - Index is only for performance. It has no hand in sorting the data, right? I thought if the index is clustered, then it should bring the result also in the same order that it is stored in.
It is true that the clustered index can affect the sorting of results when no ORDER BY is present, however there is no guarantee without ORDER BY.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 5:51 pm
TK-487581 (3/8/2011)
Yes, i've even tried the top 100 percent, but still no good.So, just confirming - Index is only for performance. It has no hand in sorting the data, right? I thought if the index is clustered, then it should bring the result also in the same order that it is stored in.
It is true that the clustered index can affect the sorting of results when no ORDER BY is present, however there is no guarantee without ORDER BY.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 6:02 pm
TK-487581 (3/8/2011)
So, just confirming - Index is only for performance. It has no hand in sorting the data, right? I thought if the index is clustered, then it should bring the result also in the same order that it is stored in.
An index can be used for sorting if SQL Server thinks it is cost effective, but SQL Server will only sort the data if the top level query contains the ORDER BY statement. So putting ORDER BY in the view or sub-query will do no good, SQL Server will ignore it. (This wasn't true in SQL Server 2005 where you could use the TOP 100 PERCENT to usually make a view sort the data.)
March 8, 2011 at 6:11 pm
So, either I ask the user to use the "Order by" or create a table TABLE2 and have the columns in the order requested by the user and ordered by column c.
I'm still not sure if option 2 will work when the user tried to get data from it without the order by clause.
What do you think?
March 8, 2011 at 7:46 pm
Even if you were to create TABLE2, and cluster it so that it is ordered on column C, you're still not guaranteed to return the rows in order unless you have the ORDER BY. Most of the time (could be 999 times out of 1000), a scan of that table will return the data in order of C, but there are circumstances (such as merry-go-round scans) which may process the rows in the table out of order, and because there was no ORDER BY, SQL Server does not count this as a problem (i.e., by leaving it out, you've told SQL Server that you don't care about the order).
The short answer is, unless you want to get a call in three months complaining about out-of-order data (that you probably can't reproduce), you should get the user to always include an ORDER BY clause if they want data ordered.
March 9, 2011 at 4:53 pm
Thanks everyone. I just wanted to make sure that there was no 'guaranteed' way to get the data ordered by C without asking the user to do so. Thanks for confiming that for me.
March 10, 2011 at 1:35 am
Maybe this is a silly question, but couldn't you just use a stored procedure
CREATE PROC Proc1
AS
SELECT b,a,c,f,g,e
FROM Table1
ORDER BY c
and then ask the user to run "EXEC Proc1" to get the ordered data?
March 10, 2011 at 9:52 am
Yes, you could use an SP, as long as the user doesn't need to be able to write adhoc queries against it. (Even then you could handle that through parameters, but it could end up requiring dynamic SQL.)
March 10, 2011 at 10:16 am
UMG Developer (3/10/2011)
Yes, you could use an SP, as long as the user doesn't need to be able to write adhoc queries against it. (Even then you could handle that through parameters, but it could end up requiring dynamic SQL.)
Presumably though, if they were actually going as far as writing adhoc queries they could just add "ORDER BY c" on the end of their queries?
I must admit I'm slightly intrigued now as to how the end user is accessing the data.
March 14, 2011 at 5:02 pm
Yes, we had a discussion and I realised that the users perfer to see the data in an Excel spreadsheet. So, I'm going to create an SSIS package and will do the order by in the query there.
Thanks heaps everyone for your help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply