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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » is it required to run reindex on a table soon...

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

Rate Topic

Display Mode

Topic Options

Author

Message

v5d1.ch

Posted Saturday, September 15, 2012 3:07 PM

Forum 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

Joy Smith San

Posted Sunday, September 16, 2012 12:39 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 1,370, Visits: 2,289

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

v5d1.ch

Posted Sunday, September 16, 2012 3:54 PM

Forum 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

Jeff Moden

Posted Sunday, September 16, 2012 3:57 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 32,906, Visits: 26,788

v5d1.ch (9/16/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1359925

opc.three

Posted Wednesday, September 19, 2012 2:26 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 6,703, Visits: 11,733

v5d1.ch (9/16/2012)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1361620

« Prev Topic | Next Topic »

Permissions