Which collation to use

[djj55]

Hello,
I have read several posts about collation, but did not find exactly what I wanted. We are planning on going to SQL 2008 from 2000. Because the 2000 server has both SQL_Latin1_General_CP1_CI_AS (SQL collation) and Latin1_General_CI_AS (Windows collation) we would like to standardize.

Thus my question is which of these two is better?

Thanks,
djj

 

[tlbroadbent]

The best collation to use depends on your locale. Check out the information regarding collation setup in SQL BOL.

http://msdn.microsoft.com/en-us/library/ms143508(SQL.90).aspx

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[mrdenny]

I believe that those two collations map to the same collation.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[djj55]

Hello Terry and Denny,
Thank you for the replies.

The two types may map to the same collation but try a join and you are using [blue]COLLATE DATABASE_DEFAULT[/blue] which is killing our performance (no index).

Being in the USA the SQL_Latin1 is indicated as the default and Terry's link indicates that the SQL_Latin1 is "used for compatibility with earlier versions of SQL Server".

So my question, is there any performance difference between the two?

It looks like I should use the Windows collation with the new server.

Thank you,
djj

 

[tlbroadbent]

According to BOL the default for US English is the SQL collation.

English (United States) 0x409 SQL_Latin1_General_CP1_CI_AS

As far as performance goes there should be no difference.


Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)