by Michael S. Kaplan, published on 2006/03/18 14:20 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/03/18/554517.aspx
Making sure that you use consistent casing behavior is really important.
A good example is a bug that is fixed in the CTP release of SQL Server 2005 SP1. As mentioned by Bob Myers:
The most critical issue is related to report processing and database collation. Model-based queries were incorrectly returning some entity key grouping values that differed only by case. Because case-insensitive report processing (the default) would combine any such rows, some reports would not actually display all the rows returned by the query. For a variety of reasons, many reports were unaffected by this issue, but some reports that display data from a large table with an auto-increment primary key could fail to display a significant number of rows returned by the query.
Even with SP1, if you are creating any report in Visual Studio Report Designer, you can get yourself into this situation by specifying an incorrect value for the dataset's CaseSensitivity property. If the value you specify does not result in a match between the behavior of report processing and the underlying database, you can expect subtle inconsistencies in your data.
Note that the original problem I complained about related to SQLCLR -- the inconsistencies in results between SQL Server and the CLR that led to my post String.Compare is for sissies (not for people who want SQLCLR consistency) -- is not something that is really being addressed here.
Now I agree that the bug they fixed is more important -- dropping rows in a report is a big deal.
But as long as SQL Server and the CLR handle things like collation and case insensitivity in two different ways yet try to interoperate, we are pretty much destined to have a smaller group of issues come up in any applications make significant use of SQLCLR features and assume they will have consistent results....