a little sql problem

Question

If the following SQL statements are executed in the order shown:

   CREATE TABLE orders 
        (order_num INTEGER NOT NULL, 
         Buyer_name VARCHAR(35), 
         Amount NUMERIC(5,2));
   CREATE UNIQUE INDEX idx_orderno ON orders(order_num);

whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values?

Answer 1

Relational databases don't work that way. An index is a means to select values doing less work (this is, not having to scan all rows to find a value), it's not a means to order the values in a result set.

In fact, most (all?) relational databases guarantee only one thing related to order, and that is that there is no guaranteed order if no order by clause is present in the query.

So, if you want ordered results you have to use the order by clause, like

select * from orders order by order_num

By default, order by column will order in ascending order. If you want it the other way around you can use order by column desc. order by column asc also exists.

To order by multiple columns you specify them separated by comma

select * from orders order by order_num asc, name desc

Answer 2

You need to specify that in the query, e.g.

SELECT * FROM orders ORDER BY order_num ASC;

There's no portable way to order by default for a certain table.

Answer 3

Unless you specify an ORDER BY clause in an SQL SELECT statement, there is no guarantee in which order the rows are returned. A given database may return them in the order of the first UNIQUE INDEX but this is not certain, and may even change over time for the same database.

To be sure of the order in which your rows are returned, always specify an ORDER BY clause in your SELECT statement. Like so:

SELECT * FROM orders ORDER BY order_num;

Answer 4

No, the rows will be displayed in an undefined order depending on the actual database software--usually the order they are stored in the database.

The unique index does have a sort order, but that is just to control how the index is stored and how efficiently it is used.

The correct way to do this is to add the desired sort to all your queries:

SELECT * 
FROM [orders] 
ORDER BY [order_num]

Answer 5

In general no, there is no reason for it.