Stop auto conversion of string in SQL Server

Question

Let's say I have a table my_table and it has two rows:

col1
-----
AAA
aaa

I have a problem in that if I do a

select col1, count(*)   
from my_table  
group by col1

I get

col1 | count(*)
----------------
AAA  |    2

Is there anyway to stop this and differentiate AAA and aaa? It is very difficult for me to reconcile my result with other databases.

Regards,

Answer 1

My guess is you ran into the same problem as the author of this question: How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

The collation of the database is probably case insensitive and you will have to change it. That would be my guess, anyway.

Here is the associated documentation provided about the subject by the accepted answer on the other question: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Answer 2

Use a case sensitive collation when querying:

SELECT col1 COLLATE Latin1_General_CS_AS as name, COUNT(*)
FROM my_table
GROUP BY col1 COLLATE Latin1_General_CS_AS as name

You can also consider permanently changing the collation of the name column to be case sensitive.

Answer 3

Select col1 Collate SQL_Latin1_General_CP1_CS_AS, COUNT(col1)
From my_table  
Group By col1 Collate SQL_Latin1_General_CP1_CS_AS

Answer 4

Use the correct collation : SQL_Latin1_General_CP1_CS_AS

as following:

   Select col1 Collate SQL_Latin1_General_CP1_CS_AS, count (col1)
   From my_table  
   Group By col1 Collate SQL_Latin1_General_CP1_CS_AS