Case-sensitive column in SQL Server

To make a column case-sensitive instead of the default case-insensitive, just add a COLLATE to the column definition, and it’ll use that instead of the database default.

Default (case-insensitive)

if object_id('tempdb..#tmp') is not null
  drop table #tmp;
 
create table #tmp (i int not null identity, val varchar(10));
 
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
 
select * from #tmp;

Case-sensitive

if object_id('tempdb..#tmp') is not null
 drop table #tmp;
 
create table #tmp (i int not null identity, val varchar(10) collate Latin1_General_CS_AS);
 
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
 
select * from #tmp;

List collations:

select [name], [description] from fn_helpcollations() order by [name];