Thank you both Gail and Steve for your response. This is not a one-time thing, we have to do this at every few months or whenever needed, so definitely,...

Thanks a lot Sean, Alan for responding. Especially Alan for taking your lunch time to write the code. I thought I provided enough information, but I guess not, sorry. ...

Thanks for responding. I need to execute the stored procedure in batches either through a while loop or some kind of table with identity column, but I don't know...

Thank you all for taking your valuable time to provide your thoughts and inputs. I'm sorry if I have not been cleared in my question 2 and 3, so...

Okay, I see, thanks Sean.

Thanks a million Abhijeet for helping me, you're so right. It works now. Really appreciated.

I found that if I added another field to the update statement, it would update that new

field fine. It just won't update the field that is using the variable, even...

This table has one non-unique clustered, so it couldn't be a heap (a heap is a table has no index, correct?).

I'm using the avg_fragmentation_in_percent in dm_db_index_physical_stats. I also compared it...

Thanks much. Your explanation makes sense. I feel better now :).

oh, and yes, it always delete the same number of records as the number were inserted. This is because once those records for that particular companyid have moved to...

The int is the companyID (it's probably was an identity from the source we get the data from, but not when it is insert into our table), and the companyID...

Thanks Gail. The non-unique clustered is on the companyID(integer) key, and insertion and deletion is done at each of these companyid (at the where clause). I'm glad that...

Okay, I see. Thanks a lot for the explanation Gail.

Thanks Lowell for the link. It is very helpful. I couldn't understand completely his code,

but what helped me was to look into SQL Server 2008 Full Text Indexing option, which...

Never mind, i got it.