Drop index in Begin Tran | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Drop index in Begin Tran

There is a table with more then 2 crore of records. I need to imporve the INSERT performance for the table. There are few non clustered index on the table. By droping the index before INSERT and recreating them after the INSERT in a
BEGIN TRAN statement give me any performance gain ?
Thanks. MNK.
If you drop an clustered index before any insert will gain the performance, but the issue will be while recreating the index again. So its better to REINDEX the table depending on the inserts/updates on that table, you can schedule reindex job during the nights to take care of performance. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi ya, there are no triggers/constraints on the table?
The datatypes are variable length when appropriate, no text/image columns?
explicit begin tran/commit wrapped around batches of insert statements? Cheers