Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Client collation and SQL Server 2005

We're upgrading an existing program from Win2k/SQL Server 2k to Windows 2003 and SQL Server 2005 as well as purchasing a new program that also uses 2k3/2k5. The vendor says that for us to host both databases we need to get the Enterprise version because the softwares clients use different collation for the connections and only Enterprise supports this.

I cannot find anything on MS's site to support this and, honestly, don't want to pay the extra for Enterprise if the Standard edition works. Am I missing some not talked about feature of SQL Server or is this, as I suspect, a vendor trying to upsell me?

like image 931
dragonmantank Avatar asked Aug 04 '08 00:08

dragonmantank


People also ask

What is the difference between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS?

The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

What collation should I use for SQL Server?

However here are the settings we typically recommend: Set the default collation to SQL_Latin1_General_CP1_CI_AS. Ensure that you have SQL Server running in Case Insensitive mode. We use NCHAR, NVARCHAR string types so all data is unicode, so no character set is specified.

How do I change SQL Server collation settings?

Set or change the database collation using SSMS Alternatively, if the database already exists, right-click the database that you want and select Properties. Select the Options page, and select a collation from the Collation drop-down list. After you are finished, select OK.

What is SQL Server default collation?

Default server-level collation is SQL_Latin1_General_CP1_CI_AS.


1 Answers

All editions of SQL Server 2000/2005/2008 support having multiple databases, each using their own collation sequence. You don't need the Enterprise version.

When you have a database that uses a collation sequence that is different from default collation for the database server, you will need to take some extra precautions if you use temporary tables and/or table variables. Temp tables/variables live in the tempdb database, which uses the collation seqyuence used by by the master databases. Just remember to use "COLLATE database_default" when defining character fields in the temp tables/variables. I blogged about that not too long ago, if you want some more details.

like image 119
Chris Miller Avatar answered Nov 02 '22 18:11

Chris Miller