Posted by RickW on 03/07/06 00:29

I sort of expected this kind of response, but since it serves utterly no
useful purpose whatsoever, I'm going to move on to a question more along
the lines suited to the kind of answers that predominate here.

Let's take a quick inventory.

Table - by definition, no sort order allowed. No problem.

View - by definition, no sort order allowed. Workaround previously
available, now withdrawn, but let's allow it for discussion. (Would
have been nice to have some warning, but what the hell.)

Table-returning Function - Testing shows that it also does not preserve
the specified order in a result set. OK. This is obvious. It returns
a table. No sort allowed.

Stored Procedure - Testing shows that ... it DOES return an ordered data
set. Wait a minute. That can't be right. It is returning...a table!
And, by definition, no sort allowed on a table.

And yet no one is griping about that. In fact, DP's own answer begins
with "Use a proc." But that can't be good. A proc, returning a table,
should not preserve order.

If we followed this line of reasoning to the limit, there would simply
be no way to return an ordered result set by using any native SS object.
That would have to depend on using a query outside SS. But wait a
minute, writing SQL in code is a sign of amateurishness. It's all
supposed to be done with parameters fed to stored procedures. Now
what!?

The plain fact is that SQL Server is a tool. Nothing more. It is a
compromise to allow a stored procedure to return an ordered set, unless
the hallowed standard being referred to in other posts has somewhere in
it an exception for SQL-Server-specific objects such as stored
procedures.

Preserving order in a result set from a stored procedure is a
compromise. It's a nice compromise. I like it. But I also liked the
compromise whereby a view could also return an ordered set. I don't see
a reason on earth to believe that the stored procedure compromise is
fine, but the view compromise is unacceptable.

I would, honestly, love to read the rationale for saying the it simplay
cannot be allowed in a view, but it is just fine (even okay to recommend
to someone as a solution) to allow it in a stored procedure.

Science is organized common sense where many a beautiful theory was
killed by an ugly fact. -- Thomas Huxley

*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]