I just learned the hard way how to "move" rows from one table to another 3x faster. I'm migrating rows from a heap table with a nonclustered PK on a guid. I was doing a "select top(100) guidkey from Attachments", and using the set of keys to perform the convertAndInsertIntoNewTable/deleteFromOldTable process. Turns out the select top(100) was in guid order instead of heap row order. The disk head was jumping all over the heap to get the next 100 rows. I added a nonPK column to the SELECT to force heap order and now its fast.

My mindset was on "Heap" and I totally forgot sqlserver would choose a covering index even though I had no ORDER BY clause.