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

Thanks for calling me a newbie (>5yrs of database development).

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.

@Guenter: do not worry, I wish that one could say, that someone is newbie if he has only such problems...

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.

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.

http://blogs.msdn.com/b/conorcunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

http://blogs.msdn.com/b/queryoptteam/archive/2006/03/24/560396.aspx

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.

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

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


@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....

@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.

@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.

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.

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.

"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.

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

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.