Thursday, December 31, 2009

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

1 comment:

Anonymous said...

HI!
I was told to come here as in COMEADEN?
This is the DINGU blog with DYMAN?
DIMONST?
Yeah.
They said that you work with DISNEY and DASSAULT and LA - LOUISIANA?
GISSANTE.
Yeah.
I think that's the way they spelled it.
DINGLENE also spelled it like that.
Anyway, how much in EF- FF do I make if I get what you want for GE?
Vermont says HARPER is a special guy?
RICE , too?
But I hear TURNER's involved?
Come on.
Don't be greedy.
Duke of EARL's in it with the QUEEN and that's bucks.
I'll wait for your answer on this blog.
SUGLADOD says it's worth the wait.