[freetds] unicode

James K. Lowden jklowden at freetds.org
Tue Jul 22 19:04:16 EDT 2008


Christos Zoulas wrote:
> 
> If you declare a column nstring instead of string then you should
> use N'literals' (and the other way around). If you don't the server
> will make an implicit conversion for you. 

The server makes an implicit conversion regardless.  

The string arrives at the server encoded as UCS-2, as you know.  It is
stored in a column according to the column's encoding, as you know. 
Whether or not you put an 'N' at the front.  

> It is very important to
> get the types right, because if you don't, then the implicit
> conversion will happen on each row, prohibiting the use of indexes,
> and have a severe performance impact.

Not if the client is correctly configured.  It's not as though there's a
mix of unicode and non-unicode in the column.  

1.  When  'literal' is stored as  varchar, it uses the single-byte
encoding. 
2.  When N'literal' is stored as  varchar, it uses the single-byte
encoding. 
3.  When  'literal' is stored as nvarchar, it uses USC-2.
4.  When N'literal' is stored as nvarchar, it uses USC-2.

There's no other choice.  

What you *can* do is goof up the client end.  You can get "correct"
results as far as the application is concerned while storing complete
garbage.  

If, say, your client is ISO 8859-1 and you use the N'UFT-8' form, FreeTDS
will convert your utf-8 data as though it were 8859-1.  Byte are bytes; it
will "convert".  The data arrive as "USC-2", seemingly.  When stored in a
varchar column, those bytes are converted again, according to the column's
collation.  (They won't be the text you sent, but they'll store OK.) 
Querying with another tool will show problems.  

Except that it won't completely work, because not every value in the
[0-255] range is valid ISO 8859-1.  But FreeTDS will complain about that. 


What am I missing?  I still don't see what effect the 'N' prefix can have.
 

--jkl



More information about the FreeTDS mailing list