Post reply

Can I change colum width of varchar in production

  • October 17, 2014 at 3:20 am

    #291029

    Hi,

    I keep getting requests to increase the width of a varchar colum every now and then.

    I want to ask if its perfectly ok when you have active users connecting to the application to do this?

    Just want to be on the safe side.

    thanks,

  • October 17, 2014 at 3:46 am

    #1753078

    This is something that might take some planning.

    How big is the table?

    Is the column used in indexes or in constraints?

    Be aware that changing the width of the column might update all of the rows in the table and thus will lead to a lot of logging.

    Furthermore, the table is also locked with a schema lock during the update.

    More info:

    ALTER TABLE (Transact-SQL)

    (check the Remarks section)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 17, 2014 at 3:50 am

    #1753082

    As far as I know, increasing the width of the column is a metadata only operation and doesn't require updating all the rows.

    Reducing the width of the column does.

    -- Gianluca Sartori

  • October 17, 2014 at 4:22 am

    This was removed by the editor as SPAM

  • October 20, 2014 at 3:16 am

    #1753431

    Thanks you folks.

    I did this through T-SQL alter table statement and users were unaffected with the operation.

    thanks,

  • October 20, 2014 at 9:40 am

    #1753541

    I'm glad you were able to successfully do what needed to be done, but I wouldn't recommend making a habit of just making changes like this in production. You should be testing things like this before doing them in production.

    You also should be monitoring performance because if you have a situation where one of your varchar values gets updated to take advantage of the new size, you could get page splits.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 20, 2014 at 11:13 am

    #1753585

    Jack Corbett (10/20/2014)

    You should be testing things like this before doing them in production.

    +1000

    -- Gianluca Sartori

  • October 20, 2014 at 4:26 pm

    #1753636

    Increasing the size of a [n]varchar column isn't directly a problem.

    But, if you think people will go back and increase the length of those columns in a significant number of existing rows, monitor the table closely for page splits and rebuild the table when necessary.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 8 posts - 1 through 7 (of 7 total)

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