Create table and default order by clause

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.

Re: Create table and default order by clause

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

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

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

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." wrote in message
news:CQz2j.6583$r62.2270@tornado.fastwebnet.it...
> 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.
>
>
>
>

Re: Create table and default order by clause

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 column to the table and to populate with values based
on the parameters. Then the report engine can still use static SQL, like
"SELECT FROM #MyTable ORDER BY sort".

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Create table and default order by clause

D. (d@d.com) writes:
> 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?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Create table and default order by clause

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

Re: Create table and default order by clause

On Nov 27, 1:33 pm, "D." wrote:
> Thanks to everyone,
> I think the I will pass the order by clause to the report engine.

Yes it is. If you want to show data in reports, better order the
result there