0

I was reding this post by Pinal Dave and found this line:

Unindexed tables are good for fast storing of data. Many times, it is better to drop all the indexes from table and then do bulk of INSERTs and restore those indexes after that.

Is that really an effective technique in the case of clustered indexes? I mean, won't it be an overhead then to recreate all those indexes again? And I have also read that records are physically stored in the same order as that of logical records when clustered indexes are used. Then how will this affect the physical storage of records when we drop the index and restore them later?

Aparan
  • 1,263
  • 3
  • 12
  • 17

1 Answers1

1

He's partially correct.

Tables without clustered indexes (known as "heaps") are useful for staging tables for bulk loads. These staging tables are not your final tables. For example, the data you load may be data you already have so you only need to find new, changed and deleted records for your final table.

And yes, it is an overhead to recreate the clustered index. When dropped, data will be allocated wherever. When rebuilt, it will rearrange data on disk.

gbn
  • 422,506
  • 82
  • 585
  • 676