Tag: collations

SQL Server Datatypes: Common Modeling Dilemmas

When designing a database, early decisions can have a huge impact on the performance and storage requirements. These decisions can be difficult to change later, as most subsequent work will depend on the physical model. This article highlights some common design decisions, flaws, and misconceptions.Creating a Primary Key: Uniqueidentifer or IntegerWhen defining a surrogate primary key for a table, two options are the most common: Integer and UniqueIdentifier (aka.Globally Unique Identifiers

Oracle Collations: Basic NLS Parameters, Binary and Linguistic Collations

Oracle bases its language support on the values of parameters that begin with NLS. These parameters specify, for example, how to display currency or how the name of a day is spelled.The table below presents some of the NLS parameters. By using one of them, NLS_SORT, we can specify the sort method (binary or linguistic) for both SQL WHERE clause operations and NLSSORT function operations.To check the current NLS settings, type:

PostgreSQL Collations

List CollationsCollations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations:The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories).Locale support is initialized when the database is created. After that, you can no longer change the locale, because the default collation selects LC_COLLATE and LC_CTYPE values are specified at database creation time. Appropriate language settings are defined in the below subcategories.

MySQL Collations

List CollationsTo list all collations available in MySQL, useYou can filter the list to show only collations for charset ‘latin1’:The convention for a collation name in MySQL: first character set name, then the name of the language, finally the type of the collation (ci stands for case-insensitive, cs for case-sensitive, bin for binary collation). So ‘latin1_danish_ci’ is a collation for charset ‘latin1’, for the Danish language and is case-insensitive.Every collation in MySQL is assigned to exactly one character set. Every character set has one default collation which is used if the collation is not specified explicitly. MySQL checks if the collation and the character set match. If they don’t match, an error is raised.

How Does a Database Sort Strings?

Different languages have different alphabets and different ways to order letters within those alphabets. For example, a Polish character Ł comes right after L and before M. In Swedish, a letter Å comes almost at the end, right after Z. In French diacritics marks have no impact on the alphabetical order, so the letters À, Á and  are treated as the letter A when sorting strings.Acollationis a set of rules that defines how to compare and sort character strings. A collation tells you what the order of characters is and which characters should be treated as the same.