CLUSTERED INDEX on a Big Table on SQL Server

This table grows into a big table with 40+ m records (it seems to me that inserting log data daily into this staging table is not really necessary…but it was designed this way). Let’s create a clustered index on load_dt in this table.

CREATE CLUSTERED INDEX [IX_Loss_Mit_Actn_Log_Archive_Load_date] ON [dbo].[tbl_Loss_Mit_Actn_Log_Archive]
(
      [LOAD_DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

However, this can slow down the insert. So we modified the SSIS package to drop the index on the fly when inserting, and creating the index otherwise (again not sure if this will really improve the performance; I still think that removing the old data to a different table for archiving purpose is a cleaner solution).