Order in database

[srikantha_nagaraj]

Hi, Actually i want rows in the table in an order. How can i force the tables to keep that order? is there any command for that. Actually i am inserting the data row from asp.net front end.

[Lost User]

srikantha_nagaraj wrote:

Actually i want rows in the table in an order.

Sort the table.

srikantha_nagaraj wrote:

How can i force the tables to keep that order?

By definition, you can't. A table is a collection of rows that's saved in a random order. You can read them back and order by any field that's present within that selection. You could also add a custom field to indicate the order of the rows, but you'd still need to 'order by' that particular field. You can, alternatively, create a stored procedure that selects all your rows and sort them. Next, hide the table and give the user only access to your sproc. That would not stop anyone from re-sorting the dataset though.

I are Troll :)

[srikantha_nagaraj]

Thanks for the answer. :)

[stendulkar54]

Use Order By clause of SQL. It orders the output.

[J4amieC]

Eddy Vluggen wrote:

srikantha_nagaraj wrote: How can i force the tables to keep that order? By definition, you can't.

That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order.

[J4amieC]

Because it escapes me.

[i j russell]

That's not strictly true either! The only way to guarantee output order is using an order by clause.

[Lost User]

I added a five, that should even it out.

J4amieC wrote:

That's not strictly true, Tables will be stored in the order of their clustered index. This then forms the "default" order

The rows in a RDBMS are not interdependent, same goes for the columns. They might not be returned in the order that they're saved. Because there is no relation between the rows themselves (except that they belong to the same table) there is no order in the collection. This may vary depending on each implementation of course. Now, that's just theory that I swallowed a long time ago. I found that the most simple table in Access has a storage-order, and I have seen people abuse it. Most of the time, it's easier to add a custom column and sort on that. I've been reading up after your comment, and a clustered index[^] seems indeed to be the solution :)

Yes? :)

[Lost User]

That makes it also not strictly false :laugh:

public enum strictBool { true, false, EFileNotFound }

[Ashfield]

You have already asked this question and had correct replies. A database does NOT guarantee the order records are returned in UNLESS you have an order by clause. If there is a clustered index or primary key then this is the default order, but even then it is NOT guaranteed (certanly by SQL Server) as when you have multiple worker processe, unless an order by is specified he results are returned as each worker process completes, which MAY not be the expected order. If you hav any further doubts try reading up on it, there are thousands of articles on google

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP