Menu
I was creating a table in SQL Server CE and realized it doesn't support varchar.
Looking this up, I found that "non-Unicode text columns (varchar, char, text) and smallmoney are not supported; although nvarchar, nchar, ntext, and money are supported" as stated at MSDN.
Is this true? Why is this, exactly? It would seem that a compact database would support data types that take less bytes to store...I'm assuming it takes more space to save the Unicode characters.
What is the reasoning behind this?
817
You can use SQL varchar when the sizes of the column vary considerably, use varchar(max) when there are chances that string length might exceed 8000 bytes, use char when the sizes of the column are fixed and use nvarchar if there is a requirement to store Unicode or multilingual data.
varchar is used for non-Unicode characters only on the other hand nvarchar is used for both unicode and non-unicode characters.
Microsoft SQL Server Compact (EOL, See SQL Express) (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition.
The real reason you want to use NVARCHAR is when you have different languages in the same column, you need to address the columns in T-SQL without decoding, you want to be able to see the data "natively" in SSMS, or you want to standardize on Unicode.
It's probably because Windows CE is completely Unicode based and all of it's strings are stored that way.
148
I think they were trying to reduce their deployment footprint and simplify the interface. That and probably trying to avoid having to deploy more versions of the DLLs (unicode vs non-unicode versions).
And yes it is true that they only support Unicode.
But that does not automatically mean it takes 2 bytes to store. You can encode that at the database layer to basically strip the first byte when it is not needed. Many database engines do this as a means of compression on Unicode.
It just means that any entries that do use the two byte set have a slight overhead of an additional marker telling the engine that the sequence is using two bytes. Otherwise a single byte can still be stored to disk, and expanded as a part of the RowData read.
Most compact databases always use a form of runlength compression when they actually put bytes to disk in order to save space. The format you happen to see it in when it comes out from the engine rarely matches what is actually stored on disk.
21
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
