none
How to increase a column size in a table ?

    Question

  • Greetings,

    I have 10 cr of data in a table in one critical database. 

    Problem :  Here i want increase the column size in that table - sql server 2008

    I am using below command : 

    ALTER TABLE MyTable ALTER COLUMN [ABC] VARCHAR(1200)

    Please suggest me how will this impact on my data and best approaches to do or any alternatives. 

    Quick help must be appreciated. 

    Thanks,

    Prasad


    Tuesday, July 17, 2012 7:07 AM

Answers

  • Hi !

    The better approach would be following steps;

    1) Create New table with same structure as 'YourTable' table.
    
    SELECT * INTO YourTable_New FROM YourTable WHERE 1 = 0
    
    2) Drop 'YourColumn' Column and its constraint.
    
    ALTER TABLE YourTable_New
    DROP COLUMN YourColumn
    
    3) Create New Column 'YourColumn'
    
    ALTER TABLE YourTable_New
    ADD YourColumn VARCHAR(MAX)
    
    4) Rename ' YourTable' table to YourTable_old' and ' YourTable_New' to ' YourTable'.
    
    sp_rename ' YourTable' , ' YourTable_old'
    GO
    sp_rename ' YourTable_New' , ' YourTable'
    
    5) Insert all rows from 'YourTable_old' to 'YourTable'
    
    Other Approach would be;
    
    1) Simply Add New Column 'YouColumn_New' to attributes table.
    
    ALTER TABLE YourTable
    ADD YourColumn_New VARCHAR(MAX)
    
    2) Update YourColumn_New with YourColumn using CAST;
    
    2) Drop 'YourColumn' Column and its constraint.
    
    ALTER TABLE YourTable
    DROP COLUMN YourColumn
    
    3) Rename the column 'YourColumn_New' to 'YourColumn'.
    
    sp_rename 'YourColumn_New' , 'YourColumn'




    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

      
    Tuesday, July 17, 2012 7:28 AM
    Answerer
  • From your ALTER TABLE script I assume you are altering a variable length column. SQL Server do not touch any rows when you increase the length/size of a variable length column , and this will be a meta data only change. So the ALTER TABLE statement is quick in this scenario (i.e. number of rows do not impact the ALTER COLUMN statement here on increasing the length of a variable data type column).


    Krishnakumar S


    Tuesday, July 17, 2012 7:47 AM

All replies

  • I am not a real expert, but I will try to give you some answer.

    As long as this column is not used as the primary key, you can do it. If it is the primary key, all foreign keys referring to it, also need to be changed. Judging from the data type, I would say it isn't the primary key. If it is the primary key, you should drop the foreign keys first, then drop the primary key, change the datatype and finally recreate the primary key and the foreign keys.

    Hopefully, you didn't include this column in an index, becauses indexes on large columns are not a good idea. An index on a column of, say 1000 bytes, means that at most 8 indexes will fit on a fully filled index page. This means that the depth of your index tree becomes quit large, slowing down index searches. I do not know if you have to rebuild the indexes if this column is indexed, but I can look it up for you if you like.

    If you regularly update this column, adding data to it, the record on the data page will become larger. All records on the page succeeding it, will also have to be moved. In the worst case, an updat will mean that the contents of the page doesn't fit anymore, causing a page split, which means that also the index pages referring to the records on those pages will have to be updated. But that doesn't necessarily apply to large VARCHAR columns, but to all variable length columns.


    Tuesday, July 17, 2012 7:25 AM
  • Hi !

    The better approach would be following steps;

    1) Create New table with same structure as 'YourTable' table.
    
    SELECT * INTO YourTable_New FROM YourTable WHERE 1 = 0
    
    2) Drop 'YourColumn' Column and its constraint.
    
    ALTER TABLE YourTable_New
    DROP COLUMN YourColumn
    
    3) Create New Column 'YourColumn'
    
    ALTER TABLE YourTable_New
    ADD YourColumn VARCHAR(MAX)
    
    4) Rename ' YourTable' table to YourTable_old' and ' YourTable_New' to ' YourTable'.
    
    sp_rename ' YourTable' , ' YourTable_old'
    GO
    sp_rename ' YourTable_New' , ' YourTable'
    
    5) Insert all rows from 'YourTable_old' to 'YourTable'
    
    Other Approach would be;
    
    1) Simply Add New Column 'YouColumn_New' to attributes table.
    
    ALTER TABLE YourTable
    ADD YourColumn_New VARCHAR(MAX)
    
    2) Update YourColumn_New with YourColumn using CAST;
    
    2) Drop 'YourColumn' Column and its constraint.
    
    ALTER TABLE YourTable
    DROP COLUMN YourColumn
    
    3) Rename the column 'YourColumn_New' to 'YourColumn'.
    
    sp_rename 'YourColumn_New' , 'YourColumn'




    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

      
    Tuesday, July 17, 2012 7:28 AM
    Answerer
  • thanks Hasham

    Thanks for your immediate answer. 

    I will follow your steps however Is there any alternative solution without creating a new column or table and renaming. I want to alter my column without changing anything. 

    Please suggest. 

    Tuesday, July 17, 2012 7:40 AM
  • Thank you chris for your inputs. 
    Tuesday, July 17, 2012 7:40 AM
  • From your ALTER TABLE script I assume you are altering a variable length column. SQL Server do not touch any rows when you increase the length/size of a variable length column , and this will be a meta data only change. So the ALTER TABLE statement is quick in this scenario (i.e. number of rows do not impact the ALTER COLUMN statement here on increasing the length of a variable data type column).


    Krishnakumar S


    Tuesday, July 17, 2012 7:47 AM
  • This is right as long as you increase the length.

    When you decrease it, then each row must be checked and truncated as needed.

    Tuesday, July 17, 2012 7:52 AM
  • Alter script doesn't have any effect on your data if you plan to increase the size. There may be truncation issue you might run into if you decrease the size. 

    So ALTER is easy and fast approach instead of loading it into a table and then loading back


    Anand

    Tuesday, July 17, 2012 11:03 AM
  • You may find this quiz question and answers related to your question and providing some insights:

    http://beyondrelational.com/quiz/sqlserver/dba/2011/questions/180/data-storage-problem.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, July 17, 2012 1:29 PM
    Moderator
  • I appreciate each and every individual for your valuable answers.

    thanks all...

    Wednesday, July 18, 2012 11:04 AM
  • I don't know if your problem is solved, but if this is the case, can you mark the reply that was most helpful as the answer?

    In that case, your thread will not show up anymore in my filter, making it easier for me to concentrate on problems that are not solved yet.

    Thank you.


    Wednesday, July 18, 2012 11:11 AM