PASS Summit Unite 2009 Speaker Tip


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."
