SQL Server – Good Articles on Database Collation

Database Collation defines default character comparison rules for a database. I also inspect the columns and expressions involved.

Readers ask me where to start with collation. A database can have a different collation from its instance default. Individual character columns and expressions can use different rules too.

Choose the right question

• For a new database, decide the intended language, case, accent, and supported character behavior before choosing its default.
• For a conflict, inspect the two expressions and choose the intended comparison rules. An expression-level COLLATE does not change the entire database.
• For a case-sensitive search, use the intended case-sensitive rules and a test input whose other characters actually match.

The original case-search list included a value with a space and several values without one. Case-insensitivity alone does not remove that space. Likewise, the default collation is not case-insensitive on every installation.

The articles below preserve the original collection. Test the relevant inputs and review the plan before a broader collation change. Share another useful example in the comments.

Reference: COLLATE reference.

Related reading

• SQL SERVER – Creating Database with Different Collation on Server
• SQL SERVER – Cannot Resolve Collation Conflict for Equal To Operation
• SQL SERVER – Collate – Case Sensitive SQL Query Search
• Twitter

Database collation is not a guarantee about every expression, it is the database default.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.