Hi,
I have this problem.

My software creates a temporary table (#MyTable).
This table should be used by a report engine and printed each time with
different "order by" clause, depending on some parameters (and the program
that creates the temporary table obviously knows these parameters...)

Now, I don't want to pass these paramete to the report engine, because I
want that the logic of the report will stay only in the program that create
the table (the report engine should onnly do a "SELECT * FROM #MyTable").

So, I'm asking if there is a way to define, for a table, a default "order
by" clause to use when no "order by" clause is specified in a "select" query
statement on that table.

If not, I think the only alternative is to create a view on that table. Is
it correct?

Thanks,
Davide.

No, the only way to guarantee result order is to specify ORDER BY in the
SELECT statement that selects from the table.

No, the only way to guarantee result order is to specify ORDER BY in the
SELECT statement that selects from the view. Although ORDER BY can be
specified in a view along with TOP, it will not guarantee ordering.

Note that it is likely, but not guaranteed, that you will get results
ordered in sequence by the table's clustered index in a trivial query with
no joins or where clause. However, it's risky to rely on behavior because
it may change between SQL Server service packs and releases.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"D." <d@d.com> wrote

Hi Davide,

The table is unordered set and you have to explicitly use ORDER BY if you
need to retrieve data in particular order. Even a view will not work, as you
cannot use ORDER BY in a view, unless the TOP clause is specified (and in
that case the ORDER BY is used only to determine the rows returned by the
TOP clause, you still have to use ORDER BY when selecting from the view to
return ordered set).

Since your software controls the creation of the temporary table, it may be
easier to add <sort> column to the table and to populate with values based
on the parameters. Then the report engine can still use static SQL, like
"SELECT <columns> FROM #MyTable ORDER BY sort".

HTH,

Plamen Ratchev
http://www.SQLStudio.com

D. (d@d.com) writes:
As the others have said: the only way to be guaranteed to get an ordered
result is to use ORDER BY. There is no way around that.

However, you could create your table with a rowno column which you
populate with the row_number() function (available from SQL 2005), and
the report engine could do "SELECT ... FROM #MyTable ORDER BY rowno".


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks to everyone,
I think the I will pass the order by clause to the report engine.

On Nov 27, 1:33 pm, "D." <d...@d.com> wrote:
Yes it is. If you want to show data in reports, better order the
result there