[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Collation Vs Unicode

Venkat

3/28/2007 1:36:00 PM

Hi,
I am trying to understand how collation is related to Unicode data and non
unicode data.

for example, if my database is designed for Unicode then what is the
collaiton need to be specified? if database is designed for non unicode then
what is the collation need to be specified?

For MS documents, i understand that: Collation for Unicode and Non unicode
differs only how data is stored. So this means for programmer it does not
require to think about Unicode and non unicode while defining collation for
database?

Any comments will be greatly appreciated,
Thanks in advance,
Venkat
8 Answers

Tibor Karaszi

3/28/2007 3:35:00 PM

0

Collation always determine things like sort order (ORDER BY), case sensitivity and accent
sensitivity. This applies to both unicode and non-unicode values.

for non-unicode, collation also determines which character set (code pages, repertoire) you have.
More info in:

http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Venkat" <Venkat@discussions.microsoft.com> wrote in message
news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
> Hi,
> I am trying to understand how collation is related to Unicode data and non
> unicode data.
>
> for example, if my database is designed for Unicode then what is the
> collaiton need to be specified? if database is designed for non unicode then
> what is the collation need to be specified?
>
> For MS documents, i understand that: Collation for Unicode and Non unicode
> differs only how data is stored. So this means for programmer it does not
> require to think about Unicode and non unicode while defining collation for
> database?
>
> Any comments will be greatly appreciated,
> Thanks in advance,
> Venkat


Venkat

3/28/2007 3:49:00 PM

0

Thank you for the reply.
I have one scenario:
There is a database column, where I want to allow case sensitive comparison.
Well, to address this need i need to specify collation on this column. Say
for example, i specified collation as:
SQL_Latin1_general_cs_as
and deployed the database to some cusotmer where the above collation gives
wrong sort order, becuase the sql server is in diffrent country/region.

The basic problem here is: Sort Order, Case Sensitive, Accent Sensitive: all
need to be specified together.

In my scenario, my database should use cusotmer defined sort order, but case
sensitive should be used as i defined.

Any comments?

Thanks,
Venkat

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
> Collation always determine things like sort order (ORDER BY), case
> sensitivity and accent sensitivity. This applies to both unicode and
> non-unicode values.
>
> for non-unicode, collation also determines which character set (code
> pages, repertoire) you have. More info in:
>
> http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Venkat" <Venkat@discussions.microsoft.com> wrote in message
> news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>> Hi,
>> I am trying to understand how collation is related to Unicode data and
>> non
>> unicode data.
>>
>> for example, if my database is designed for Unicode then what is the
>> collaiton need to be specified? if database is designed for non unicode
>> then
>> what is the collation need to be specified?
>>
>> For MS documents, i understand that: Collation for Unicode and Non
>> unicode
>> differs only how data is stored. So this means for programmer it does not
>> require to think about Unicode and non unicode while defining collation
>> for
>> database?
>>
>> Any comments will be greatly appreciated,
>> Thanks in advance,
>> Venkat
>
>


Tibor Karaszi

3/28/2007 6:00:00 PM

0

You can have a customer defined collation for the database, then specify the desired collation for
this column in CREATE TABLE.

Or, if you need to customize collation attributes (yes, they do go together), for operations *on
this column*, then you can use the COLLATE clause in your query.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Venkat" <venkatarao.v@gmail.com> wrote in message news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
> Thank you for the reply.
> I have one scenario:
> There is a database column, where I want to allow case sensitive comparison.
> Well, to address this need i need to specify collation on this column. Say for example, i
> specified collation as:
> SQL_Latin1_general_cs_as
> and deployed the database to some cusotmer where the above collation gives wrong sort order,
> becuase the sql server is in diffrent country/region.
>
> The basic problem here is: Sort Order, Case Sensitive, Accent Sensitive: all need to be specified
> together.
>
> In my scenario, my database should use cusotmer defined sort order, but case sensitive should be
> used as i defined.
>
> Any comments?
>
> Thanks,
> Venkat
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
>> Collation always determine things like sort order (ORDER BY), case sensitivity and accent
>> sensitivity. This applies to both unicode and non-unicode values.
>>
>> for non-unicode, collation also determines which character set (code pages, repertoire) you have.
>> More info in:
>>
>> http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Venkat" <Venkat@discussions.microsoft.com> wrote in message
>> news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>>> Hi,
>>> I am trying to understand how collation is related to Unicode data and non
>>> unicode data.
>>>
>>> for example, if my database is designed for Unicode then what is the
>>> collaiton need to be specified? if database is designed for non unicode then
>>> what is the collation need to be specified?
>>>
>>> For MS documents, i understand that: Collation for Unicode and Non unicode
>>> differs only how data is stored. So this means for programmer it does not
>>> require to think about Unicode and non unicode while defining collation for
>>> database?
>>>
>>> Any comments will be greatly appreciated,
>>> Thanks in advance,
>>> Venkat
>>
>>
>
>


Venkat

3/29/2007 4:56:00 AM

0

Tibor Karaszi, thanks for the reply. But the problem remains same.

Yes, we need to do this at query level.
there are two issues if you do at query level:

1)we are not solving the core problem: either query level or at any hifgher
level, case sensitivity is always defined together with sort order. It is
not seperated from sort order
assume that i have where clause which needs case sensitivty check and at the
same time i have Order by clause also.

