CirquedeSQLeil (8/25/2009)
This was a good question. I would have initially ticked the truncate table option but ran some testing and research first. Though accurate, I am not sure I agree with dropping the clustered index - I think I would rather drop the nonclustered index and leave a clustered index on the table.Thanks for the good question.
Hi Jason,
Thanks for the kind words.
The answer option with a clustered index included dropping of same because it had to be assumed that the schema should remain unchanged. In an idea world, a table with no clustered index doesn't just happen; someone has thought about it long and hard, discussed it with a colleague, and then made the decision to not have a clustered index on the table. Such a decision should not be unmade on a wimp. Though I do of course know that this ideal world often has little to no resemblence of reality. 😉
In the large majority of cases, tables should have a clustered index. There are exceptions to this rules, but they should be a small minority.
And though not the real reason for prefering a clustered index on each table, a nice added benefit is that you can henceforth reclaim lost space by simply rebuilding the index. With Enterprise Edition, that can even be done without downtime, by using the online rebuild feature!