Hi,

I am using MSDE. I see in my table my records are order by my primary key. I am wodering is it possible that you have records in your table in the sequences in which they get inserted?

Thanks

Only if you have a time stamp at the time of insertion or an auto increment field.



Sal

A table in a relational database does not have "records", they are called "rows". These rows are members of the set represented by the table, be it a 'real' table as stored in the database, or a 'virtual' table that you construct via a query. Members of a set are, by definition, unordered.

It is possible using SQL to order the elements of the set; that is what the ORDER BY clause in a query does. The ORDER BY clause turns the set oriented nature of the table rows into a resultset which you see as a sequential list of rows in some physical order.

If you happen to notice that the rows of a table are in a certain order by default, that is interesting, but really something you can't depend on, as the query processor, absent any ORDER BY instructions, is free to return the rows in whatever order it feels like.

SQL Server, like other RDBMS's, allows you to place an index on a column of a table. This index allows the query processor to find a value in a row (and hence the row itself) in a table relatively quickly, for purposes such as JOINs or WHERE clause selections. Indexes in SQL Server are implemented as a tree structure which happen to order the items in the index. It's important to note that ordering of the values in an index is not a requirement; some RDBMS products implement their indexes as hash or other mapping structures; these provide no ordering information at all.

It happens that in SQL Server (MSDE) the data comprising the rows is stored in what's called the "clustered index". This is a special index which is ordered by its key (not necessarily the primary key of the table) and which contains all of the data in the table rows. Usually, when you display query results without any ORDER BY clause, the query processor decides that a simple scan of the clustered index is the most efficient way to present the data. The important thing to remember is that it doesn't have to be this way, and depending on the specifics of your query, the processor may choose another way to retrieve the data which would result in it being presented in a different order.

If you want to return the rows in a query in a certain order, say so explicitly.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com