A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS
, with 8 databases … for a new application, we have been asked to install a SQL Server 2008 with collation SQL_Latin1_General_BIN
.
In order to maintain a single database server, and then also by addressing the previous db migration to the new version, which instance collation should be adopted?
Thanks a lot
Bye,
Francesco
Best Answer
The default collation is based on the Windows system locale of your server.
SQL_Latin1_General_CP1_CI_AS
is usually the default collation for computers based in the North American locale. Collation can be changed if you have a reason for it, like: sorting order, case sensitivity, accent sensitivity, non-English characters, width.Collation for a DB can be set at the time of the creation of the DB, so you can have a server that has DBs with different collations. The
Alter database
command can be used to change the collation of all newly created objects in the DB. I have used the defaultSQL_Latin1_General_CP1_CI_AS
all the time and have not had any issues, but then I do more DB administration that development.