|
Home > Archive > MS SQL Server ODBC > January 2006 > SQL server becomes case sensitive if I choose collation as : Chinese_PRC_BIN
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL server becomes case sensitive if I choose collation as : Chinese_PRC_BIN
|
|
| Ajay Bansal 2006-01-24, 3:23 am |
| 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
| |
| m.bohse@quest-consultants.com 2006-01-24, 3:23 am |
| 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 2006-01-24, 3:23 am |
| 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.googlegroups.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
>
| |
| Tibor Karaszi 2006-01-24, 3:23 am |
| 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.googlegroups.com...
>
>
| |
| Erland Sommarskog 2006-01-24, 3:23 am |
| 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_A
I
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Ajay Bansal 2006-01-24, 3:23 am |
| 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@hotmai
l.nomail.com> 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...
>
| |
| Uri Dimant 2006-01-24, 3:23 am |
| 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@hotmai
l.nomail.com> wrote
> in message news:eTSV2LMIGHA.3036@tk2msftngp13.phx.gbl...
>
>
| |
| Tibor Karaszi 2006-01-24, 7:23 am |
| 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@hotmai
l.nomail.com> wrote in message
> news:eTSV2LMIGHA.3036@tk2msftngp13.phx.gbl...
>
>
| |
| Erland Sommarskog 2006-01-24, 7:23 am |
| 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
|
|
|
|
|