2)Other issue is: Performance hit if we specify collation at query level,
becuase it can't use indexes.

If SQL Server could have given option to specify these collation properties
individually, this could have been better.

Any comments?

Thanks,
Venkat

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:%23wcnhLWcHHA.4308@TK2MSFTNGP02.phx.gbl...
> You can have a customer defined collation for the database, then specify
> the desired collation for this column in CREATE TABLE.
>
> Or, if you need to customize collation attributes (yes, they do go
> together), for operations *on this column*, then you can use the COLLATE
> clause in your query.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Venkat" <venkatarao.v@gmail.com> wrote in message
> news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
>> Thank you for the reply.
>> I have one scenario:
>> There is a database column, where I want to allow case sensitive
>> comparison.
>> Well, to address this need i need to specify collation on this column.
>> Say for example, i specified collation as:
>> SQL_Latin1_general_cs_as
>> and deployed the database to some cusotmer where the above collation
>> gives wrong sort order, becuase the sql server is in diffrent
>> country/region.
>>
>> The basic problem here is: Sort Order, Case Sensitive, Accent Sensitive:
>> all need to be specified together.
>>
>> In my scenario, my database should use cusotmer defined sort order, but
>> case sensitive should be used as i defined.
>>
>> Any comments?
>>
>> Thanks,
>> Venkat
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
>> in message news:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
>>> Collation always determine things like sort order (ORDER BY), case
>>> sensitivity and accent sensitivity. This applies to both unicode and
>>> non-unicode values.
>>>
>>> for non-unicode, collation also determines which character set (code
>>> pages, repertoire) you have. More info in:
>>>
>>> http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/d...
>>> http://www.solidqualitylea...
>>>
>>>
>>> "Venkat" <Venkat@discussions.microsoft.com> wrote in message
>>> news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>>>> Hi,
>>>> I am trying to understand how collation is related to Unicode data and
>>>> non
>>>> unicode data.
>>>>
>>>> for example, if my database is designed for Unicode then what is the
>>>> collaiton need to be specified? if database is designed for non unicode
>>>> then
>>>> what is the collation need to be specified?
>>>>
>>>> For MS documents, i understand that: Collation for Unicode and Non
>>>> unicode
>>>> differs only how data is stored. So this means for programmer it does
>>>> not
>>>> require to think about Unicode and non unicode while defining collation
>>>> for
>>>> database?
>>>>
>>>> Any comments will be greatly appreciated,
>>>> Thanks in advance,
>>>> Venkat
>>>
>>>
>>
>>
>
>


masri999

3/29/2007 8:52:00 AM

0

On Mar 29, 9:55 am, "Venkat" <venkatara...@gmail.com> wrote:
> Tibor Karaszi, thanks for the reply. But the problem remains same.
>
> Yes, we need to do this at query level.
> there are two issues if you do at query level:
>
> 1)we are not solving the core problem: either query level or at any hifgher
> level, case sensitivity is always defined together with sort order. It is
> not seperated from sort order
> assume that i have where clause which needs case sensitivty check and at the
> same time i have Order by clause also.
>
> 2)Other issue is: Performance hit if we specify collation at query level,
> becuase it can't use indexes.
>
> If SQL Server could have given option to specify these collation properties
> individually, this could have been better.
>
> Any comments?
>
> Thanks,
> Venkat
>
> "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote in
> messagenews:%23wcnhLWcHHA.4308@TK2MSFTNGP02.phx.gbl...
>
>
>
> > You can have a customer defined collation for the database, then specify
> > the desired collation for this column in CREATE TABLE.
>
> > Or, if you need to customize collation attributes (yes, they do go
> > together), for operations *on this column*, then you can use the COLLATE
> > clause in your query.
>
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/d...
> >http://www.solidqualitylea...
>
> > "Venkat" <venkatara...@gmail.com> wrote in message
> >news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
> >> Thank you for the reply.
> >> I have one scenario:
> >> There is a database column, where I want to allow case sensitive
> >> comparison.
> >> Well, to address this need i need to specify collation on this column.
> >> Say for example, i specified collation as:
> >> SQL_Latin1_general_cs_as
> >> and deployed the database to some cusotmer where the above collation
> >> gives wrong sort order, becuase the sql server is in diffrent
> >> country/region.
>
> >> The basic problem here is: Sort Order, Case Sensitive, Accent Sensitive:
> >> all need to be specified together.
>
> >> In my scenario, my database should use cusotmer defined sort order, but
> >> case sensitive should be used as i defined.
>
> >> Any comments?
>
> >> Thanks,
> >> Venkat
>
> >> "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote
> >> in messagenews:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
> >>> Collation always determine things like sort order (ORDER BY), case
> >>> sensitivity and accent sensitivity. This applies to both unicode and
> >>> non-unicode values.
>
> >>> for non-unicode, collation also determines which character set (code
> >>> pages, repertoire) you have. More info in:
>
> >>>http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>
> >>> --
> >>> Tibor Karaszi, SQL Server MVP
> >>>http://www.karaszi.com/sqlserver/d...
> >>>http://www.solidqualitylea...
>
> >>> "Venkat" <Ven...@discussions.microsoft.com> wrote in message
> >>>news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
> >>>> Hi,
> >>>> I am trying to understand how collation is related to Unicode data and
> >>>> non
> >>>> unicode data.
>
> >>>> for example, if my database is designed for Unicode then what is the
> >>>> collaiton need to be specified? if database is designed for non unicode
> >>>> then
> >>>> what is the collation need to be specified?
>
> >>>> For MS documents, i understand that: Collation for Unicode and Non
> >>>> unicode
> >>>> differs only how data is stored. So this means for programmer it does
> >>>> not
> >>>> require to think about Unicode and non unicode while defining collation
> >>>> for
> >>>> database?
>
> >>>> Any comments will be greatly appreciated,
> >>>> Thanks in advance,
> >>>> Venkat- Hide quoted text -
>
> - Show quoted text -


1. I think you need to use dynamic sql to overcome the first problem
2. About performance , you can provide same column_names on both sides
without collation

Like

Select * from customer
where customer_name COLLATE --- = 'abcd'
and customer_name ='abcd' -- This will use index

Venkat

3/29/2007 10:09:00 AM

0

Srinivas,
the 2nd point will not work if:
customer sets the server to case sensitive and I realy don't want to do case
sensitive comaprison in the where clause.
Beucase the 2nd AND condtion will fail becuase by default it does case
sensitive comparison.

Select * from customer
where customer_name COLLATE --- = 'abcd'
and customer_name ='abcd' -- This will use index


Comments?

I am wondering why MS has not given option for setting collation properties
individually.

Regards,
Venkat

