none
Production table : Add Column RRS feed

  • Question

  • Hi Experts,

    I have a table that

    1. Receives massive inserts

    2. Currently contains 190 million records

    3. Replicated to 2 servers

    4. Contains a Primary Key, 3 foreign key references , 3 default constraints

    The new column to be introduced is a BIT column. The change need to be made in production and am a bit nervous. 

    Could you please suggest best practices for performing such a change.

    Thanks

    Priya

    Thursday, November 1, 2018 11:29 AM

Answers

  • There should be no problem simply running ATLER TABLE and adding the column to the end of the table.  There is no reason for concern.

    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Thursday, November 1, 2018 11:46 AM
    Moderator
  • The new column to be introduced is a BIT column. The change need to be made in production and am a bit nervous. 

    Could you please suggest best practices for performing such a change.

    The change will be a meta-data only operation if the new column allows NULL or if you add a NOT NULL column with a DEFAULT constraint with SQL Server Enterprise (or Developer) edition. Only a brief schema modification lock will be needed on these cases.

    However, in lesser editions, adding a new NOT NULL column with a default constraint will require a schema modification lock to be held for the duration of the operation while all rows in the table are updated with the new default value. Other activity against the table will be blocked until it completes.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Visakh16MVP Thursday, November 1, 2018 11:55 AM
    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Thursday, November 1, 2018 11:46 AM
  • Yes the column allows NULL so I can add it regardless of the volume of data correct. 

    Correct.

    Also I need to drop an existing column from the table which is also NULL. I hope this operation won't cause any downtime. 

    This is a meta-data only operation regardless of edition. Be aware that the space for the dropped column won't be reclaimed until the clustered index is rebuilt.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Friday, November 2, 2018 11:26 AM

All replies

  • ALTER TABLE tbl ADD col BIT..... Should be defined with as default value?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 1, 2018 11:35 AM
    Answerer
  • There should be no problem simply running ATLER TABLE and adding the column to the end of the table.  There is no reason for concern.

    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Thursday, November 1, 2018 11:46 AM
    Moderator
  • The new column to be introduced is a BIT column. The change need to be made in production and am a bit nervous. 

    Could you please suggest best practices for performing such a change.

    The change will be a meta-data only operation if the new column allows NULL or if you add a NOT NULL column with a DEFAULT constraint with SQL Server Enterprise (or Developer) edition. Only a brief schema modification lock will be needed on these cases.

    However, in lesser editions, adding a new NOT NULL column with a default constraint will require a schema modification lock to be held for the duration of the operation while all rows in the table are updated with the new default value. Other activity against the table will be blocked until it completes.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Visakh16MVP Thursday, November 1, 2018 11:55 AM
    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Thursday, November 1, 2018 11:46 AM
  • Hi Sir,

    Yes the column allows NULL so I can add it regardless of the volume of data correct. Also I need to drop an existing column from the table which is also NULL. I hope this operation won't cause any downtime. 

    SQL Server standard edition 2012.

    Thanks

    Priya

    Friday, November 2, 2018 10:19 AM
  • Yes the column allows NULL so I can add it regardless of the volume of data correct. 

    Correct.

    Also I need to drop an existing column from the table which is also NULL. I hope this operation won't cause any downtime. 

    This is a meta-data only operation regardless of edition. Be aware that the space for the dropped column won't be reclaimed until the clustered index is rebuilt.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Priya Bange Friday, November 2, 2018 3:17 PM
    Friday, November 2, 2018 11:26 AM