June 14, 2007 at 3:45 am
One of the questions asked to a friend in an evaluation was:
Is there a way to make database case sensitive or insensitive? If so how?
I always assumed that databases are case insensitive by default.
What are the possible answers to the above q?
Replies would be highly appreciated.
June 14, 2007 at 5:05 am
Case senstitive depends on the collation settings that is preferred. You can set collation @ server, database, table and column level.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 14, 2007 at 9:39 pm
Can you be a little more elaborate?
lets say i have a database which is not case insensitive. how do i make it case sensitive?
please explain with example.
June 14, 2007 at 11:58 pm
Try
ALTER DATABASE "db-name" COLLATE Latin1_General_CS_AS
CS stands for "case sensitive"
AS stands for "accent sensitive"
This makes the collation "Latin1_General" the default collation of the database, specifying that characters with different case or accent are to be treated as unequal when comparing strings. For insensitivity, replace the S by an I, as in Latin1_General_CI_AI.
June 15, 2007 at 12:33 am
And keep that COLLATE phrase handy. Because for every cross-database query you write that compares, say, varchar fields you need to use it. For example, if you have a table with column userid which you want to compare with master.dbo.sysxlogins.name you would need to write
select ...
from mytable t
join master.dbo.sysxlogins l on t.userid collate Latin1_general_BIN = l.name
Due to my particular activity, I have worked a lot with case-sensitive databases and case-sensistive SQL Server instances. I have gotten thoroughly used to them but have never seen any particular advantage.
June 15, 2007 at 12:17 pm
There's more to it than altering the database-level collation. This will have no effect on tables that were created before the change. You need to re-create and reload the tables that have columns affected by collation (character types)
June 20, 2007 at 4:31 am
What Jeff says is right. Altering collation after creation creating the database only changes the collation of the database any objects that would have been created will have the collation as before.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply