Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

ORDER BY in table valued functions is doesn't work as I expected

Apr 19 2012 12:00AM by Guenter   

I just stumbled across a weird misconception that I'd like to help you to avoid: If you consume the results of a (non-inline) table valued function, the results are not in the same order as if you take the content óf the TVF in your query.

So, if you have a query like

select * from SomeTable order by FirstColumn

and you put it into a table valued function like

CREATE FUNCTION myFunction()
RETURNS @result TABLE(FirstColumn int)
AS
BEGIN
  INSERT INTO @result
  SELECT FirstColumn from SomeTable order by FirstColumn
  RETURN
END

and call it as

SELECT * from myFunction()

you will probably, but not always, get the FirstNumber in order. But you cannot rely on it, as it could change with different execution plans. As soon as the plan generator chooses a parallel plan you might get a partially sorted data stream. This conforms to MS who say that a SELECT from anything is not guaranteed to be sorted in any way unless you specify an explicit ORDER BY clause. So in my case you get the correct result by using

SELECT * from myFunction() ORDER BY FirstColumn
Read More..   [77 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Guenter
28 · 6% · 1887
21
 
11
 
11
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

17  Comments  

  • This is a newbie who has no idea what a table is presuming it is a sequential file.

    commented on Apr 19 2012 6:57AM
    jcelko
    452 · 0% · 87
  • Thanks for calling me a newbie (>5yrs of database development). alt text

    The mismatch was not about "a table" but about ordering in TVFs - maybe that's a newbie thing, too, but alas I learned it - and some others too.

    commented on Apr 19 2012 12:06PM
    Guenter
    28 · 6% · 1887
  • @Guenter: do not worry, I wish that one could say, that someone is newbie if he has only such problems...

    commented on Apr 19 2012 1:26PM
    Adam Tokarski
    58 · 3% · 1021
  • The mismatch was not about "a table" but about ordering in TVFs - maybe that's a newbie thing, too, <<

    What does a TVF produce? If you know what a table is this would not be a surprise. You are right, tho; the use the proprietary TVF instead of a derived table tells us the newbie is an OO programmer. He wants to use methods and not expressions. so he uses the construct that looks most like his old language. Of course, it screws up the optimizer and other users sessions, does not port and messes up maintainability. But it looks OO.

    commented on Apr 19 2012 8:32PM
    jcelko
    452 · 0% · 87
  • It's a good reminder, I'm sure people are willing to know all the basics as well as the more advanced topics.

    I totally went through that kind of SQL issue at some point in my life, newbie or not Guenter post is valuable.

    commented on Apr 20 2012 2:33AM
    Sergejack
    41 · 4% · 1395
  • I knew ORDER is not guaranteed unless ORDER BY is specified. It was important for me primarily because I started my career with DBASE and went through FOXBASE, CLIPPER, FOXPRO etc where the concept of 'physical position' of rows existed. When I moved to SQL Server, this was one thing that took time to digest.

    I always use an ORDER BY in my queries when I need data sorted. I still find the post by Gunter valuable because that is something many people miss. People might think, "hey there is an order by in my TVF, so I am supposed to get ordered results". I find this post as a good reminder to everyone.

    commented on Apr 20 2012 3:00AM
    Jacob Sebastian
    1 · 100% · 32235
  • Guenter,

    Thanks for the information.

    Can you provide few more details like how many rows u have and when it -TVF starts behaving like this.

    Thanks Manoj

    commented on Apr 20 2012 8:29AM
    Manoj Bhadiyadra
    147 · 1% · 335
  • I hear and understand what you are saying. However, I think adding a PRIMARY KEY to the result works, as well. Check out this test. Am I wrong?

    CREATE FUNCTION myFunction()
    RETURNS @result TABLE(FirstName nvarchar(50) primary key)
    AS
    BEGIN
    INSERT INTO @result
        SELECT distinct top 10 FirstName from AdventureWorks.Person.Contact
        RETURN
    END

    commented on Apr 20 2012 9:44AM
    jlmurphy
    325 · 0% · 131
  • @jlmurphy : afaik a primary key is merely a constraint, ensuring that the combination of the fields it exists of will be unique. If you don't use the "Order By"-clause in the query I wouldn't count on having any result set always returning in the same order.

    @Guenter : it's not a newbie thing at all to fall into the trap you explained! I've seen expensive database specialists with many years of experience falling into it, and I learned it the hard way myself too, in the time that expressions were old and OO was brand new....

    commented on Apr 20 2012 3:17PM
    Sebastiaan
    765 · 0% · 41
  • @jlmurphy: I have seen this comment in the web, but it did not work for me.

    The point is that as soon as you consume the result of the TVF in a SQL statement, things start to mess up. As there's no way to get the results of a TVF than through a surrounding query, there's always a chance to get the wrong order, no matte what you do inside the query.

    @Manoj: I believe that as soon as the query optimizer starts to parallelize the query, the order is not guaranteed any more, as the buffers are processed in parallel (that's what it sais, isn't it? :-). I found that the data received has the correct ordering in general, but for example if you have record numbers 1 to 10000, the data received starts with 2000..3999, than 1...1999, than 6000.7999 and so on, where the actual number of consecutive rows is dependent on row size and other criteria of that sort.

    I took from my experience (you know, such things tend to happen only on the production servver...) that I won't trust ordering unless the outermost statement has an explicit ORDER BY. At least until now that worked for me. Think of everything inside the query as a SET where no order can be taken for granted, and you're off fine.

    commented on Apr 20 2012 3:21PM
    Guenter
    28 · 6% · 1887
  • @jcelko: Why do you attack someone who just wants to share a caveat with the world? The discussion here is about sorting, so perhaps it's a bit off-topic but there are situations where "screwing up" the optimizer and other users sessions, not being able to port and messing up maintainability aren't very strong arguments for not using a TVF over a derived table. On the contrary, sometimes a TVF can make code more maintainable and has no negative impact on the optimizer or other user sessions, and sometimes people just don't want to port anyway.

    commented on Apr 20 2012 3:39PM
    Sebastiaan
    765 · 0% · 41
  • Can you provide few more details like how many rows u have and when it -TVF starts behaving like this. Thanks Manoj <<

    "Against stupidity the gods themselves struggle in vain." - Die Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

    You missed the whole concept! A table with a trillion rows has no ordering. A table with a mllion rows has no ordering. A table with a thousand rows has no ordering. A table with a hundred rows has no ordering. A table with ten rows has no ordering. Now listen carefully, a table with one row has no ordering!! And an empty tlavb ehas no ordering! THERE IS NO ORDERING IN RDBMS.

    If you get an ordering it is not required.

    commented on Apr 20 2012 4:42PM
    jcelko
    452 · 0% · 87
  • I have never used an ORDER BY within a TVF, for a simple reason that ordering of records in the result set is generally important only when the final presentation is to be made, i.e. in the query that actually returns results to the calling code.

    The order of records in a table is NOT guaranteed by SQL Server (or for that matter, by any RDBMS system). Because TVFs return a table, the order of records in the resulting table cannot (and should not) be guaranteed. If an order is required, it should be the duty of the consuming query to specify the ORDER BY.

    SQL Server is absolutely justified, in my humble opinion, to demonstrate the mentioned behaviour.

    commented on Apr 20 2012 10:37PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • "Against stupidity the gods themselves struggle in vain." - Die Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

    There are no stupid questions, I'm not sure about the answers though......

    THERE IS NO ORDERING IN RDBMS.

    In theory that's correct. But sometimes... no, many times it can take a lot of effort to get a result in a random order. And e.g. clustered indexes that order the data on disk add to the confusion.

    commented on Apr 21 2012 4:08AM
    Sebastiaan
    765 · 0% · 41
  • Hi Jlmurphy,

    Instead of Primary Key, adding Clustered Index will give most of the time ordered result (not necessary) since data is stored in Index Key order.

    But as per set theory there is no grantee on order of result set if your not specified, not only for row even for columns.

    Ideally as part TVF don't use ORDER BY until unless any specific logic applied based on the ORDER BY clause, In this example you not using some thing specific to ORDER BY clause (like TOP 1 or RowNumber() =1 etc).

    thanks Nirmal Ram P K

    commented on Apr 22 2012 12:56AM
    Nirmal Ram
    1416 · 0% · 15
  • Hi Gaunter,

    Really this is more help full to who is new to sql server. And also i have a look on comments from the experts, so there is lot of discussion on this.

    Any how thanks for sharing.

    commented on Jan 24 2013 1:32AM
    Bala Krishna
    83 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"ORDER BY in table valued functions is doesn't work as I expected" rated 5 out of 5 by 21 readers
ORDER BY in table valued functions is doesn't work as I expected , 5.0 out of 5 based on 21 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]