Vadim Tabakman

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".

 Prevent

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.

Posted by Vadim Tabakman Saturday, April 28, 2012 8:59:00 PM Categories: SQL
Copyright Vadim Tabakman
Rate this Content 0 Votes

Comments

Friday, October 9, 2015 4:53:10 AM
Jaksa
Gravatar

re: SQL Converting NChar to NVarChar

Tank you sir. You saved me a lot of trouble. :)

Friday, October 9, 2015 5:55:24 AM

re: SQL Converting NChar to NVarChar

Glad it helped Jaksa.

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register

Statistics

  • Entries (279)
  • Comments (1769)

Categories