Clustered index

I have a few tables with a clustered index (SQL Server 2000).
When I open some of these tables, enter a new value into an indexed field
and reopen the table (actually click Run in a context menu), the table is
ordered by the indexed field.
In some other tables, the new value is not ordered and remain in the last
row.
Why is this difference between the tables and what is a proper behaviour of
the clustered index?
Thanks."Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23F%23A0ToEIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have a few tables with a clustered index (SQL Server 2000).
> When I open some of these tables, enter a new value into an indexed field
> and reopen the table (actually click Run in a context menu), the table is
> ordered by the indexed field.
> In some other tables, the new value is not ordered and remain in the last
> row.
> Why is this difference between the tables and what is a proper behaviour
> of the clustered index?
> Thanks.
>
This is really a feature of whatever application you are using to query the
table. When you "open" a table in Enterprise Manager for example it will
just query the table by selecting all columns and rows but it won't specify
an ORDER BY clause. That means the ordering is uspecified and will be
determined by whatever execution plan the server chooses at runtime. If
ORDER BY isn't specified but the table has a clustered index then there is a
fair chance that the rows will be returned in an order that matches that
index - although that's definitely not guaranteed.
If you want to be sure what your application is doing, then use SQL Profiler
to capture the statements that it uses and see if it includes an ORDER BY
clause.
Just remember that a table is an unordered set of rows. Unless you query it
using ORDER BY you should assume nothing about the order of rows returned.
--
David Portas|||While this is 100% true, it doesn't tell the whole story.
I have noticed the default order of rows being "sorted" on a non-ordered
select for years. At a casual view, this seems to actually be a sort, even
though it isn't. In particular, I've noticed this on freshly built tables
with (and without) clustered indexes and where the internal blocks used to
store the data were contigious. When clustered indexes are used, I've seen
the "sort" maintain itself after an insert.
At one point I was convinced this was reliable behavior, sadly, it isn't.
Sure looks that way though. I suspect it has to do with the physical order
the data was entered into the database and the way clustered indexes are
implimented.
So, it is not automatically a feature of something, it could be the order
the data is coming out of the database because of the order it was put in
and/or where the insert occured (because of the clustered index).
Jay
> This is really a feature of whatever application you are using to query
> the table. When you "open" a table in Enterprise Manager for example it
> will just query the table by selecting all columns and rows but it won't
> specify an ORDER BY clause. That means the ordering is uspecified and will
> be determined by whatever execution plan the server chooses at runtime. If
> ORDER BY isn't specified but the table has a clustered index then there is
> a fair chance that the rows will be returned in an order that matches that
> index - although that's definitely not guaranteed.
> If you want to be sure what your application is doing, then use SQL
> Profiler to capture the statements that it uses and see if it includes an
> ORDER BY clause.
> Just remember that a table is an unordered set of rows. Unless you query
> it using ORDER BY you should assume nothing about the order of rows
> returned.
> --
> David Portas
>