is it required to run reindex on a table soon after a column is dropped or added?if yes then why?

Question

Post reply

is it required to run reindex on a table soon after a column is dropped or added?if yes then why?

v5d1.ch

SSC Enthusiast

Points: 178
More actions
September 15, 2012 at 3:07 pm

#281548

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Please see the below link.

http://www.simple-talk.com/blogs/2009/02/19/why-should-i-rebuild-a-table-after-dropping-or-adding-a-column/

v5d1.ch SSC Enthusiast Points: 178 More actions · Answer 2

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?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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 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?

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

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 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?

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