Collation Info (Microsoft Connect)

Collations Info

Home Concepts Answers Reference Microsoft Connect Contact

collation, encoding, code page, locale, sorting, comparison, LCID, T-SQL, TSQL, SQL Server

— Microsoft Connect —

Suggestions ( for SQL Server )
Please add missing properties to COLLATIONPROPERTY built-in function and/or sys.fn_helpcollations() Support "natural sorting" / DIGITSASNUMBERS as a Collation option Allow collation set by variable when using the COLLATE clause (at least in expressions) Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation

Suggestions ( for .NET )
Expose Collation Info of SQL Server Result Set via SqlDataReader Support "natural sorting" / SORT_DIGITSASNUMBERS via CompareOptions

Bugs

BIN2 collations do not come through SqlCompareOptions of SqlString
SSMS 17.3 IntelliSense does not recognize CATALOG_DEFAULT as a valid collation
SSMS 17.3 IntelliSense collation-related validations do not adjust for Contained Databases
SSMS 17.3 IntelliSense validates temporary stored procedures using case-sensitive comparison even on a case-insensitive instance
Contained Databases use TempDB Collation for Temporary Stored Procedure names instead of CATALOG_DEFAULT
new TRANSLATE function syntax not recognized by SSMS 17.3 – Fixed (in SSMS 18.0)
UTF-8 in SQL 2019: NVARCHAR to VARCHAR(MAX) can get Msg 8152 "String or binary data would be truncated" – Fixed
UTF-8 in SQL 2019: Binary UTF-8 Collations are missing – PARTIALLY Fixed (in CTP 2.3)
UTF-8 in SQL 2019: Invalid UTF-8 bytes get (Msg 682, Level 22, State 148) Internal error. Buffer provided to read column value is too small. – Fixed
UTF-8 in SQL 2019: Inconsistent handling of invalid UTF-8 byte sequences
UTF-8 in SQL 2019: Collation Precedence allows for Unicode character data loss concatenating UTF8 with higher precedence non-UTF8 VARCHAR
UTF-8 in SQL 2019: An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0 – Fixed
UTF-8 in SQL 2019: Install fails with "Could not find the Database Engine startup handle." if a Unicode-only Collation is selected – PARTIALLY Fixed (in CTP 2.3)
UTF-8 in SQL 2019: Collation Precedence allows for silent truncation (data loss) implicitly converting Extended ASCII characters into UTF8
xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation
sp_validname incorrectly returns 1 / true if name being checked contains code point U+FFFF which is invalid, even for delimited identifiers
QUOTENAME considers 0xFFFF to be valid, but needs to return NULL if that character exists in input value
Restoring databases into newer versions of SQL Server will create missing Internal Tables with incorrect collation