Search This Blog

October 28, 2013

Using COLLATE Latin1_General_CS_AS can search the right data

Last Friday I found out an interesting issue. The data was not showing up in the Silverlight UI, but the data has the value in the table in SQL Server 2008 R2.

After investigating the issue with our product owner, also a GIS analyst, we finally found out it was the data load issue.

One of the ArcGIS domain values is “Unknown”.  When we do something in the Silverlight UI, the value of “Unknown” is saved in the table. But they used ‘unknown’ to do the data load, which causes the current issue in Silverlight UI.

In order to find out how many rows have “unknown” value, we use the following SQL command because adding COLLATE Latin1_General_CS_AS will not return any “Unknown” data.

select colum1
FROM table
where colum1 COLLATE Latin1_General_CS_AS = 'unknown'

The table has 10, 000+ rows with “unknown” value.  After updating the value from unknown to Unknown, the data is showing up in Silverlight UI perfectly. 

No comments: