Configure SharePoint – SQL Server Collation

 

This one comes up every few months…the topic never goes away.  The issue is whether you should change the default SQL Server Collation on SQL Builds when plan on installing SharePoint.

The default SQL Collation is sql_latin_general_cp1_ci_as. The CI stands for Case Insensitive.  If you read the Microsoft document on SQL Server Preparation you will find that the “The SQL Server collation must be configured for case-insensitive.” http://technet.microsoft.com/en-us/library/cc263187.aspx 

In the following line, Microsoft describes database configuration for SharePoint: “The SQL Server database collation must be configured for case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. This is to ensure file name uniqueness consistent with the Windows operating system.”

THE Database configuration is automatically set to the right Collation when SharePoint Creates a database:

“Note SharePoint Portal Server 2003 and SharePoint Server 2007 will automatically create databases with the correct collation settings. Notice that the collation settings apply to the database level, but not to the SQL Server level.”  http://support.microsoft.com/kb/843578

In short, I don’t care what the default server collation is as long as its case insensitive, but out of preference I would leave it to the default collation.  This is the configuration that I have seen Microsoft choose in the products that have SharePoint WSS installation such as SBS and WSS.