how to add field size without dropping index in sql server

Dushyant Patel 1 Reputation point

I've Field name called SPS and data type CHAR(6) as of current in our DB but we wanted to change it to CHAR(10) and there is lot of indexes on it and Primary key attached to it along with multiple table same field. How would I change data type to CHAR(10)?

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points MVP

    As Tom says, you can't. A possibility could be to add a new column, copy data over, add a unique index on the new column, retarget all foreign keys to refer to this column, drop the primary key, drop the column and rename the new column, and promote the unique index to be a primary key.

    Whether this is actually is easier I am not sure.

    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points

    Hi @Dushyant Patel ,

    Welcome to the Microsoft TSQL Q&A Forum!

    As other experts have said, you cannot change the field size without deleting the index.

    you can't define the column in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index isn't the result of a PRIMARY KEY constraint. See the example in the short section titled Altering a Column Definition.

    Changing the size of a column

    If you have any question, please feel free to let me know.


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our [documentation][2] to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments