All You Wanted to Know About Collations by Erland Sommarskog

Session Title: All You Wanted to Know About Collations
Speaker: Erland Sommarskog

Abstract: Collations is a topic that tends to confuse SQL Server professionals. It is not that the implementation of collations in SQL Server is extremely confusing, but computer people are not always well versed in human languages and character encodings, and therefore it can be a big surprise that V and W could be considered equal or that i and I could be different in a case-insensitive comparison.

In this light-minded presentation I cover what a collation controls and I explain what all the funny components in a collation name like Latin1_General_100_CI_AS_KS_WS_SC_UTF8 actually mean. I illustrate this by giving examples of how they work with different languages. I provide some tricks you can apply to overcome some traps related to collations. I give particular attention to the UTF-8 collations added in SQL 2019. Finally, I discuss how the choice of collations can affect performance.