0

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

lubna.najeh
  • 1
  • 2

2 Answers2

2

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.

MBuschi
  • 4,835
  • 1
  • 6
  • 17
0

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

Stephen Morris - Mo64
  • 4,656
  • 1
  • 10
  • 18