We’re excited to announce you can now change the default database collation from the Azure portal when you create a new Azure SQL Data Warehouse database. This new capability makes it even easier to create a new database using one of the 3800 supported database collations for SQL Data Warehouse.

Collations provide the locale, code page, sort order and character sensitivity rules for character-based data types. Once chosen, all columns and expressions requiring collation information inherit the chosen collation from the database setting. The default inheritance can be overridden by explicitly stating a different collation for a character-based data type.

Changing collation

To change the default collation, you simple update to the Collation field in the provisioning experience.

For example, if you wanted to change the default collation to case sensitive, you would simply rename the Collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.

Listing all supported collations

To list all of the collations supported in Azure SQL Data Warehouse, you  can simply connect to the master database of your logical server and running the following command:

SELECT * FROM sys.fn_helpcollations();

This will return all of the supported collations for Azure SQL Data Warehouse. You can learn more about the sys.fn_helpcollations function on MSDN.

Checking the current collation

To check the current collation for the database, you can run the following T-SQL snippet:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation;

When passed ‘Collation’ as the property parameter, the DatabasePropertyEx function returns the current collation for the database specified. You can learn more about the DatabasePropertyEx function on MSDN.

Learn more

Check out the many resources for learning more about SQL Data Warehouse, including:

• What is Azure SQL Data Warehouse?
• SQL Data Warehouse best practices
• Video library
• MSDN forum
• Stack Overflow forum