Collation SQL 2005 sql_latin1_general_cp1_ci_as


  • Hi,

    Our application uses SQL 2005 database with Latin1_General_CI_AS as collation. There are nearly a million records in this database.

    I need to move my data to another SQL 2005 server which has 'SQL_Latin1_General_CP1_CI_AS ' as collation.

    I know there would be lots of issues. Do you know what are the likely issues that we would face.

    Since there are lots of modules dependent on this database, it would be difficult to test each and every component if all works ok or not.

    Is this advisable to move to another database with this non standard SQL_Latin1_General_CP1_CI_AS  collation?





    Thursday, September 09, 2010 11:34 AM


  • Hi

    The main problem you will have is when you use Tempdb which will have the server deafault collation.

    If you are going to convert your database you will have problems converting columns that are used in indexes, foreign keys, and computed columns. You could drop all of these objects change collation to server default then rebuild them. You should test this first of course. I have had to do this before and it went through ok. The script below if run in the context of your database will create an alter script for every column which needs to be changed -when you reun the results they will alter the tabls and change collation per column. You will hit problems on indexes etc as I mentioned earlier. If you don't have indexes on these columns you may be ok.

    Remember please do this in test and make sure you rapplication is happy before applying to live.

    select 'alter table [' + + '].[' + + '] alter column [' + + '] ' + + case when not in ('text', 'sysname') then '(' + case when c.max_length > 0 then 
    case when not in ('nchar', 'nvarchar') then convert(varchar, c.max_length) else convert(varchar, c.max_length/2) end else 'max' end +
    ')' else '' end + 
    case when c.is_nullable = 0 then ' NOT ' else '' end + ' NULL' 
    from (sys.columns c inner join sys.types ty on c.system_type_id = ty.system_type_id) inner join 
    (sys.objects t inner join sys.schemas s on t.schema_id = s.schema_id) on c.object_id = t.object_id 
    where t.type='U' and c.collation_name is not null and <> 'sysname' 
    AND c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
    order by,, c.column_id;




    (SQL_Latin1_General_CP1_CI_AS was the default collation for SQL 2000)
    Friday, September 10, 2010 3:17 PM