How to change the datatype of column without deleting it indexes; [closed]

Posted on

Question :

I am looking for changing the datatype of a field from nvchar(20) to nvchar(MAX), but it retains the below message.

my questions here:
Deleting the index will affect on the data?
Can I restore the index after deleting it and changing the data type?
Is The index in the screenshot below the only index that will be deleted?

I will be very appreciated for any help 🙂

Best,
Lubna

enter image description here

Answer :

The data type of columns included in an index cannot be changed unless
the column is a varchar, nvarchar, or varbinary data type, and the new
size is equal to or larger than the old size.

With this info from the knowledge base a suggestion could be to alter column to a nvarchar(4000) or something like that.
Remember that you can’t index a varchar(max) or nvarchar(max) field.

What you should do is right click on the index and script the create statement to a new query window.
then drop the index
make the column alteration
then run the create index statement

I would caution you that nvarchar(max) will be quite an expensive choice as the memory grant required for every query you run will increase dramatically, if nvarchar(100) (or 200) is sufficient that would be a better choice

Leave a Reply

Your email address will not be published. Required fields are marked *