"M A Srinivas" <masri999@gmail.com> wrote in message
news:1175158319.258172.69320@y80g2000hsf.googlegroups.com...
> On Mar 29, 9:55 am, "Venkat" <venkatara...@gmail.com> wrote:
>> Tibor Karaszi, thanks for the reply. But the problem remains same.
>>
>> Yes, we need to do this at query level.
>> there are two issues if you do at query level:
>>
>> 1)we are not solving the core problem: either query level or at any
>> hifgher
>> level, case sensitivity is always defined together with sort order. It is
>> not seperated from sort order
>> assume that i have where clause which needs case sensitivty check and at
>> the
>> same time i have Order by clause also.
>>
>> 2)Other issue is: Performance hit if we specify collation at query level,
>> becuase it can't use indexes.
>>
>> If SQL Server could have given option to specify these collation
>> properties
>> individually, this could have been better.
>>
>> Any comments?
>>
>> Thanks,
>> Venkat
>>
>> "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote
>> in
>> messagenews:%23wcnhLWcHHA.4308@TK2MSFTNGP02.phx.gbl...
>>
>>
>>
>> > You can have a customer defined collation for the database, then
>> > specify
>> > the desired collation for this column in CREATE TABLE.
>>
>> > Or, if you need to customize collation attributes (yes, they do go
>> > together), for operations *on this column*, then you can use the
>> > COLLATE
>> > clause in your query.
>>
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/d...
>> >http://www.solidqualitylea...
>>
>> > "Venkat" <venkatara...@gmail.com> wrote in message
>> >news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
>> >> Thank you for the reply.
>> >> I have one scenario:
>> >> There is a database column, where I want to allow case sensitive
>> >> comparison.
>> >> Well, to address this need i need to specify collation on this column.
>> >> Say for example, i specified collation as:
>> >> SQL_Latin1_general_cs_as
>> >> and deployed the database to some cusotmer where the above collation
>> >> gives wrong sort order, becuase the sql server is in diffrent
>> >> country/region.
>>
>> >> The basic problem here is: Sort Order, Case Sensitive, Accent
>> >> Sensitive:
>> >> all need to be specified together.
>>
>> >> In my scenario, my database should use cusotmer defined sort order,
>> >> but
>> >> case sensitive should be used as i defined.
>>
>> >> Any comments?
>>
>> >> Thanks,
>> >> Venkat
>>
>> >> "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com>
>> >> wrote
>> >> in messagenews:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
>> >>> Collation always determine things like sort order (ORDER BY), case
>> >>> sensitivity and accent sensitivity. This applies to both unicode and
>> >>> non-unicode values.
>>
>> >>> for non-unicode, collation also determines which character set (code
>> >>> pages, repertoire) you have. More info in:
>>
>> >>>http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>>
>> >>> --
>> >>> Tibor Karaszi, SQL Server MVP
>> >>>http://www.karaszi.com/sqlserver/d...
>> >>>http://www.solidqualitylea...
>>
>> >>> "Venkat" <Ven...@discussions.microsoft.com> wrote in message
>> >>>news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>> >>>> Hi,
>> >>>> I am trying to understand how collation is related to Unicode data
>> >>>> and
>> >>>> non
>> >>>> unicode data.
>>
>> >>>> for example, if my database is designed for Unicode then what is the
>> >>>> collaiton need to be specified? if database is designed for non
>> >>>> unicode
>> >>>> then
>> >>>> what is the collation need to be specified?
>>
>> >>>> For MS documents, i understand that: Collation for Unicode and Non
>> >>>> unicode
>> >>>> differs only how data is stored. So this means for programmer it
>> >>>> does
>> >>>> not
>> >>>> require to think about Unicode and non unicode while defining
>> >>>> collation
>> >>>> for
>> >>>> database?
>>
>> >>>> Any comments will be greatly appreciated,
>> >>>> Thanks in advance,
>> >>>> Venkat- Hide quoted text -
>>
>> - Show quoted text -
>
>
> 1. I think you need to use dynamic sql to overcome the first problem
> 2. About performance , you can provide same column_names on both sides
> without collation
>
> Like
>
> Select * from customer
> where customer_name COLLATE --- = 'abcd'
> and customer_name ='abcd' -- This will use index
>


Tibor Karaszi

3/29/2007 10:25:00 AM

0

> assume that i have where clause which needs case sensitivty check and at the same time i have
> Order by clause also.

You should be able to specify one COLLATE for the WHERE clause and another COLLATE for the ORDER BY
clause.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Venkat" <venkatarao.v@gmail.com> wrote in message news:%23ME9K6bcHHA.1340@TK2MSFTNGP04.phx.gbl...
> Tibor Karaszi, thanks for the reply. But the problem remains same.
>
> Yes, we need to do this at query level.
> there are two issues if you do at query level:
>
> 1)we are not solving the core problem: either query level or at any hifgher level, case
> sensitivity is always defined together with sort order. It is not seperated from sort order
> assume that i have where clause which needs case sensitivty check and at the same time i have
> Order by clause also.
>
> 2)Other issue is: Performance hit if we specify collation at query level, becuase it can't use
> indexes.
>
> If SQL Server could have given option to specify these collation properties individually, this
> could have been better.
>
> Any comments?
>
> Thanks,
> Venkat
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:%23wcnhLWcHHA.4308@TK2MSFTNGP02.phx.gbl...
>> You can have a customer defined collation for the database, then specify the desired collation
>> for this column in CREATE TABLE.
>>
>> Or, if you need to customize collation attributes (yes, they do go together), for operations *on
>> this column*, then you can use the COLLATE clause in your query.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Venkat" <venkatarao.v@gmail.com> wrote in message news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
>>> Thank you for the reply.
>>> I have one scenario:
>>> There is a database column, where I want to allow case sensitive comparison.
>>> Well, to address this need i need to specify collation on this column. Say for example, i
>>> specified collation as:
>>> SQL_Latin1_general_cs_as
>>> and deployed the database to some cusotmer where the above collation gives wrong sort order,
>>> becuase the sql server is in diffrent country/region.
>>>
>>> The basic problem here is: Sort Order, Case Sensitive, Accent Sensitive: all need to be
>>> specified together.
>>>
>>> In my scenario, my database should use cusotmer defined sort order, but case sensitive should be
>>> used as i defined.
>>>
>>> Any comments?
>>>
>>> Thanks,
>>> Venkat
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
>>> news:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
>>>> Collation always determine things like sort order (ORDER BY), case sensitivity and accent
>>>> sensitivity. This applies to both unicode and non-unicode values.
>>>>
>>>> for non-unicode, collation also determines which character set (code pages, repertoire) you
>>>> have. More info in:
>>>>
>>>> http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/d...
>>>> http://www.solidqualitylea...
>>>>
>>>>
>>>> "Venkat" <Venkat@discussions.microsoft.com> wrote in message
>>>> news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>>>>> Hi,
>>>>> I am trying to understand how collation is related to Unicode data and non
>>>>> unicode data.
>>>>>
>>>>> for example, if my database is designed for Unicode then what is the
>>>>> collaiton need to be specified? if database is designed for non unicode then
>>>>> what is the collation need to be specified?
>>>>>
>>>>> For MS documents, i understand that: Collation for Unicode and Non unicode
>>>>> differs only how data is stored. So this means for programmer it does not
>>>>> require to think about Unicode and non unicode while defining collation for
>>>>> database?
>>>>>
>>>>> Any comments will be greatly appreciated,
>>>>> Thanks in advance,
>>>>> Venkat
>>>>
>>>>
>>>
>>>
>>
>>
>
>


Venkat

3/29/2007 10:42:00 AM

0

Yes, we could do like that.
But there are two issues here:
1) I want to use the Server collation for sort order. But it uses the
collation specified on the column: for case sensitivity
2) I am not sure ORDER BY uses indexes, if yes, since we specify collaiton
it can't use indexes so affects performance by specifying collation in
queries

In order to solve these problems only way is dynamic queries: which affect
performance (both interms of using indexes and compilation), not programmer
friendly

