Just a few minutes ago, I thought a coworker insulted “my” database. He said it was insensitive. I asked him exactly what problem he had with the database. He said it couldn’t distinguish between lowercase and uppercase and he wanted that fixed.
Case sensitive comparisons do come up from time to time. By default, SQL Server creates case insensitive databases. Rebuilding a database to change to a case sensitive collation is one way of dealing with case sensitive comparisons, but there is a much easier way. You can specify the collation in the join or comparison condition. CS is case sensitive, CI is case insensitive. AI is accent insensitive.
Run the sample code below to see how it works:
create
table #uppercase (
col nchar(1)
);
go
create table #lowercase (
col nchar(1)
);
go
insert into #uppercase (col) values ('A');
insert into #uppercase (col) values ('B');
insert into #uppercase (col) values ('C');
insert into #uppercase (col) values ('D');
insert into #uppercase (col) values ('E');
insert
into #lowercase (col) values ('a');
insert into #lowercase (col) values ('b');
insert into #lowercase (col) values ('c');
insert into #lowercase (col) values ('d');
insert into #lowercase (col) values ('e');
select
u.col, l.col
from #uppercase u inner join #lowercase l
on u.col = l.col; --returns 5 rows
select u.col, l.col
from #uppercase u inner join #lowercase l
on u.col collate Latin1_General_CS_AI = l.col collate Latin1_General_CS_AI; --returns 0 rows