Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to change the database server for case sensitive


How to change the database server for case sensitive

Author
Message
dastagirid
dastagirid
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 248
hi,

In my database I created one table and add one column.

column name is : column1 ..

when i used select query select .

select COLUMN1 FROM table ..

it showing data.. how can i change the database to case sensitive..
I am using sql server 2005 ctp expressedition..

regards:
giri...

Thanks
Dastagiri.D
Eswin
Eswin
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1639 Visits: 1078
So whats the proble it must not show column1 when give COLUMN1.

I dont think there is an option for that.

Tanx :-D
rene-500237
rene-500237
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2640 Visits: 1171
Hi,

you can change the server collation, check http://msdn.microsoft.com/en-us/library/ms179254.aspx

Regards,
René
JamieX
JamieX
Old Hand
Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)Old Hand (371 reputation)

Group: General Forum Members
Points: 371 Visits: 437
Note that you can also change your database, or table collation as well (if you don't want your entire server running that way)

So if you're using SQL_Latin1_General_CP1_CI_AS, try SQL_Latin1_General_CP1_CS_AS

ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CS_AS

ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Why do you want to change to case sensitive?

"Keep Trying"
JacekO
JacekO
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 606
You do not have to change the database or column collation in order to use different collations for specific tasks. In fact you can mix collations to match your needs.

If you have your database built with case insensitve collation but would like to do case sensitive search just use the COLLATE keyword.

For example the first select will find all records where test code is 'ABC' regardless of the case - so abc, AbC aBc and such will be found. The second one will find only the record matching the case of the specified string 'ABC' in this case. The third example will mix the case sensitive and case insensitive search in one query.

SELECT * FROM Tests
WHERE TestCode = 'ABC'

SELECT * FROM Tests
WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS

SELECT * FROM Tests
WHERE TestCode = 'ABC' COLLATE Latin1_General_CS_AS
OR TestCode = 'CBS'

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Pam Brisjar
Pam Brisjar
SSC Eights!
SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)SSC Eights! (910 reputation)

Group: General Forum Members
Points: 910 Visits: 2804
Note that changing server or database collation does NOT change the collation for pre-existing columns/data. There's only one way to do that:
Create a brand new empty database in the desired collation and migrate the data into that DB.

Simplest thing to do is to use the COLLATE option, though (as mentioned above).

MSSQLTIPS just had an article on that today: slightly different context but same issue
MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 8766
Read about it at http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

MJ
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search