Re: unicode

> 
> Christos Zoulas wrote:
> > On query analyzer, create a table with a
> > varchar column. Add an index to it. Then select * from table where
> > column = N'foo'. Look at the query plan. Try the same with 
> select * from
> > table where name = 'foo'.
> 
> Ah, OK, that I believe.  I'm not thrilled, but I'm not 
> surprised, either. 
> 
> 
> When *inserting* data the server must convert any (UCS-2) SQL 
> text to the
> column's encoding.  Else it can't be stored.  On selecting, it has to
> match the encoding, and it appears the 'N' prefix serves as a kind of
> hint.  
> 
> [soapbox]
> 
> A shame, really.  From what you're telling me, the matching logic is:
> 
> 	where cast(col as nvarchar) = N'literal'
> 
> instead of
> 
> 	where col = cast(N'literal' as varchar)
> 
> Positively brilliant.  I wouldn't have thought of that.  
> 
> I reminds me of two things. 
> 
> 1.  When Sybase brought out System 10, the new query 
> optimizer failed to
> match char with varchar.  If you had two tables, one keyed by 
> char and the
> other by varchar, it refused to use the index.  It scanned the table
> instead.  A few hardy souls complained, approximately 100% of the
> installed base.  Wouldn't you know, it got fixed pretty quick.  Before
> some of their queries finished.  
> 
> 2.  Query optimization is still a mere promise.  It is 
> neither a priority
> nor a goal.  The vendors think we need new query languages, 
> frameworks,
> piles of abstraction, XML whosis, ORM baloney, etc., 
> presumably because
> that's what the market demands.  Meanwhile people writing 
> complex queries
> get lots of practice because no two expressions of the same 
> idea yield the
> same query plan.  And don't forget to put that 'N' in there, kid.  You
> can't expect the machine to read your mind, you know!  
> 
> [end soapbox]
> 

On other end inserting is quite strange... just a small test

$ ./tsql -STESTSRV -U '' -P ''
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1> create table #tmp1(c nvarchar(10))
2> go
1> insert into #tmp1 values('§')
2> go
1> select * from #tmp1
2> go
c
§
(1 row affected)
1> insert into #tmp1 values('ю')
2> go
1> select * from #tmp1
2> go
c
§
?
(2 rows affected)
1> insert into #tmp1 values(N'ю')
2> go
1> select * from #tmp1
2> go
c
§
?
ю
(3 rows affected)
1>

As you can see it seems that not using N prefix string got converted twice, one
ucs2->single and another single->ucs2 (nvarchar use ucs2).

freddy77

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds