MS SQL Server - case sensitive

Author

case sensitive

Frank Ashley

2005-06-13, 8:23 pm

Is there a way to set a case sensitive db to case insenstive.

Thanks
Frank

Mike Hodgson

2005-06-14, 3:23 am

ALTER DATABASE MyDB COLLATE <collation_name>

(see BOL:
http://msdn.microsoft.com/library/d..._aa-az_4e5h.asp)

For example:

ALTER DATABASE MyDB COLLATE Latin1_General_CI_AS

Note, this can be overridden at the column level and even during query
evaluations. For instance, these 2 queries return different result sets:

select * from Northwind.dbo.Orders where ShipCountry COLLATE
Latin1_General_CS_AS
= 'germany'
select * from Northwind.dbo.Orders where ShipCountry COLLATE
Latin1_General_CI_AS
= 'germany'

--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com

Frank Ashley wrote:

>Is there a way to set a case sensitive db to case insenstive.
>
>
>Thanks
>Frank
>
>
>
>

Tibor Karaszi

2005-06-14, 3:23 am

.... also note that ALTER DATABASE does not change collation for existing tables. You need to use
ALTER TABLE ... ALTER COLUMN for that.

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

"Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message
news:upXygsIcFHA.3040@TK2MSFTNGP14.phx.gbl...
> ALTER DATABASE MyDB COLLATE <collation_name>
>
> (see BOL:
> http://msdn.microsoft.com/library/d..._aa-az_4e5h.asp)
>
> For example:
>
> ALTER DATABASE MyDB COLLATE Latin1_General_CI_AS

>
>
> Note, this can be overridden at the column level and even during query
> evaluations. For instance, these 2 queries return different result sets:
>
> select * from Northwind.dbo.Orders where ShipCountry COLLATE
> Latin1_General_CS_AS
= 'germany'
> select * from Northwind.dbo.Orders where ShipCountry COLLATE
> Latin1_General_CI_AS
= 'germany'
>
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
>
>
>
> Frank Ashley wrote:
>
>

Frank Ashley

2005-06-14, 3:23 am

So I need to iterate through all the existing tables and columns using ALTER
TABLE ... ALTER COLUMN. Is that what you mean?

Thanks
Frank

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23D%23cB3KcFHA
.1148@tk2msftngp13.phx.gbl...
> ... also note that ALTER DATABASE does not change collation for existing
> tables. You need to use ALTER TABLE ... ALTER COLUMN for that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message
> news:upXygsIcFHA.3040@TK2MSFTNGP14.phx.gbl...
>
>

Tibor Karaszi

2005-06-14, 3:23 am

Yes. Not fun, especially as you can only do one column at a time. Also, you need to remove indexes
(including PK and UQ constraints) and possibly foreign keys (don't remember). It is documented in
Books Online.

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

"Frank Ashley" <aa@aa.com> wrote in message news:OadX2ZLcFHA.3712@TK2MSFTNGP09.phx.gbl...
> So I need to iterate through all the existing tables and columns using ALTER TABLE ... ALTER
> COLUMN. Is that what you mean?
>
> Thanks
> Frank
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:%23D%23cB3KcFHA
.1148@tk2msftngp13.phx.gbl...
>
>

Sponsored Links