Hi,

I migrated a database using backup/restore. On the new machine, a select * from one of the tables will not be in order of the index (a clustered index based on the column1, asc., it's an int 4, identity column). The sort order is the same on both machines.

The results returned by this table from a select * are different from the table on the other machine. On the new machine, the ordering does not begin at 1 for some reason, although it is sequential, there are 119 records at the beginning before it starts with 1. On the older machine, it starts with 1. Despite the fact that they shouldn't be using a select *, I guess some developers have used datasets that depend on the data being in this order. Using select top 10 returns the records in the correct order, I guess SQL Server inserts an order by clause somewhere for this.

Any ideas on how to get the new table to follow the order of the original table?

So DBCC DBREINDEX (mytable, '', 70) fixes the problem...

Is there a way to do this automatically on restore?

Doesn't seem to be an option on restore, but I scripted it to occur just after using:

EXEC sp_MSforeachtable