Sql-server – create a Azure SQL Server (server) with a different collation

azure-sql-databasecollationsql servert-sql

I want for my application the same collation at all database levels (server, database, columns): Latin1_General_CI_AS
This is recommanded by Dan Guzman to have constistent collations and get the collation configuration right from the start.

The default collation for a Azure SQL Server (server) is SQL_Latin1_General_CP1_CI_AS. I would like to change this to Latin1_General_CI_AS. Or create a new Azure Sql Server (server) with the collation Latin1_General_CI_AS. For on premise SQL Servers it is possible.

In the SQL Azure Collation Documentation from 2010 is stated that …the server and database collations cannot be configured in SQL Azure… Besides this, I didn't found something useful.

I would be totally happy if I could create a new Azure SQL Server (server) with a collation Latin1_General_CI_AS.

Is it somehow possible or exists a workaround?
I really appreciate any help.

Best Answer

The collation for user databases can be specified for SQL Azure Database user databases nowadays (e.g. CREATE DATABASE YourDatabase COLLATE Latin1_General_CI_AS;).

EDIT:

The logical Azure SQL Server collation is fixed at SQL_Latin1_General_CP1_CI_AS and is not configurable (as of this writing). However, this collation is only used for system objects in master, tempdb, and user databases. It does not affect the collation of user objects in tempdb, which is the collation of the current Azure SQL Database context (not logical SQL Server) unless otherwise specified. This behavior differs from on-prem and IaaS SQL Server instances where the instance collation also determines the tempdb default database collation used for both system and user objects, therefore having a more significant impact.

With this in mind, you might think of system and user collations orthogonally in the Azure SQL Database world because you won't run into collation conflict errors in the user space as long as you use the same collation consistently. Also, if you use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS as your user database collation, you'll still get the same sorting and comparison behavior as system object names because Windows sorting and comparison rules are used for Unicode data and system object names are stored as nvarchar(128) (a.k.a sysname).

Related Solutions

Sql-server – Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance

Quite honestly, I think your easiest approach will be:

backup your user databases
uninstall SQL Server
reinstall SQL Server with the right collation
restore your databases
fix the collation on the user databases

Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases since then?

Sql-server – Collation conflict when running dynamic sql FOR XML PATH

You'll need to put a COLLATE hint in your join clause. Basically, choose one of the two collations, i.e. Latin1_General_CI_AS and place it after the column name that uses the other collation.

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''       
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']'' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] **COLLATE Latin1_General_CI_AS** = [master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')'

Best Answer

Related Solutions

Sql-server – Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance

Sql-server – Collation conflict when running dynamic sql FOR XML PATH

Related Question