Re: ORDER BY in VIEW not working

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

Relevant Pages

  • Re: Debunking the "Linux cant have viruses" myth ...
    ... Goodness knows I do that sort of thing often enough myself. ... And I might point out that your machine _can_ be subject to compromise ... public development server shells.source.org are.... ... > Source is unpractical for businesses, pulled this kind of an argument ...
    (comp.os.linux.security)
  • Re: expanding a stored proc that is already beyond me
    ... >> I need to expand this stored procedure which is already beyond my current ... and which direction to sort in.. ... SELECT MainID, Title, ShortDesc, Priority, EstimatedDeliveryDate, ... >> EstimatedDeliveryDate END ASC, ...
    (microsoft.public.sqlserver.programming)
  • Re: Sorted data is not sorted
    ... I don't think Select looks at the deafault view's sort. ... > Select Coverage, Limit, LimitDescription, PerPerson, PerAccident From ... > dim drs() as datarow ... I would prefer the data be ordered by my stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Sorting in GridView problem
    ... My stored procedure will return the data in the correct sort order ... clicked column isn't being passed to the select function that calls the ... >> an ObjectDataSource that uses a stored procedure to return the data. ... >> * In the gridviews OnSorting event handler, ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Re: expanding a stored proc that is already beyond me
    ... I use the following stored procedure to bind data ... It handles getting the data and which field to sort ... > SELECT MainID, Title, ShortDesc, Priority, EstimatedDeliveryDate, ... > EstimatedDeliveryDate END ASC, ...
    (microsoft.public.sqlserver.programming)
Loading