Tim Van Wassenhove

Passionate geek, interested in Technology. Proud father of two

18 Oct 2012

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';