how to add field size without dropping index in sql server

Question

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

Answer 1

You can't.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#changing-the-size-of-a-column

Answer 2

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.

Answer 3

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.