An interesting titbit, that I encountered today is with COLLATION with in Sql Server. As we all know collation is an important and interesting element in data storage and retrieval. It directly affects the way character data is stored, sorted and inturn retrieved.

By default Sql Server collation is set to SQL_Latin1_General_CP1_CI_AS (Latin, case insensitive and accent sensitive) but you can change it during installation or anytime after. Each  database, in an instance, could also be set to a different collation as needed. But I would not recommend setting it to something other than default, unless there is a strong business reason for it. But the beauty here is to know that we can set it at any level: Server, database or even each query or an expression within a query.

Sometimes, not very often, but sometime, I run into the need to add COLLATION to a particular condition with in WHERE clause to retrieve the exact data I need. So in this case I use the following style to set COLLATION to an individual condition expression within a query.

[sourcecode language=”sql”]
SELECT * FROM dbo.TableName
WHERE FileName = ‘TestFileName.JPG’ COLLATE SQL_Latin1_General_CP1_CS_AS
[/sourcecode]

For this case, I need to compare character data with case sensitivity, so chose to add ‘COLLATE SQL_Latin1_General_CP1_CS_AS‘, but you can set it to any collation applicable to your situation.

• Note: Please know that you do not want to do this as a general practice everywhere in application code. If you see a repeated need to sort character data in a particular fashion then it is better to restructure the table to store data in new collation. This helps query performance too. I do this once in a while in production systems, so I do not need to restructure the table.

When I apply new COLLATION to the WHERE clause conditions, the underlying indexes are not utilized. The table data is SCANed rather than SEEKed (or INDEX SEEK). This may be okay for once in awhile ad hoc queries,  but not for constantly running application code. If you need to query the data in a particular collation constantly, its better to restructure the table with new collation so retrieval becomes easier with use of any available indexes.

Hope this helps,
_Sqltimes