September 15, 2012 at 3:07 pm
Hi,
Should reindexind be done on a table after a new column is added to the table..does it depend on if the table is heap or clustered.similarly immediately after a col is droped..should ne do reindexing
September 16, 2012 at 12:39 pm
September 16, 2012 at 3:54 pm
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 to
free 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?
September 16, 2012 at 3:58 pm
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?
Because if you add a column, the new data may cause the clustered index to split pages which is a form of fragmentation that can slow up batch processing quite a bit.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2012 at 2:26 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply