SQLCLR: Why use Sql[type] instead of Plain Old [Type]
I got nailed with a couple of good questions tonight that I could have answered better. One of them was “why would you choose to use SqlString vs. String in a SQLCLR object?”
- These types are direct mappings of the native types in the SQL engine
- They are faster since there is no conversion to a .NET type to cope with
- They are safer since there the semantics of the type match those with SQL Server, especially for conversion.
- They deal with nullability the way we'd think about it from a database point of view
- If an instance is nullable, setting it to dbNull.value works.
- If an instance isn't nullabe, setting it to dbNull value causes an SqlNullValueException
- When an operation would result in a significant loss of value throws an SqlTruncationException
The crux here is probably the safety factors rather than the performance aspects. Why? In the past, I've not been very diligent about using the the SQL-Specific types within the body of a method, but have been about making sure the method signature as for SQL-Specific types. Performance never really seemed less than I expected, except in one case where I was building -- rather than parsing -- strings.
That made me wonder, though, what are other folks doing in this regard. Interestingly enough, the example in “Using CLR Integration in SQL Server 2005” white paper doesn't actually show them being used all that much, except for those things that actually call back to the engine. Not really sure what to make of that.