4

If you were building a new database in SQL Azure, which collation would you use?

SQL_Latin_General_CP1_AS_AI or Latin_General_AS_AI?

Reason I ask is that the default collation on SQL Azure is SQL_Latin_General_CP1_AS_AI as described here:

https://azure.microsoft.com/en-gb/documentation/articles/sql-database-general-limitations/

BUT

If you look at the MSDN article about Collation it states,

SQL Server supports Windows collations. SQL Server also supports a limited number (<80) of collations called SQL Server collations which were developed before SQL Server supported Windows collations. SQL Server collations are still supported for backward compatibility, but should not be used for new development work. For more information about Windows collations, see Windows Collation Name (Transact-SQL).

Taken from: https://msdn.microsoft.com/en-GB/library/ms180175.aspx

This suggests that SQL collation support is not recommended for new database development, yet that conflicts with the fact that the SQL Azure collation is SQL_Latin by default.

Appreciate people's thoughts on this.

Thanks.

juvchan
  • 6,113
  • 2
  • 22
  • 35
Owen
  • 43
  • 1
  • 4

1 Answers1

1

For me the answer is down to what you are planning to host on the database. If the application in question requires a SQL collation then you obviously have no choice, if it doesn't then your good to go.

I don't believe there is a contradiction, the documentation states:

SQL Server also supports a limited number (<80) of collations called SQL Server collations which were developed before SQL Server supported Windows collations. SQL Server collations are still supported for backward compatibility, but should not be used for new development work.

The key part here is "still supported for backward compatibility". A number of server based applications require SQL collation and are just getting updated to remove that requirement.

In summary: It all depends on the application you are deploying. If you don't require a SQL collation then don't use one, as the documentation suggests this is just around for backward compatibility.

Martyn C
  • 1,109
  • 9
  • 18
  • Thanks Martyn. The application can handle either type. We are just trying to pick the one that makes sense and is future proof. I guess my main concern comes from the fact that Azure is defaulting to SQL_Latin_General. If it defaulted to Latin_General then it would make sense that they were moving away from the SQL collations and I would use Latin_General. Moving away from the default option feels like we would be going against best practice. – Owen Apr 06 '16 at 15:58
  • I see what you mean it does seem a bit backward. I would go without the SQL then if you can most certainly. – Martyn C Apr 06 '16 at 16:00
  • There must be a good reason that the SQL Azure team chose SQL_Latin_General_CP1_AS_AI. If you don't need a specific collation surely the default one is the way to go, we just need to know the true reasoning behind the default! – Damo Apr 06 '16 at 21:08
  • It's chosen because so many applications still require SQL collations, so to try and avoid confusion and potential deployment issues I think it still makes sense for it to be default, but as the documentation points out, avoid it where possible. – Martyn C Apr 07 '16 at 07:35
  • 1
    This would also agree with Martyn. Think Latin_General is the way to go here. "The default collations are set to the oldest available version that is associated with each specific locale. This is due to backwards compatibility reasons. Therefore, these are not always the recommended collations." https://msdn.microsoft.com/en-GB/library/ms143508(v=sql.105).aspx I will leave this open for a while longer to see if anyone else has any thoughts but we might press on. – Owen Apr 07 '16 at 09:04