Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-23 : 09:45:01
|
| Andrew writes "I have read books on line and am still a bit confused as people talk about using dts and bcp to copy the data to a new database etc so just wanted to confirm what I think is correct. The problem.We have a number of SQL servers that have been installed with different collation settings also the columns have been created with the collate clause so this don't necessarily match the database collations. This causes a problem when we try to do queries across databases and servers and we get collation conflict errors. So I am trying to set the collation for all servers, databases, tables and columns to be the same.Solution.I have scripts that will generate the alter statements for tables and columns that are different to the collation that we are going to use. It also lists constraint and indexes that are referencing these columns and create the necessary drop and create statements for these.My question. To alter the collation settings I am going to script alter database, alter table and alter columns and drop and re-create any constraints/indexes that reference these columns to set the collation to the correct collation. Can I run this on the database or do I need to create a new database and bcp/dts the data across. i.e. is the data collation changed when the alter column statement is issued." |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-01 : 18:42:18
|
| I'm working from memory here, but I think the existing data is left alone, and only newly inserted / updated data will honour the new COLLATE statement on the column.A quick test might be your best approach!Given the morass you describe I think I would chose to re-make all the tables in a new database and use a bunch of INSERT INTO .. SELECT * FROM ... to get the data into the new, replacement, database.Kristen |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-09-02 : 05:47:04
|
quote:
Originally posted by AskSQLTeamis the data collation changed when the alter column statement is issued
Yes. The collation of existing columns will only be changed by changing the column's collation, not the database's. When you change the collation of a non-Unicode column, all the data in that column will be converted to the codepage (character set) of the new collation: this includes down-converting characters that don't exist in the codepage of the new collation (at worst, replacing them with question marks). If your old and new collations use the same codepage this won't be a concern.If you had unique or primary key constraints on the column, you'll find that these can't be recreated if the data contains strings that compare different in the old collation but equal in the new. The most obvious place this happens is going from a case-sensitive to a case-insensitive collation, but there are other oddities, particularly between SQL and Windows collations. For example, the following fails to create the new primary key:CREATE TABLE #ChangeCollation ( pk varchar(10) COLLATE SQL_Latin1_General_Cp1_CI_AI NOT NULL, CONSTRAINT ChangeCollation_pk PRIMARY KEY CLUSTERED (pk))INSERT INTO #ChangeCollation SELECT 'thorn'INSERT INTO #ChangeCollation SELECT 'þorn'ALTER TABLE #ChangeCollationDROP CONSTRAINT ChangeCollation_pkALTER TABLE #ChangeCollationALTER COLUMN pk varchar(10) COLLATE Latin1_General_CI_AI NOT NULLSELECT *FROM #ChangeCollationALTER TABLE #ChangeCollationADD CONSTRAINT ChangeCollation_pk PRIMARY KEY CLUSTERED (pk) |
|
|
|
|
|
|
|
|
Column Collations