If all the users of your instance of SQL Server 2005 speak the same language, you should pick the collation that supports that language. For example, if all the users speak French, select a French collation. If the users of your instance of SQL Server speak multiple languages, you should pick a collation that best supports the requirements of the various languages. For example, if the users generally speak western European languages, choose the Latin1_General collation.
When you support users who speak multiple languages, it is most important to use the Unicode data types nchar, nvarchar, and nvarchar(max), for all character data. Unicode was designed to prevent the code page conversion difficulties of the non-Unicode char, varchar, and text data types. Collation still makes a difference when you implement all columns using Unicode data types, because it defines the sort order for comparisons and the sorts of Unicode characters. Even when you store your character data using Unicode data types, you should pick a collation that supports most of the users in case a column or variable is implemented by using the non-Unicode data types.
SQL Server can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer.
If the collation specified, or the collation used by the referenced object, uses a code page not supported by the Windows operating system, SQL Server issues an error. Your response to the error depends on the version of the Windows operating system installed on the computer. Windows 2000 and later versions support all the code pages that are used by SQL Server collations. Therefore, the error message will not occur.