Agreed. The theta join is probably going to be slower than even a cursor\ loop.
I admit the original observation is perplexing but I have no time to investigate. Are you certain there are no gaps in the numbers? A non clustered index on (id, dt) will speed things up if there are lots of columns or the there are columns with lots of data (say wide string columns).
No, the order of columns in a table is immaterial. The order of columns in an index is critical.
Those are potentially large rows. 8 bytes for the datetime. 4 for the id. Up to 2650 bytes for the text columns. Rows like that would fit 3 to a page. Of course since these are VARCHAR they might be much smaller.
Guess I'm officially either a noob or a bum, you choose:
I installed the 2005 Server Mgmt Studio a while back and used it to create the indexes. I didn't see that, when modifying a column adding an index that column isn't selected...