Menu
what would be the simplest way to change every nvarchar column in a database to a varchar?
I personally would prefer nvarchar, but the data arch has specified that varchar must be used.
354
Solution. Use Virtual View to load the data to Data Vault, and in Virtual View, use the CONVERT() function of SQL Server to extract NVARCHAR(MAX) data as VARCHAR.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.
Here, to get you started:
Select 'Alter Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] Alter Column [' + COLUMN_NAME + '] VarChar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'
From INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'
This will generate all the needed alter statements for you (cut, paste, run).
Note that this does not take any constraints into account.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
