Skip to Content.
Sympa Menu

freetds - Re: [freetds] unicode

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] unicode
  • Date: Wed, 23 Jul 2008 15:50:54 +0200

>
> 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

Attachment: smime.p7s
Description: S/MIME cryptographic signature




Archive powered by MHonArc 2.6.24.

Top of Page