"By definition, a clustered index will maintain the order."

Not to be pedantic here, but this is not true.

It is true that a clustered index organizes the data rows in physical order
of the clustered index key, but it is a mistake to assume that this order
will preserved. The optimizer is free to return data rows in any order it
pleases, absent any ORDER BY clause, even if a clustered index exists. You
should never assume an order of rows unless you explicitly state it.

--
Jeff Mason Custom Apps, Inc.
[Email Address Removed] Message-----
From: John Obie [mailto:[Email Address Removed] Friday, April 25, 2003 5:01 PM
To: LazyDBA.com Discussion
Subject: Re: re-sequence steps


Another approach, which will keep step and sub_step in
order, would be to create a clustered index on the
table. By definition, a clustered index will
maintain the order.

For example:

create table my_test (step int, substep int)

insert into my_test values (1,1)
insert into my_test values (1,2)
insert into my_test values (2,1)
insert into my_test values (2,0)
insert into my_test values (3,1)
insert into my_test values (3,2)
insert into my_test values (3,4)
insert into my_test values (4,1)
insert into my_test values (4,3)

create clustered index foo on my_test(step, substep)

select * from my_test

step substep
----------- -----------
1 1
1 2
2 0
2 1
3 1
3 2
3 4
4 1
4 3

MS Sql Server LazyDBA home page