v5d1.ch (9/16/2012)
Thank you joy smith.that article explains lot but i am still bit confused.i understood why we need to rebuild whenever we drop a column.it is beacuse we have tofree up the space used by dropped col but i donot understand why we need to rebuild index when a new col is added.could you please clarify?
You probably don't need to rebuild indexes unless:
your table has a clustered index (see Jeff's comment above)
or
(
your table is a heap
and
(
the new column is a fixed-width data type
or
(
the new column is NOT NULL
and
the new column has a DEFAULT CONSTRAINT associated with it
)
)
In the two bolded scenarios a similar (but not quite the same) thing to what Jeff described with the clustered table occurs with the heap in that rows no longer fitting on the page have to be moved to new pages and forwarding and back pointers are created. Having too many forwarded pages in a heap can also be bad for performance and in SQL 2005 the problem is hard to get rid of (no pun intended). Heaps are not great in general for this reason so if you're dealing with them consider creating a clustered index on them, they are much easier to maintain than heaps.
edit: added bolding
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato