June 24, 2011 at 5:45 pm
When I install a new server, what collation should I use,
It looks like the default for SQL 2008 is SQL_Latin1_General_CP1_CI_AS.
But I heard our company's standard for install of sql server is Latin1_General_CI_AS, not sure why it is like this? I have to dig a little bit to see why we have this as a standard.
We have many SQL servers, some hosts databases of the applications we develop, some host databases of Vendor products.
What I'm concerned is if vendors test their software in the default collation of sql server, that's how it will work.
But if we use Latin1_General_CI_AS as our standard, will that cause problems for future?
I know once you install sql server and created databases with data, then if you want to change the collation, it will be a great pain.
Because the server setting changes, the database and collumns collation need to change too.
This is really frustrated part in SQL server for DBAs, I hope it can be an easier job in future sql versions.
Can someone help with my above question?
Thanks
June 27, 2011 at 9:07 am
I think Dannol Liu somes it up quite well...
Edit - seems the link is forbidden so here is the content of his response:
As you might know that the collation decides the sorting, data saving in pages. ... "In most of case, people will use Windows collation as the collation, except us-English which still use sql_latin1_general_cp1_ci_as." and you could visit his blog to see detailed information.
If your database is different to the instance collation, then you can have issues when joining to temporary tables or to tables in other databases, to get around this you can specify the collation of columns when creating temporary tables or force a collation using the COLLATE clause when comparing (joining) two text columns. Using the option database_default for the collation will make this independent of what the database collation actually is. See more about the COLLATE clause in Books Online.
It is better to stick to a single collation globally. Otherwise you will experience problems when working with temporary tables. And although it's always easier if collation was consistent through out everywhere you have your application, but if you are installing one a shared instance at a customer it is not always possible. Therefore in general it is best not to force or assume a collation except where it is necessary e.g. you may want a binary collation to force order and case sensitivity.
You should see little difference if the collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but both have instances where they are faster or slower than the other.
Latin1_General_CI_AS :- Latin1-General, case-insensitive, accent-
sensitive, kanatype-insensitive, width-insensitive
SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode
Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
You can get more idea from fn_helpcollations.
gsc_dba
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply