how to add field size without dropping index in sql server

Dushyant Patel 1 Reputation point
2021-01-25T19:26:58.61+00:00

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)?

Transact-SQL
Transact-SQL
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
    2021-01-25T22:31:09.233+00:00

    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
    2021-01-26T06:15:37.63+00:00

    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.

    Regards
    Echo


    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