Case Sensitive database and use of Distinct clause

Hi,

In general, we don't have case sensitive database but whenever we have a sensitive database we need to watch out for Distinct statement.

Let's create a new database with coalation as case sensitive:

Click on Options and change the COLLATION to SQL_LATIN1_GENERAL_CP1_CS_AS

Now let's create a table in it and insert data in that table.

create table dbo.DisTest
(
Id int identity,
name varchar(100)
)

insert into dbo.DisTest
values ('Name1'), 
('Name2'),
('Name3'), 
('Name3')

select * from dbo.DisTest

Now let's enter name as 'NAME3' & use distinct with select statement.

Insert into dbo.DisTest
values('NAME3')

Select distinct name from dbo.DisTest

In this situation, what to do to avoid duplicate values:

We can execute following query to get distinct values:

Select distinct name Collate SQL_LATIN1_GENERAL_CP1_CI_AS from dbo.DisTest

Finally, We saw that whenever we setup/create database we must think about what collation we want.

Do let me know if there is any issue.

Happy SQL Coding