You can run the following statement to return a list of available character sets in MySQL:
SHOW CHARACTER SET;
This returns a list displaying the character set name, a description, its default collation, and its maximum length.
How to Find the Collations Supported by the Server in MySQL
Running the following statement lists all collations supported by the server in MySQL:
SHOW COLLATION;
However, the resulting list is quite long, and if you have a collation in mind, you can always filter the list with either the LIKE clause or the WHERE clause.
What is Collation in Databases?
In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.
For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.
Collation is also used to determine how accents are treated, as well as character width and Japanese kana characters. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).
Different database management systems will provide different collation options. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.
New Features in SQL Server 2017 (Database Engine)
Below is a list of new features added in the SQL Server 2017 database engine.
Support for Linux and Mac
For the first time since SQL Server was introduced back in 1989, SQL Server is available on Linux (Red Hat, SUSE, Ubuntu) and Docker. This means you can also install SQL Server 2017 on a Mac (by using a Linux image in a Docker container).
The initial release of SQL Server 2017 for Linux doesn’t include the full set of features available in the Windows release, but it’s a good start. Microsoft has stated that it is working on including more features in future releases.
For now, here’s a list of the main features available in SQL Server 2017 for Linux (as of its initial release).
SQL Server 2017: Available Features on Linux
SQL Server 2017 is available on Linux and Docker (which means that it’s also available on Mac). This is the first time SQL Server has been available on a non-Windows platform.
However, not all SQL Server features are available on Linux (at least, not in the initial release).
The following table outlines the main features available in the initial release of SQL Server 2017 on Linux. These are the same features available if you are running SQL Server on a Mac (given the Mac runs SQL Server 2017 via a Linux Docker container).
How to Find the Database Collations Supported by your SQL Server Instance
You can run the following query to return all the collations that are supported in your instance of SQL Server:
SELECT name, description FROM sys.fn_helpcollations();
Note that this returns quite a long list. Running that statement on SQL Server 2017 returns 3955 collations.
Full List of Collations Supported in SQL Server 2017
Below is a table of all collations supported in SQL Server 2017 (warning: It’s a long list!).
This list was returned by running the following code:
SELECT name, description FROM sys.fn_helpcollations();
That returns the following list of 3955 collations.
How to Change the Collation of a SQL Server Database using T-SQL
This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.
Here’s the basic code:
USE master; GO ALTER DATABASE Solutions COLLATE French_CI_AS ; GO
This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.
How to Backup a SQL Server Database using T-SQL
You can use the T-SQL BACKUP DATABASE statement to back up any SQL Server database.
This article shows you how to backup a database to disk. This creates a .bak file which can be used later to restore the database if required.
How to Rename a SQL Server Database using T-SQL
While you can certainly rename a SQL Server database in the SSMS GUI by right-clicking on the database name and selecting Rename, sometimes you might prefer (or need) to do it using Transact-SQL.
The most basic way to rename a database using T-SQL is like this:
-- Change to the master database USE master; -- Change the database name ALTER DATABASE Films Modify Name = Movies; GO
The only problem with this basic script is that it doesn’t change the name of the data files and log files. In most cases you’ll probably want to change the names of these files to match the new name. In that case you can take the following script and replace the database name with your own (as well as its file names and paths):
