Collation question....

I did a test,
create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS)
create table #b(char_set2 varchar(50)collate Latin1_General_BIN)
insert into #a values ('collate')
insert into #b values ('collate')
go
select * from #a where char_set1='Collate'
go
select * from #b where char_set2='Collate'
I see the displayed execution plan side by side, Case insensitive is
actually achieving the same speed as case sensitive,
even though case sensitive collation query (#a) did not return any row. If
performance is not an issue, why is most of db programmers
use collation Latin1_General_BIN instead of Latin1_General_CI_AS then?
Actually,it might help the performance if we use case insenstive,
this way we don't need to upper the column to force insensitive search.What makes you think that most programmers use "Latin1_General_BIN"?
The default US/Western collation for SQL Server 2000 is
Latin1_General_CI_AS.
Choose the collation that best meets your requirements. If your
searches are case insensitive then you should consider using
case-insensitive collations.
David Portas
SQL Server MVP
--|||Someone told me that case sensitive database is faster than case insensitive
database in some cases.
of course when you do case insensitive search, we should use
Latin1_General_CI_AS so you don't have to do upper the column.
But assume there is a table called "table1" and we always input upper-cased
data onto table.
so when you do a search, run this query against both collations:
select * from table1 where column1 like 'JOHN%'
in this case, is case sensitive collation faster?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109794003.686267.290350@.l41g2000cwc.googlegroups.com...
> What makes you think that most programmers use "Latin1_General_BIN"?
> The default US/Western collation for SQL Server 2000 is
> Latin1_General_CI_AS.
> Choose the collation that best meets your requirements. If your
> searches are case insensitive then you should consider using
> case-insensitive collations.
> --
> David Portas
> SQL Server MVP
> --
>|||IT IS (SLIGHTLY) FASTER IN SOME CASES. IT IS WAY MORE TROUBLE TO DEAL WITH
IN ALMOST ALL CASES.
SO YOU STORE ALL DATA UPPER CASE? GROSS. THAT IS THE ONLY WAY TO MAKE THE
CASE SENSITIVE STUFF WORK BETTER, BUT THEN ALL OF YOUR OUTPUT LOOKS LIKE YOU
ARE MAD AT THE USER. IT WILL BE A BIT FASTER, BUT BEFORE I SET MY ENTIRE
DATABASE TO CASE INSENSITIVE, I WOULD RECONSIDER
UNLESS THIS IS NOT A USER ORIENTED APPLICAITON, MY ADVICE IS TO STORE THE
DATA AS YOU NEED IT, IN THE BEST POSSIBLE FORMAT, AND CHOOSE A COLLATION
THAT WORKS BEST. CASE INSENSITIVE, I mean, case insensitve is clearly the
best.
You can choose a binary collation for a single column, which may or may not
be good enough for your usage
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23gqmeU2HFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Someone told me that case sensitive database is faster than case
> insensitive
> database in some cases.
> of course when you do case insensitive search, we should use
> Latin1_General_CI_AS so you don't have to do upper the column.
> But assume there is a table called "table1" and we always input
> upper-cased
> data onto table.
> so when you do a search, run this query against both collations:
> select * from table1 where column1 like 'JOHN%'
> in this case, is case sensitive collation faster?
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109794003.686267.290350@.l41g2000cwc.googlegroups.com...
>