SQLCLR and Collations

PASS Summit Unite 2009 Speaker Tip

An important quality of a well-written SQL Server Common Language Runtime (SQLCLR) routine is transparency: A caller should never have to think about, or even realize, that a given routine was written in a .NET language rather than in T-SQL. As developers, therefore, our job is to be mindful of the differences between the two systems so that we can create routines that behave the way we—and our end users—expect them to.

One of the most important differences between data in SQL Server and in .NET involves strings and how they are compared. In SQL Server, all strings have an associated collation. The collation controls the behavior of comparison operations, a factor that is considered any time data needs to be sorted, indexed, or otherwise manipulated. The collation used by a given string can be inherited from the parent database or table, depending on the context, or can be defined at runtime using a collation designator. In .NET languages, however, strings don’t have collations. Rather, they have default comparison behaviors that you must explicitly override using a structure called CompareInfo.

How does this difference affect those of us writing solutions using SQLCLR routines? To make a SQLCLR routine behave just like a T-SQL routine, we need to communicate the collation settings of a given string when passing it into the SQLCLR routine as an argument. The solution, luckily, isn’t too difficult: You can use the SqlString structure, found in the System.Data.SqlTypes namespace, as an input parameter to your SQLCLR routines. This structure maps to all the SQL Server string types and includes the CompareInfo property, which exposes a fully-formed instance of the CompareInfo structure based on the collation of the input string.

Read more from Adam about his Nov. 2 PASS Summit 2009 pre-conference seminar, "SQLCLR from Beginner to Expert."


Editor's Note: SQL Server Magazine would like to thank PASS for providing this technical tip.