Actually doing it is not hard, if there's a simple rule as to which row to keep. I'm sure once the schema's posted there'll...

Something for you to think about.

If two rows have duplicate values for those 4 columns, but different values for the other three, which row would you want to keep and...

Do note that snapshots cannot be backed up. If one runs out of space it immediately becomes suspect and has to be dropped. Also, they're not free, there's an IO...

That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially used until...

Hang one, just noticed what you said in the first post. "fetch hundreds of rows one by one"

Are you indeed retrieving one row, then doing something with it, then the...

Unless you're using read uncommitted, you are locking...

If you look at the script that management studio GUI runs, you'll notice that it's dropping and recreating the table for changes like that. It doesn't just alter the table,...

Are you doing the select WITH NOLOCK or are you explicitly setting the READ UNCOMMITTED isolation level?

How did you change the length/type of the columns? ALTER TABLE or via the management studio GUI?

The GO is not necessary and, if this is for a stored proc, shouldn't be used.

Well, think about it logically.

If you put the create table before the drop table, then you'll be dropping the table that you just created.

If you put the create inside the...

amoericke (12/15/2009)

---

The other idea was to change the clustered indexes to try to physically seperate the inserts from the deletes (the FIFO idea). This seems like it'd help, but...

Clustered index, yes. Primary key, Unique constraint, don't know. Maybe.

Edit: Removed incorrect info.

Start by only returning the columns that you really need. No SELECT *