October 7, 2007 at 2:43 pm
Hello,
I am currently trying to find out whether the following example of order by in a view would not yield a correct ordered result:
CREATE VIEW dbo.vwTopQry (name, type) AS SELECT TOP 99.999999999 PERCENT name, type FROM sys.objects ORDER BY type, name
I am not looking for a theoretical explanation like "relational sets are not ordered" or "it is not guaranteed because BOL states so". I just would like to see a practical example where the code might break.
Is there any step in the generation of the results that would "scramble" the order of sorted interim results? Or better to say, is there any reason for SQL Server to read from a sorted interim result set in an unordered fashion? And if yes, how would this happen?
Thanks in advance!
Chris
Best Regards,
Chris Büttner
October 7, 2007 at 9:02 pm
Good evening.
Someone correct me if I am wrong.
You can not Embed an Order By in a view.
A view is handled by SQL as a logical table. You can create a view that contains only certain columns of a logical table as an example and when you do say:
SELECT *
FROM MyView
You will only get those columns included in the view.
More often you will use a view to join tables that are commonly related to each other and perhaps even index the view; thereby negating much of the performance hit in creating these joins within a stored procedure, not to mention the time saved if this is an often used join set.
That said you Order by is applied in your query when you are pulling data from the view. It is here that you can include Order By, Group By, ect.
Hope this clears it up for you and provides the answers that you are looking for. If not please let the community know and I or someone else would be happy to jump in with something else.
Regards,
Jeff
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 8, 2007 at 12:41 am
Hello Jeff,
Thanks very much for your answer first. But unfortunately this was exactly the type of answer I was trying to avoid. From what I can see my sample is not really showing up in the post which should be the cause for the misunderstanding. Therefore I paste it now as clear text:
CREATE VIEW dbo.vwDoesWork AS
SELECT TOP 99.999999999999 PERCENT type, object_id FROM sys.objects ORDER BY type, name
GO
SELECT * FROM dbo.vwDoesWork
You should get the results from the view back sorted by type and object id and this is what it does in 100% of the examples I have tried so far.
This is the execution plan:
|--Top(TOP EXPRESSION:((9.999999999999901e+001)) PERCENT)
|--Sort(ORDER BY:([o].[type] ASC, [o].[name] ASC))
|--Filter(WHERE:(has_access('CO',[master].[sys].[sysschobjs].[id] as [o].[id])=(1)))
|--Clustered Index Scan(OBJECT:([master].[sys].[sysschobjs].[clst] AS [o]), WHERE:([master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)))
Now since the interim result set is sorted in the second last step, I would also expect SQL Server to return the results in this order. If not, I would like to understand the reason: Why / when would SQL Server return rows from a sorted result set not in a sequential manner?
What I do understand is that this type of "internal" sorting somehow might prevent the optimizer from making better choices, so it really only makes sense if you want to immediately return the results to the client. (Unless this can be used like an alternative "hint" telling the optimizer to pre-sort a derived table for getting a specific join type instead of choosing a join type directly. Hm but now back to reality 😉 )
Hope this explains my question a little better.
Thanks!
Best Regards,
Chris Büttner
October 8, 2007 at 5:50 am
If your query is executed on a multi-processor machine, then SQL may choose to open multiple threads to return your result set. If the DBA has defined multiple files to hold tempdb, then the work table holding your intermediate query results will be spread over the physical files and SQL is even more likely to open multiple threads to return your result set. If your query runs when SQL is quiet and when it is busy, SQL may assign different numbers of threads to return your result set. If you run on SQL 2008 SQL is more likly to use multiple threads to return reaults than SQL 2005 or 2000. Each thread will run independantly, returning the rows as it reads them. In this way the data in your view may not get returned in an ORDERed fashion to the client.
The only way to guarantee the oder in which data is returned to the client is to use an ORDER BY in the outermost SELECT statement.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 8, 2007 at 5:51 am
it's not really considered good practice to write views in this manner, you should order the results from the view in the select against the view.
That said the view will order the data - index choice will be affected by the select against the view - I assume you're not just issuing select * statements against the view?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 7:43 am
Thanks Ed! This was was really helpful.
May I ask you from where you got your knowledge?
Especially the parallel execution doesnt seem to be
well documented out in the net.
Best Regards,
Chris Büttner
October 8, 2007 at 7:53 am
Christian,
I think what you may be looking for is as follows:
In SQL Server 2000 you can create a view that includes an ORDER BY in the view's definition.
Ex. "SELECT TOP 100 PERCENT * FROM tblMyTable ORDER BY myKey."
When selecting records like this, "SELECT * FROM vwMyView" the results returned will be in order by [myKey].
---------------------
However, in SQL Server 2005 and later, if you create the same view, and select records with the same "SELECT" statement the results will be unordered.
This is because Microsoft has changed the effect the "ORDER BY" clause has when you create the view in the first place.
The new functionality causes the "ORDER BY" to be used ONLY to make sure you get the correct TOP number of records. If your view is selecting the TOP 100 PERCENT, then the ORDER BY is ignored.
If your view (vwMyView) is, "SELECT TOP 10 * FROM tblMyTable ORDER BY myKey ASC." Then "SELECT * FROM vwMyView" will return the first 10 lowest [myKey] values (but they will not necessarily be in any ORDER). When you select from the view you will still have to ask for the records in the order you want. Like "SELECT * FROM vwMyView ORDER BY myKey."
---------------------
In Books Online for SQL Server 2000, CREATE VIEW:
There are a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:
Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement.
---------------------
In Books Online for SQL Server 2005, CREATE VIEW (see the note Microsoft adds below):
The SELECT clauses in a view definition cannot include the following:
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
Note:
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.
October 8, 2007 at 8:03 am
Hi Colin,
I still think that order by in a view would make sense if the order by is the last step performed and the view is not joined to anything else.
Its just a short cut. And thats why we are programming: to reduce the steps we have to make for a specific task. Even if its just removing the need for adding an "ORDER BY" when selecting from a view 😀
But unless MS introduces something like "ORDER BY FOR END_RESULT" I totally agree that "ORDER BY" should not be used in views etc. at all.
Thanks for your answer!
Best Regards,
Chris Büttner
October 8, 2007 at 8:08 am
Hello R.
thanks for pulling out this information. It certainly helps most users get detailed insight into this topic.
I already knew these BOL explanations. Thats why I used 99.999999999 PERCENT instead of 100 as a workaround. If you examine the execution plans for both types, you will see that SQL Server sorts the first output but not the second.
What BOL didnt state was, where things could actually go wrong.
But Ed did a great job of explaining this.
Thanks again for your input.
Best Regards,
Chris Büttner
October 8, 2007 at 8:44 am
If an order by in a view is 'sense' or 'nonsense', that's a theoretical question.
According to theory, if we want a table, then there is no order.
If we have order, then we have no table.
Anyways, here's how it works in SQL Server. When you can expect order by to 'come through' and when you're just lucky if it does...
The full article is here: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
-- quote --
Ordering guarantees of queries in various context is a common source of confusion. For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent to the client since the query optimizer will re-order operations to find more efficient query plans. Note that even though this topic applies to SQL Server 2005 most of the rules are valid for SQL Server 2000 too.
Here are the scenarios that guarantee ordering:
1. If you have an ORDER BY in the top-most SELECT block in a query, the presentation order of the results honor that ORDER BY request
2. If you have a TOP in the same SELECT block as an ORDER BY, any TOP computation is performed with respect to that ORDER BY. For example, if there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows within a given sort. Note that this does not guarantee that subsequent operations will somehow retain the sort order of a previous operation. The query optimizer re-orders operations to find more efficient query plans
3. Cursors over queries containing ORDER BY in the top-most scope will navigate in that order
4. INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
5. SQL Server 2005 supports a number of new "sequence functions" like RANK(), ROW_NUMBER() that can be performed in a given order using a OVER clause with ORDER BY
6. For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.
-- end quote --
/Kenneth
October 8, 2007 at 8:51 am
According to theory, if we want a table, then there is no order.
If we have order, then we have no table.
Exactly. So it depends on whether you want a table or just a result. And in my examples, I just want an ordered result.
And thanks for the interesting quote.
Best Regards,
Chris Büttner
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply