Hi All

I am facing following issue with SQL server 2000. If, while creating DB, I
choose collation as Chinese_PRC_BIN, SQL server becomes case sensitive.

e.g. if Table name is "MYTABLE", following query fails

select * from mytable

however, if collation is default (English) , then following query works
fine.

What is the issue here?

Regards
Ajay

Ajay,

you have chosen a binary sort-order. Since the binary value for 'T' is
different from 't' the database is effectively case sensitive.
Markus

But then, why does it work for database if collation is english?

<m.bohse@quest-consultants.com> wrote in message
news:1138091684.802799.211120@g44g2000cwa.googlegr oups.com...
> Ajay,
>
> you have chosen a binary sort-order. Since the binary value for 'T' is
> different from 't' the database is effectively case sensitive.
> Markus
>

Yes, binary collations are case sensitive.


ML

---
http://milambda.blogspot.com/

Can you elaborate on that? Are you saying that an "English" database collation which is binary
doesn't make object names case sensitive? Can you in that case name that exact collation name so we
can test it?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
news:erHXzGMIGHA.1028@TK2MSFTNGP11.phx.gbl...
> But then, why does it work for database if collation is english?
>
> <m.bohse@quest-consultants.com> wrote in message
> news:1138091684.802799.211120@g44g2000cwa.googlegr oups.com...
>> Ajay,
>>
>> you have chosen a binary sort-order. Since the binary value for 'T' is
>> different from 't' the database is effectively case sensitive.
>> Markus
>>
>
>

Ajay Bansal (ajay.bansal02@gmal.com) writes:
> I am facing following issue with SQL server 2000. If, while creating DB, I
> choose collation as Chinese_PRC_BIN, SQL server becomes case sensitive.
>
> e.g. if Table name is "MYTABLE", following query fails
>
> select * from mytable
>
> however, if collation is default (English) , then following query works
> fine.
>
> What is the issue here?

Precisely what you have discovered, that the collation rules of the
database applies to system objects as well. Consider, for instance this
as well:

CREATE DATABASE test1 COLLATE Finnish_Swedish_CI_AI
go
USE test1
go
CREATE TABLE V(w int NOT NULL)
go
INSERT w (v) VALUES (12)
go
SELECT * FROM W
go
USE master
go
DROP DATABASE test1

(In Swedish W is not a letter of its own, but just a variation of V.)

My recommendation is to always develop on a database with a case-
sensitive (or binary) collation, and use only lowercase names. If
you develop on case-insensitive, you will face a nightmare if the
customer says that they want a case-insensitive or binary collation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ok.. lets go back a step here..

How do I know, if in a SQL serevr database - collation is binary? I mean -
does _BIN specify that collation is binary?


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:eTSV2LMIGHA.3036@tk2msftngp13.phx.gbl...
> Can you elaborate on that? Are you saying that an "English" database
> collation which is binary doesn't make object names case sensitive? Can
> you in that case name that exact collation name so we can test it?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
> news:erHXzGMIGHA.1028@TK2MSFTNGP11.phx.gbl...
>> But then, why does it work for database if collation is english?
>>
>> <m.bohse@quest-consultants.com> wrote in message
>> news:1138091684.802799.211120@g44g2000cwa.googlegr oups.com...
>>> Ajay,
>>>
>>> you have chosen a binary sort-order. Since the binary value for 'T' is
>>> different from 't' the database is effectively case sensitive.
>>> Markus
>>>
>>
>>
>

Hi
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')





"Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
news:ujjTHSMIGHA.3036@tk2msftngp13.phx.gbl...
> Ok.. lets go back a step here..
>
> How do I know, if in a SQL serevr database - collation is binary? I mean -
> does _BIN specify that collation is binary?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
> in message news:eTSV2LMIGHA.3036@tk2msftngp13.phx.gbl...
>> Can you elaborate on that? Are you saying that an "English" database
>> collation which is binary doesn't make object names case sensitive? Can
>> you in that case name that exact collation name so we can test it?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
>> news:erHXzGMIGHA.1028@TK2MSFTNGP11.phx.gbl...
>>> But then, why does it work for database if collation is english?
>>>
>>> <m.bohse@quest-consultants.com> wrote in message
>>> news:1138091684.802799.211120@g44g2000cwa.googlegr oups.com...
>>>> Ajay,
>>>>
>>>> you have chosen a binary sort-order. Since the binary value for 'T' is
>>>> different from 't' the database is effectively case sensitive.
>>>> Markus
>>>>
>>>
>>>
>>
>
>

Yes, BIN indicates a binary collation.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
news:ujjTHSMIGHA.3036@tk2msftngp13.phx.gbl...
> Ok.. lets go back a step here..
>
> How do I know, if in a SQL serevr database - collation is binary? I mean - does _BIN specify that
> collation is binary?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:eTSV2LMIGHA.3036@tk2msftngp13.phx.gbl...
>> Can you elaborate on that? Are you saying that an "English" database collation which is binary
>> doesn't make object names case sensitive? Can you in that case name that exact collation name so
>> we can test it?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>
>> "Ajay Bansal" <ajay.bansal02@gmal.com> wrote in message
>> news:erHXzGMIGHA.1028@TK2MSFTNGP11.phx.gbl...
>>> But then, why does it work for database if collation is english?
>>>
>>> <m.bohse@quest-consultants.com> wrote in message
>>> news:1138091684.802799.211120@g44g2000cwa.googlegr oups.com...
>>>> Ajay,
>>>>
>>>> you have chosen a binary sort-order. Since the binary value for 'T' is
>>>> different from 't' the database is effectively case sensitive.
>>>> Markus
>>>>
>>>
>>>
>>
>
>

Ajay Bansal (ajay.bansal02@gmal.com) writes:
> How do I know, if in a SQL serevr database - collation is binary? I mean -
> does _BIN specify that collation is binary?

Yes. BIN = Binary. CI = Case insensitive, AI = Access insensitive,
CS = Case sensitive, AS = Accent sensitive. Some collations also has
WS and KS for Width and Kana sensitive. The latter mainly applies to
Japanese collations I believe.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx