SQL Server Performance Forum – Threads Archive

question on collation

guys: want to ask which collation I need to use when I create a database – windows collation or sql server collation?
regardingless the legacy database and other stuff, just a purely new database. thanks,

Take the default settings during the setup and BOL says
The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release. During setup, the master, model, tempdb, msdb, and Distribution system databases are assigned the same default collation as the default collation chosen for the instance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

thanks, satya. besides, based on my understanding, it’s quite difficult to change the sql collation after it has been set up during installation. need to rebuild master and other sys dbs. right?

Yes it can be quite difficult to change. On a new system I would select the default Windows collation (Latin1_General_CI_AS)

It can be difficult to change, but actually that is more a question for SQL 6.5 and 7.0 installations. SQL 2000 supports different collations for columns within any user table, and a default collation within each database, and the default collation for the server instance. There are two issues that you must be aware of: (1) When you create a temporary table, the columns get the default collation of the server. When you try to join that temporary table to a user table on a column that has a different collation, you get an error message about SQL not being able to resolve a collation conflict. (2) When you run a query that joins a user table to a system table in the master database, again with different collations – produces the same error. There is a simple work-around: for #1, always use the COLLATE DATABASE_DEFAULT clause for each column when creating a temporary table, and for #2, always use that clause next to the column from the system table in your ON clause. Plus the SQL_Latin1_General_CI_AS is the default collation when SQL is installed with the US English locale, otherwise it conforms to the local collation (in that case, you cannot even select SQL_Latin1_General_CI_AS in the installation wizard …).

True and with extensive information by Argyle I feel you are well versed with the collation issues you may use. From SQL 2000 onwards the collation is maintain upto the column level and can be effective for the new data only, for old data you need to re-import. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, since we are in the topic of collation. I like to know if my DB is using the default collation,but
my master is using another collation, does it have any side effect or problem that you all know
of ? My master is in different collation due to my client location in another country, hence
the default setup will use the local window collation. Please advise which system db to rebuild beside the master db
and any know side effect on the performance or query Thank you

My earlier post specified the two problem areas that you must be aware of: ******************** (1) When you create a temporary table, the columns get the default collation of the server. When you try to join that temporary table to a user table on a column that has a different collation, you get an error message about SQL not being able to resolve a collation conflict. (2) When you run a query that joins a user table to a system table in the master database, again with different collations – produces the same error. There is a simple work-around: for #1, always use the COLLATE DATABASE_DEFAULT clause for each column when creating a temporary table, and for #2, always use that clause next to the column from the system table in your ON clause. ******************** So when creating a temporary table, always do it like: CREATE TABLE #TMP
(Col1 VARCHAR(5) COLLATE DATABASE_DEFAULT,
Col2 VARCHAR(5) COLLATE DATABASE_DEFAULT,
Col3 INT) And when joining a user table to a system table, do it like this: SELECT T1.*
FROM dbo.MyTable T1
INNER JOIN dbo.sysobjects T2
ON T1.name = T2.name COLLATE DATABASE_DEFAULT … or this: SELECT T1.*
FROM dbo.sysobjects T1
INNER JOIN dbo.MyTable T2
ON T1.name COLLATE DATABASE_DEFAULT = T2.name If the collations already match, the query still works fine.

]]>