Sample query to demonstrate influence of collation in Sql Server
Lately I had the pleasure to investigate collations and here is a sample query that demonstrates how a collation impacts the behaviour of a query:
WITH [Words] AS (
SELECT N'Een' AS [word]
UNION ALL
SELECT N'Eèn'
UNION ALL
SELECT N'EEN'
)
SELECT [word]
FROM [Words]
WHERE [word]
--COLLATE Latin1_General_CS_AS -- returns Een
--COLLATE Latin1_General_CI_AI -- returns Een, Eèn and EEN
--COLLATE LAtin1_General_CI_AS -- returns Een and EEN
COLLATE Latin1_General_CS_AI -- returns Een and Eèn
= N'Een';