• sjimmo (6/23/2010)


    Nick,

    When you issue a select * from tablename on a table without a PK or clustered index, the data should be returned in the order that it appears in the table using the collation order. If there is a clustered index on the table it will return the data in the order of the PK.

    Neither of those statements is true (not even sure what the "order that it appears in the table using the collation order" actually means). If you issue a SELECT statement without ORDER BY then the ordering of rows returned is undefined[/i] and could even be different when you execute the same SELECT again even if the data has not changed. You cannot guarantee to return rows in any specific order unless you use ORDER BY. This is true even when the table has a clustered index - a fact that's often very easy to prove for yourself if you try it out.

    There is no guarantee that an IDENTITY column will be assigned in a particular order for a bulk insert. If you create the IDENTITY column after the insert then there is no guarantee that the order of the IDENTITY values will match the order rows were inserted. In fact the row insertion order isn't preserved or recorded anywhere in the table or index structure.