![]() This is the natural state for anyone using this for compatibility, but not for those hoping to use it for space-savings. Be warned that UTF-8 collations work best when ALL columns, and the database itself, are using VARCHAR data (columns, variables, string literals) with a _UTF8 collation. Testing includes all functionality, and more than just a few rows of data. If you want this for space-savings, you had better test, and TEST AGAIN. If you need this for compatibility, then the cost is acceptable. Even though there are a few scenarios where UTF-8 can provide up to 50% space savings as compared to NVARCHAR, that is a side-effect and has a cost of a slight hit to performance in many / most operations. PLEASE NOTE: UTF-8 was designed / created for compatibility with environments / code that are set up for 8-bit encodings yet want to support Unicode. There is also a single binary _UTF8 collation ( _BIN2, not _BIN). version 90 or 100 collations with _SC in their name, and version 140 collations). ![]() This option can only be used on collations that support supplementary characters (i.e. It's an 8-bit encoding that allows for Unicode data to be stored in VARCHAR and CHAR datatypes (but not the deprecated TEXT datatype). _UTF8 is a new option as of SQL Server 2019. For details on the differences between _BIN and _BIN2, please see: Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2). If using SQL Server 2005 or newer, use _BIN2. _BIN is the older style, and _BIN2 is the newer, more accurate style. This ending is never combined with any of the 5 sensitivities or _SC. Data is still stored the same, but there are no linguistic rules. _BIN or _BIN2 at the end means "binary" sorting and comparison.This ending can be added to any non-binary, version 90 or 100 collation. Without _SC at the end (or _140_ in the middle), built-in functions don't see a single supplementary character, but instead see two meaningless code points that make up the surrogate pair. The "support" only affects how the built-in functions interpret surrogate pairs (which are how supplementary characters are encoded in UTF-16). _SC at the end means "Supplementary Character support".VSS = variation selector sensitive (only available in the version 140 collations) or missing = variation selector insensitive.WS = width-sensitive or missing = width insensitive.KS = Kana type-sensitive or missing = Kana type-insensitive.AS = accent-sensitive or AI = accent-insensitive.CS = case-sensitive or CI = case-insensitive. ![]() Next you have the sensitivities, that can be in any combination of the following, but always specified in this order:.So, those endings exist for version 90 and 100 collations, but only starting in SQL Server 2012. I said "for the most part" because the collations ending in _SC were introduced in SQL Server 2012 (version 11.0), but the underlying data wasn't new, they merely added support for supplementary characters for the built-in functions. There are some that are 90 (for SQL Server 2005, which is version 9.0), most are 100 (for SQL Server 2008, version 10.0), and a small set has 140 (for SQL Server 2017, version 14.0). Not all versions of SQL Server come with new collations, so there are gaps in the version numbers. Windows collations with no version number in the name are version 80 (meaning SQL Server 2000 as that is version 8.0). The COLLATE, while not present in all collation names, refers to the SQL Server version in which the collation was introduced (for the most part). If "What does COLLATE SQL_Latin1_General_CP1_CI_AS do?" means "What does the COLLATE clause of CREATE DATABASE do?", then: BUT, the comparison rules defined by the chosen Collation are used in many places outside of user queries against user data. Yes, at the most basic level Collation handles sorting. Please be aware that the accepted answer is a bit incomplete. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |