Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

is it required to run reindex on a table soon after a column is dropped or added?if yes then why? Expand / Collapse
Author
Message
Posted Saturday, September 15, 2012 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 4:22 PM
Points: 2, Visits: 26
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
Post #1359844
Posted Sunday, September 16, 2012 12:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,593, Visits: 2,661
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/
Post #1359909
Posted Sunday, September 16, 2012 3:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 16, 2012 4:22 PM
Points: 2, Visits: 26
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?
Post #1359924
Posted Sunday, September 16, 2012 3:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1359925
Posted Wednesday, September 19, 2012 2:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1361620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse