SQL2k
There are realy 2 parts to the question.
1) Clustered Indexes.
I have a clustered index made up of a couple of fields.
What I have noticed is that the data is not sorted in the clustered index order UNLESS the index is rebuild using DBCC DBREINDEX. Is this correct?
The sp to populate the table does a truncate table
then a Insert/Select to the table.
I was expecting the the index to be 'maintained' by the db when the new data got inserted.
Can someone explain whats happening?
2) Identifier and rank/sort order
I have 2 sp's. The first one creates table1 with a clustered index so the data is in a particular order(it also runs a DBCC DBREINDEX table1)
The second sp creates table2 using table1 as a input
i.e.
truncate table2
Insert Table2 (fields)
Select fields from table1
The table2 has a identifier which i'm using for ranking
When I run a query on table2, the data is a different order to table1 when I sort on the identifier.
What can I do to ensure that table2 is always in the same order as table1? (i thought by creating table1 with a clustered index, sql will keep the data in the same sequence for table2)
Most times when I re-run sp2, the data is then re-ranked correctly.
I'm using a datawarehouse tool that basically gereates all the code for sp's/views. It doesn't have the ability to have a 'order by' clause in the sp/views that it generates. I could write my own code, but I am trying to use the standard functionality of the datawarehouse by trying to compensate in SQL by creating the 2 tables.
Is there a reliable way of trying to 'rank' this data in a specific order without a order by clause?
Thanks
Cheers
Fred
There are realy 2 parts to the question.
1) Clustered Indexes.
I have a clustered index made up of a couple of fields.
What I have noticed is that the data is not sorted in the clustered index order UNLESS the index is rebuild using DBCC DBREINDEX. Is this correct?
The sp to populate the table does a truncate table
then a Insert/Select to the table.
I was expecting the the index to be 'maintained' by the db when the new data got inserted.
Can someone explain whats happening?
2) Identifier and rank/sort order
I have 2 sp's. The first one creates table1 with a clustered index so the data is in a particular order(it also runs a DBCC DBREINDEX table1)
The second sp creates table2 using table1 as a input
i.e.
truncate table2
Insert Table2 (fields)
Select fields from table1
The table2 has a identifier which i'm using for ranking
When I run a query on table2, the data is a different order to table1 when I sort on the identifier.
What can I do to ensure that table2 is always in the same order as table1? (i thought by creating table1 with a clustered index, sql will keep the data in the same sequence for table2)
Most times when I re-run sp2, the data is then re-ranked correctly.
I'm using a datawarehouse tool that basically gereates all the code for sp's/views. It doesn't have the ability to have a 'order by' clause in the sp/views that it generates. I could write my own code, but I am trying to use the standard functionality of the datawarehouse by trying to compensate in SQL by creating the 2 tables.
Is there a reliable way of trying to 'rank' this data in a specific order without a order by clause?
Thanks
Cheers
Fred