I need to convert a non-unicode SQL Server 2005 database to a unicode based database. I have hundreds of stored procs and of course the data is stored in varchar. I know that I need to change all the data types to the unicode equivalent (varchar to nvarchar) but don't I have to change how the stored procs are written or will they continue to work as before? I am trying to figure out what is necessary to change from non-unicode to unicode for a large database with many stored procs.
+2
A:
Yes, you need to update your data and stored procedures, but an important thing to remember is that you only need to change some of your columns to UNICODE. For anything that is "internal", you don't need to pay the UNICODE cost.
There is a lot of work to do for this change, but don't change everything blindly. I've been on the receiving end of that kind of change before, and it's painful. (Using nvarchar(1) to store 'y' and 'n' is stupid.)
Rob Garrison
2009-10-23 22:24:21
Note that in SQL Server 2008 R2, if you use data compression, you only pay the Unicode cost for actual Unicode data. The compression algorithm is very smart - it doesn't waste two bytes on ASCII characters, and it enables significant compression even on Unicode data by storing the offsets instead of the actual 2-byte characters. Add that on top of the dictionary and other compression you already get with data compression. Of course you pay a penalty for compression / decompression, so always test. Some blogs (first three hits): http://is.gd/4yleO
Aaron Bertrand
2009-10-23 23:37:40
The disappointment for me is that UNICODE compression is Enterprise-only. It's something SQL Server is behind on, and now they're catching up with a good solution, but they limit it to Enterprise (which I don't get to use).
Rob Garrison
2009-10-24 05:29:57