How to change the database server for case sensitive

Question

Post reply

How to change the database server for case sensitive

dastagirid

SSCommitted

Points: 1810
More actions
March 26, 2009 at 1:00 am

#202764

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eswin SSCrazy Eights Points: 8320 More actions · Answer 1

So whats the proble it must not show column1 when give COLUMN1.

I dont think there is an option for that.

Tanx 😀

rene-500237 Hall of Fame Points: 3769 More actions · Answer 2

Hi,

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

Regards,

René

JamieX Hall of Fame Points: 3763 More actions · Answer 3

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 One Orange Chip Points: 26137 More actions · Answer 4

Why do you want to change to case sensitive?

"Keep Trying"

JacekO SSCertifiable Points: 6610 More actions · Answer 5

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'

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Pam Brisjar SSChampion Points: 12094 More actions · Answer 6

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. SSC-Dedicated Points: 31126 More actions · Answer 7

Read about it at http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

MJ

mrunmayeekulkarni42 SSC Rookie Points: 27 More actions · Answer 8

To set or change the database collation

In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases.
If you are creating a new database, right-click Databases and then click New Database. If you do not want the default collation, click the Options page, and select a collation from the Collation drop-down list.
Alternatively, if the database already exists, right-click the database that you want and click Properties. Click the Options page, and select a collation from the Collation drop-down list.
After you are finished, click OK.
This will restrict on case for overall database