Comments?

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:ehilNyecHHA.2404@TK2MSFTNGP02.phx.gbl...
>> assume that i have where clause which needs case sensitivty check and at
>> the same time i have Order by clause also.
>
> You should be able to specify one COLLATE for the WHERE clause and another
> COLLATE for the ORDER BY clause.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Venkat" <venkatarao.v@gmail.com> wrote in message
> news:%23ME9K6bcHHA.1340@TK2MSFTNGP04.phx.gbl...
>> Tibor Karaszi, thanks for the reply. But the problem remains same.
>>
>> Yes, we need to do this at query level.
>> there are two issues if you do at query level:
>>
>> 1)we are not solving the core problem: either query level or at any
>> hifgher level, case sensitivity is always defined together with sort
>> order. It is not seperated from sort order
>> assume that i have where clause which needs case sensitivty check and at
>> the same time i have Order by clause also.
>>
>> 2)Other issue is: Performance hit if we specify collation at query level,
>> becuase it can't use indexes.
>>
>> If SQL Server could have given option to specify these collation
>> properties individually, this could have been better.
>>
>> Any comments?
>>
>> Thanks,
>> Venkat
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
>> in message news:%23wcnhLWcHHA.4308@TK2MSFTNGP02.phx.gbl...
>>> You can have a customer defined collation for the database, then specify
>>> the desired collation for this column in CREATE TABLE.
>>>
>>> Or, if you need to customize collation attributes (yes, they do go
>>> together), for operations *on this column*, then you can use the COLLATE
>>> clause in your query.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/d...
>>> http://www.solidqualitylea...
>>>
>>>
>>> "Venkat" <venkatarao.v@gmail.com> wrote in message
>>> news:uykVlCVcHHA.3976@TK2MSFTNGP06.phx.gbl...
>>>> Thank you for the reply.
>>>> I have one scenario:
>>>> There is a database column, where I want to allow case sensitive
>>>> comparison.
>>>> Well, to address this need i need to specify collation on this column.
>>>> Say for example, i specified collation as:
>>>> SQL_Latin1_general_cs_as
>>>> and deployed the database to some cusotmer where the above collation
>>>> gives wrong sort order, becuase the sql server is in diffrent
>>>> country/region.
>>>>
>>>> The basic problem here is: Sort Order, Case Sensitive, Accent
>>>> Sensitive: all need to be specified together.
>>>>
>>>> In my scenario, my database should use cusotmer defined sort order, but
>>>> case sensitive should be used as i defined.
>>>>
>>>> Any comments?
>>>>
>>>> Thanks,
>>>> Venkat
>>>>
>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
>>>> wrote in message news:%23xI6l6UcHHA.4624@TK2MSFTNGP03.phx.gbl...
>>>>> Collation always determine things like sort order (ORDER BY), case
>>>>> sensitivity and accent sensitivity. This applies to both unicode and
>>>>> non-unicode values.
>>>>>
>>>>> for non-unicode, collation also determines which character set (code
>>>>> pages, repertoire) you have. More info in:
>>>>>
>>>>> http://msdn2.microsoft.com/en-us/librar...(SQL.80).aspx
>>>>>
>>>>> --
>>>>> Tibor Karaszi, SQL Server MVP
>>>>> http://www.karaszi.com/sqlserver/d...
>>>>> http://www.solidqualitylea...
>>>>>
>>>>>
>>>>> "Venkat" <Venkat@discussions.microsoft.com> wrote in message
>>>>> news:F9BD8619-885A-4906-8F0D-7D21A326419B@microsoft.com...
>>>>>> Hi,
>>>>>> I am trying to understand how collation is related to Unicode data
>>>>>> and non
>>>>>> unicode data.
>>>>>>
>>>>>> for example, if my database is designed for Unicode then what is the
>>>>>> collaiton need to be specified? if database is designed for non
>>>>>> unicode then
>>>>>> what is the collation need to be specified?
>>>>>>
>>>>>> For MS documents, i understand that: Collation for Unicode and Non
>>>>>> unicode
>>>>>> differs only how data is stored. So this means for programmer it does
>>>>>> not
>>>>>> require to think about Unicode and non unicode while defining
>>>>>> collation for
>>>>>> database?
>>>>>>
>>>>>> Any comments will be greatly appreciated,
>>>>>> Thanks in advance,
>>>>>> Venkat
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>