The servers in the current environment I am working has been configured with case insensitive collation by default during the installation. I had to help a developer who was trying to write a clr function to compare values stored in a database. We had to find out all the uppercase first and last names stored in one of the tables. The columns in the table have been created case insensitive. I thought of using built in feature instead of doing it in CLR and finding out the results. As you see in the simple script below I am creating 4 columns 2 case insensitive and 2 case sensitive. Populate it with sample data and finally compare the columns against each other. Note that once you create a columns like this you can not compare case insensitive column against so pay attention the where clause in the select statements. With this simple solution we were able to easily produce all the records stored in uppercase in the table. For more information look in the Books Online and search for collate
HTH,
USE tempdb
GO
CREATE TABLE dbo.CaseSensitiveTest(
RowId TINYINT IDENTITY (1,1),
CIFName VARCHAR (30),
CILName VARCHAR (30),
CSFName VARCHAR (30) COLLATE Latin1_General_CS_AS,
CSLName VARCHAR (30) COLLATE Latin1_General_CS_AS
)
GO
INSERT INTO dbo.CaseSensitiveTest
SELECT 'MICKEY', 'MOUSE', 'Mickey','Mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'mickey','mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'Mickey','Mouse'
UNION ALL
SELECT 'MICKEY', 'MOUSE', 'MICKEY','MOUSE'
UNION ALL
SELECT 'bunny', 'rabbit', 'Bunny','Rabbit'
UNION ALL
SELECT 'BUNNY', 'RABBIT', 'BUNNY', 'RABBIT'
GO
SELECT * FROM dbo.CaseSensitiveTest
SELECT *
FROM dbo.CaseSensitiveTest
WHERE CIFName COLLATE Latin1_General_CS_AS = CSFName
AND CIFName COLLATE Latin1_General_CS_AS = UPPER(CIFname)
DROP TABLE dbo.CaseSensitiveTest
