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]

Regards, 

--jkl