Collation issue - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Collation issue

Author

Topic

MariaM
Starting Member

17 Posts

Posted - 2006-02-20 : 09:37:32

Hi!

I have an SQL-server with several databases. Unfortunately theese were created with different collations. I would like to know if there is an easy way to find out the collation of each database on the server and also to change the collation for the databases which has the wrong collation.

Any tips with scripts etc would be appreciated.

/Maria

mallier
Starting Member

24 Posts

Posted - 2006-02-20 : 09:57:05

SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE SQL_Latin1_General_CP850_BIN '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END

FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')

cheers,
http://mallier.blogspot.com

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 10:19:59

I don't think chancing the collation on the tables/database will alter the already-stored-data - unless it is exported/imported of transferred to a temporary table and back again.

You can check the existing collations with:


SELECT	[Database]  = name,
	[Collation]	= DATABASEPROPERTYEX(name, 'Collation')
FROM	master.dbo.sysdatabases

As an alternative you could explicitly specify the Collation when you do intra-database operations (as they are likely to be pretty rare), and explicitly specific collations on ALL Create Table statements for TEMPOARY tables (#TempTable and @TableVar) - so that the difference between the current database and the TEMPDB database doesn't muck things up!

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-02-20 : 11:03:31

quote:
Originally posted by Kristen

I don't think chancing the collation on the tables/database will alter the already-stored-data

Alter it how? If you change the collation on a column such that the character set changes it will try to keep the characters the same. If the characters in that column are not in the new collation's character set they will get converted into the 'nearest' character (e.g. by stripping diacritics or, if there is no such suitable character, turning it into a question mark).
This only affects char/varchar/text columns where the character set varies with collation. nchar/nvarchar/ntext columns all have the same character set (Unicode) irrespective of collation.

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 11:35:36

"Alter it how"

Sorry ... attempting less ambiguity!:

If you alter the collation on a column from CollationA to CollationB does all the data in that column get "converted" to CollationB?

I've never done this, and am too lazy to look up the syntax, but maybe it involves making a new Temp table, INSERTing the data into it, dropping the original table and renaming the Temp table to the original name - which would (presumably?!) convert the existing data to the CollationB

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-02-20 : 12:48:49

quote:
Originally posted by Kristen
If you alter the collation on a column from CollationA to CollationB does all the data in that column get "converted" to CollationB?

Yes, all the characters get converted to characters in the character set of CollationB, on the basis of what Unicode characters they represented in CollationA.

For example if you go from CP1257 to CP1252, look at what happens to row 191:


DROP TABLE CollationTest
GO

CREATE TABLE CollationTest(
  n int PRIMARY KEY,
  c1 varchar(10) COLLATE Latin1_General_BIN NOT NULL,
  c2 varchar(10) COLLATE Lithuanian_BIN NOT NULL
)

DECLARE @n int
SET @n = 0

-- fill both varchar columns with same bit patterns
SET NOCOUNT ON
BEGIN TRANSACTION
WHILE @n < 256 BEGIN
  INSERT INTO CollationTest (n, c1, c2)
  SELECT @n, CAST(@n AS binary(1)), CAST(@n AS binary(1))

  SET @n = @n + 1
END
COMMIT TRANSACTION
SET NOCOUNT OFF

GO

SELECT n AS row,
  c1,
  UNICODE(c1) AS unicode_codepoint1,
  ASCII(c1) AS char_codepoint1,
  c2,
  UNICODE(c2) AS unicode_codepoint2,
  ASCII(c2) AS char_codepoint2
FROM CollationTest
WHERE UNICODE(c1) <> UNICODE(c2)

GO

ALTER TABLE CollationTest
ALTER COLUMN c2 varchar(10) COLLATE Latin1_General_BIN

GO

SELECT n AS row,
  c1,
  UNICODE(c1) AS unicode_codepoint1,
  ASCII(c1) AS char_codepoint1,
  c2,
  UNICODE(c2) AS unicode_codepoint2,
  ASCII(c2) AS char_codepoint2
FROM CollationTest
WHERE UNICODE(c1) <> UNICODE(c2)

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 12:52:16

Very useful Arnold, Cheers!

Dunno why but I had assumed that existing data would retain its collation.

Kristen

MariaM
Starting Member

17 Posts

Posted - 2006-02-21 : 08:24:12

Hi again !

Thanks for your answers ! As I understand the data for the columns you change will be converted to the new collation you chose. How do you know if they will be converted to the same characters ? Can somone explain how this works and how you can check this ?

/Maria

Kristen
Test

22859 Posts

Posted - 2006-02-21 : 09:17:29

If you use Arnold's example and change Lithuanian_BIN to the CURRENT collation you are using and the TWO occurrences of Latin1_General_BIN to your NEW collation you should get a before/after table that you can eyeball for comparison.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources