Using the distinct problem in case-sensitive SQL Server

Problem

SQL Server provides you with the ability to store mixed-case data in your database, but depending on how you create the database, SQL Server ignores the case when you give T-SQL commands. One problem you may face is that you want to get a list that contains only a different value from a table, to show the difference in your table, but if your database is based on case-insensitive, then the distinct conditional statement will not show these differences, it will only put all the values together. So is there any choice in this area?

Expert answers

To illustrate this action, we will use a case-sensitive and case-insensitive database to see the two ways it works.

The first group of queries uses the AdventureWorks database, which is configured to be case-sensitive. To get a collection from your database, you can run this query:

SELECT name, collation_name
FROM master.sys.databases

We will query the Preson.contact data in the AdventureWorks database. All the data is created as a mixed case, so when we run this query we don't get duplicate values.

SELECT DISTINCT TOP 10 FirstName
FROM Person.Contact
WHERE FirstName LIKE 'A%'
ORDER BY 1

If you update a record and change FirstName from "Adam" to "Adam," then we get two different values when we run this query.

UPDATE Person.Contact
SET FirstName = 'ADAM'
WHERE ContactID = 62
GO
SELECT DISTINCT TOP 10 FirstName
FROM Person.Contact
WHERE FirstName LIKE 'A%'
ORDER BY 1

As you can see, "Adam" and "Adam" are now displayed as different values.

What we're going to do next is create a new table in a case-insensitive database and load all the data from person.contact into this new table.

CREATE TABLE Test.dbo.contact (FirstName nvarchar(50))
GO
INSERT INTO Test.dbo.contact
SELECT FirstName FROM Person.Contact
GO
SELECT DISTINCT TOP 10 FirstName
FROM Test.dbo.contact
WHERE FirstName LIKE 'A%'
ORDER BY 1
GO

When we run a select query, you can see that the output combines "Adam" and "Adam" because it is case-insensitive.