Welcome to my blog post with some useful information on SQL Server collations
The Sage X3 Version 12 Prerequisites for Microsoft® Windows - Microsoft® SQL Server 2016/2017/2019 have recently been updated to include the requirement to use the Latin1_General_BIN2 SQL collation previously Latin1_General_BIN was a prerequisite.
Please see this link https://online-help.sageerpx3.com/erp/12/public/prerequisites_windows-sql-server.html
This change was introduced to resolve a sorting issue with Chinese characters. It is recommended that if performing a new installation to use the Latin1_General_BIN2 Collation, existing Installations can continue to use Latin1_General_BIN collation.
In this blog post, I plan to :
- Give an Introduction to collations and options
- Illustrate how to select Latin1_General_BIN2 collation during the installation of a SQL Server Instance.
- List some useful queries
- List some useful related Knowledgebase articles
Introduction to collations
A SQL Server collation type is a set of rules predefined in your SQL server database that determines how data in the database tables is sorted, retrieved, and compared. There are numerous types of collations and options. Sage X3 uses the Latin1_General collation (code page 1252 for the English language). There is a different code page for each language because the order for characters will differ depending on the language. Sometimes the language is also referred to as the collation culture.
A Collation also has additional sort order settings that are worth knowing about:
Case Insensitive (CI) – Default during database installation will be this option case insensitive. This means that all your queries will execute in alphabetical order, whether you use upper or lower case or a combination of both.
Case Sensitive (CS) – with a case-sensitive collation, you need to get the upper and lower case correct, or the query will not return the expected results. For example, sensitive collation, SageX3.Abank is treated as a different object to sagex3.abank
Binary (BIN) Only the first character sorted according to the code point, and any following characters are sorted according to the byte value.
Binary2 (BIN2) All characters are sorted according to their code point values.
See below a practical example to illustrate the difference
Example from (https://stackoverflow.com/questions/35583412/what-is-the-difference-between-nvarchar-bin-collation-bin2-collation) Please note that Sage is not affiliated with this website and will not be held responsible for any content.
How to Select Latin1_General_BIN2 collation during the installation of your SQL Server instance.
Note: this section of the post gives you an illustrated insight on how to select a BIN2 Type collation and not a guide on installing and configuring a SQL instance.
Launch the setup.exe for SQL server in this example; I am using SQL 2019
Select to perform a new installation of Server 2019
Select the features you would like to install; In this example, I selected database engine services as a minimum
Give your Instance a suitable name, for example, SageX3, that will help you to identify the instance in future. In this example, I have used CollationTest
Now the interesting part setup your service accounts and select the collation tab, and then customize
Select the option for Binary-code point
Now on the server configuration page, you will see the Latin1_General_BIN2 collation, which will be installed.
Next, specify any additional configuration settings that you need for your SQL server.
Once the installation is complete, log into the Instance
Execute the query to check the database collations
USE master
SELECT NAME, COLLATION_NAME
FROM sys.databases
And that's how you can set up your instance with the Latin1_General_BIN2 Collation.
Useful SQL Queries
1. This will list the version of SQL Server installed
Select @@VERSION
2. You can list all the available collation combinations in the SQL server by using this query. It may be useful if you are creating a new database using a query. You can use this to look up the collation specifics like codepage and description.
Select Name, Description
FROM fn_helpcollations()
3. This query elaborates on the above and will give you the code page and the description of the 'Latin1_General_CS_AS' collation.
SELECT name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
FROM sys.fn_helpcollations ()
WHERE name = 'Latin1_General_CS_AS'
4. With this query, you can get a list of all databases and the database compatibility level. Compatibility level is associated with each database. It allows the behaviour of the database to be compatible with the specific version of the SQL Server it is running on.
SELECT name, compatibility_level
FROM sys.databases;
5. This will list all databases on the instance with the collation name.
USE master
SELECT name, collation_name
FROM sys.databases
Useful Links
Do I need to change the Sage X3 SQL Database collation to Latin1_General_BIN2 ?
How to change the Database collation
WARNING: KO-Collations for master and tempdb databases are not compliant
How To Get Basic Server Information From SQL Server
I hope you found this blog post useful if there is anything you would like to add or feel that will be useful to others, feel free to leave any comments