change collation field

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » change collation field

change collation field

Rate Topic

Display Mode

Topic Options

Author

Message

pedro.ribeiro

Posted Tuesday, May 12, 2009 4:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982

Hi - I need to change a collation of a field.

The field is varchar(20).

I need to change it, from SQL_Latin1_general_cp1_CI_AS to latin1_general_ci_as.

Someone created this field with this collation, but i need to put the collation equal to the other tables.

This field as only data , like this : s4.01-12312 or like this 0000233

Can i lose data when changing the field collation?

tks,

Pedro

P.S when changing the collation of this field, i receive the folowing message:

Server: Msg 5074, Level 16, State 8, Line 1
The index 'Indice_RGC' is dependent on column 'RGC'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN RGC failed because one or more objects access this column.

Do i have to disable the index and then enable it after the collation alter?

Post #714822

Ramesh Saive

Posted Tuesday, May 12, 2009 4:35 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

To change the collation of a column which is a part of an INDEX, your have to re-create the INDEX(es)

IF ( OBJECT_ID( 'tempdb..#test' ) IS NOT NULL )
	DROP TABLE #test
	
CREATE TABLE #test( SomeCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )

CREATE INDEX IX_1 ON #test( SomeCol )

DROP INDEX IX_1 ON #test 

ALTER TABLE #test ALTER COLUMN SomeCol VARCHAR(10) COLLATE Latin1_General_CI_AS

CREATE INDEX IX_1 ON #test( SomeCol )

--Ramesh

Post #714845

pedro.ribeiro

Posted Tuesday, May 12, 2009 6:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982

Can i lose some data, because of the change of collation?

Thanks,

Pedro

Post #714903

Ramesh Saive

Posted Tuesday, May 12, 2009 6:49 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

Don't worry, you wont lose any data if you change the collation. But since this behaviour hasn't been documented very well, I suggest you take the backup of the table/database before changing the collation.

--Ramesh

Post #714950

pedro.ribeiro

Posted Tuesday, May 12, 2009 6:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982

ok.

Thank you very much for your help,

Pedro

Post #714960

« Prev Topic | Next Topic »

Permissions