Collate Break Resolution For Free

If you do not specify a collation, the column is assigned the default collation of the database. You can also use the databasedefault option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

MSDN states COLLATE DATABASEDEFAULT clause casts the collation of an expression, column definition, or database definition to inherit the collation of the "current database". To complement MSDN, the "current database" is the context of the database where the query is executed.

Common collation names either end in BIN or CIAS such as Latin1GeneralBIN or SQLLatin1GeneralCIAS. The BIN means that this is a binary sort order where strings will be sorted using a computer binary order, the result is that A-Z are before a-z and things like accented characters will be at the end.

If you elect to use UTF-8 as your collation, always use utf8mb4 (specifically utf8mb4unicodeci). You should not use UTF-8 because MySQL's UTF-8 is different from proper UTF-8 encoding. This is the case because it doesn't offer full unicode support which can lead to data loss or security issues.

For LIKE queries, the Latin1GeneralCSAS collation is not case-sensitive. According to a bug report to Microsoft, this was listed as "By Design". However, the Latin1GeneralBin collation is also case-sensitive and works exactly as expected for LIKE queries.

The SQLLatin1GeneralCP1CIAS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1GeneralCIAS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

According the SQL Server Books Online, the characters included in range searches depend on the sorting rules of the collation. Collation Latin1GeneralCSAS uses dictionary order so both upper and lower case characters of the specified range are included.