SQL Server Collation Choices

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » SQL Server Collation Choices

SQL Server Collation Choices

Rate Topic

Display Mode

Topic Options

Author

Message

NigelFN

Posted Wednesday, November 28, 2007 9:32 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:31 AM
Points: 65, Visits: 194

Hi there, please can I get some opinions on the subject of Server Collation.

We are in the process of configuring a new SQL Server 2005 and we wish to choose the best Collation setting - for backwards compatibility, and to ensure that we do not hit problems in the future.
We realise that the main consideration is to have consistency between various environments.

The potential choices are either “SQL_Latin1_General_CP1_CI_AS” or “Latin1_General_CI_AS”.

Microsoft documentation shows that “SQL_Latin1_General_CP1_CI_AS” is the US default, but there is also much talk about the SQL prefixed collations being superseded.

We do not make heavy use of UNICODE characters.

We are currently favouring the use of “SQL_Latin1_General_CP1_CI_AS” – we have a legacy SQL 2000 database that will be transferred to this server.
Please advise whether the choice of “SQL_Latin1_General_CP1_CI_AS” is likely to cause any un-toward problems in the future.

Many thanks,

Post #426948

Steve Jones - SSC Editor

Posted Sunday, December 02, 2007 1:22 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

I'm not really a collation expert, but since 2000 (and in 2005), databases can have separate collations, so there isn't an impact with the server collation.

If you are moving data between databases, you'd have to really dig in and see if there are potential issues with the data you use.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #428528

matt stockham

Posted Tuesday, December 04, 2007 12:28 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:36 AM
Points: 750, Visits: 2,946

It's true that databases can have their own collations, but you can run into problems if they don't match the tempdb collation - it depends on how your code is written. I won't make any recommendations because I'm definitely not an expert in this area and it's been too long since I had to look into it in detail, but this might be one of those cases where running multiple instances of SQL is beneficial .... one instance for legacy apps, and one for whichever collation you choose to use going forward.

Post #429498

Sinisa-425520

Posted Wednesday, December 05, 2007 8:36 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2008 2:37 AM
Points: 13, Visits: 43

You should be aware that SQL Collations are going to be depreciated.
Another thing is use of TERTIARY_WEIGHTS function that could kill performance of any server if you use SQL collations.
In other terms - use of SQL collation and ANSI defined columns (char,varchar) in some cases(queries) yields use of TERTIARY_WEIGHTS function and table scans no matter if you have index on column or not. If you have big tables it could be a very big problem.

So, if you can, go with clean install with windows collation. Also, change database collation and column level collations to the same - windows collation.

A good starting point to go deeply is http://blogs.msdn.com/michkap/default.aspx

rgds
Sinisa

Post #429812

huskerwendy

Posted Thursday, December 06, 2007 9:19 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 1:22 PM
Points: 961, Visits: 409

When we set up our SQL Server 2005 server, we did some research on the collation settings and we decided on using the Windows collation (Latin1_General_CI_AI) because of the sort issues using Unicode data and non-Unicode data and also because MS is going to deprecate the SQL Collations. http://support.microsoft.com/kb/322112

However, when we migrated some of our SQL Server 2000 databases to SQL Server 2005, we did experience some issues because of the different collation settings between the TempDB and the migrated databases. We wrote a script to go through and alter the database, tables, and columns to change the collation settings of them to the Windows collation and everything works fine.

Wendy Schuman

Post #430270

Mazharuddin Ehsan

Posted Wednesday, December 26, 2007 11:55 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 378, Visits: 2,381

We are supporting SQL Server installations which has databases with non English data.
Our practice is to keep the server and database collation as SQL_Latin1_General_CP1_CI_AS
On the object level we use nvarchar for non English language string data and the respective collation.
This serves the purpose well.

Regards,
Maz

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #436455

« Prev Topic | Next Topic »

Permissions