I have a 38 million row table that I periodically truncate and refill. I disable the indexes (non-clustered) when I do this. But when I included the Execution Plan I noticed SQL Server 2005 is still updating the indexes as I insert the data. How can this be?
Paul White has asked for some documentation. I created a test script to simplify the event. In summary, this is what I found. Consider a table with a clustered index and 2 non-clustered indexes. If the 2 non-clustered indexes are disabled, they are ignored when inserting data as expected. But if the clustered index is also dropped, then the 2 non-clustered indexes ARE updated during the insert. I am only allowed 2 links so I included my DDL script and the execution plan that shows the disabled indexes being updated.