SQL Converting NChar to NVarChar

While designing a database using MSSQL, I inadvertantly left some fields using the default NChar data type.

The downside to using this data type, is that you specify the max number of characters for that field and when you put text into it, it pads it with spaces.  This means more data coming back with SQL queries and also means it’s a pain to compare text strings.

I needed to convert this field to a NVarChar data type, but when I did, I had issues saving the table.

It turns out you need to disable an option in SQL Management Studio.  In SQL Management Studio, click on the Tools menu then select Options.  Click on the “Designer” node on the left and then you’ll find an option that you need to uncheck named “Prevent saving changes that require table re-creation”.

Once you have done this, you need to trim the values in your field so they aren’t padded.  I found this query worked to trim the field.

UPDATE [TableName]
SET [FieldName]=LTrim(RTrim([FieldName]))

That’s it.