3

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.

Improve this question

1 Answer 1

Reset to default
5

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).

Improve this answer
4
  • this works,i just tested,i was looking at blog post from 2010
    – TheGameiswar
    Feb 16, 2017 at 13:58
  • Thank you for your answer. I edited my question to make it more clear. With SQL Server (logical) I meant the SQL Server (server). I used the term logical because it is used in the official Documentation.
    – a1300
    Feb 18, 2017 at 10:47
  • 1
    @mathayk, I added more information to my answer to address your question.
    – Dan Guzman
    Feb 18, 2017 at 15:29
  • @DanGuzman Is the user objects in tempdb "feature" mentioned anywhere in the official documentation
    – ErikEJ
    Oct 28, 2019 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.