locked
Best practices for globalization and collation RRS feed

  • Question

  • I'm trying to find the best way to implement a globalization feature where the user can change sort order of a table depending on the language of UI. Note that the application has SQL backend, which its collation cannot be changed on the fly. The data used in the application can be very large (>8000 records) so we cache x number of records instead of loading everything into the memory. Then it is displayed in DataGrid in a Form. Could someone give me a recommendation? The application is written in C#.
    Friday, July 16, 2010 6:03 PM

Answers

  • Hmn, does it relate to SQL Server? You can use N letter and NAVARCHAR(MAX) datatype to store unicode data such as INSERT INTO tbl VALUES (N'tetststs')
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 18, 2010 7:09 AM
  • Sandeep

    Only if you join on THAT column  you need to specify COLLATION .... so if you join on PK =Int there is no need to specify COLLATION


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 21, 2010 11:51 AM
  • Agreed, Uri Dimant and But for INT (other few) data type column, the collation is the Database default collation <database default>....that is why the collation need not to specify in the JOIN.

    Adding to my above post, the collation is for "Region Specific" DB otherwise MS could have come up with a “mixed collation” type to use any language. I mean the collation is basically used for specific region that does not mean multiple collations in a DB.

    Thanks,

    Sandeep

     

     

    Wednesday, July 21, 2010 12:38 PM
  • 1. If your application DB collation is not same as SQL Server Instance Collation then you will not be able to use "#" temp table directly. (note: you need to use collation type in the time of creation)

    Not a problem: you need to use the statement « COLLATE DATABASE_DEFAULT » when creating your #temp table; without forgetting that the current database in use must not be the tempdb database but the real database; for example:

    -- Won't work if the current database is tempdb or not the target database;
    -- so we make sure to have the correct database in use:
    Use TheDatabase
    Go

    Create Table #TemporaryTable (t nvarchar(100) collate database_default, ....)
    ...


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Sandeep Mishra> wrote in message news:de0ae06b-f337-4b57-ab08-4bac6912b6b2@communitybridge.codeplex.com...

    Please do not change the collation of Table/Column level because if your database has multiple collations then you need to specify the collation type in JOIN and other sql query.



    I have an idea, it may help you. Just have a table with the entire UI display label text message. And show them in browser. But in database when you store the actual data, store the data in NVARCHAR type.



    Issue you will face if you change your database collation:

    1. If your application DB collation is not same as SQL Server Instance Collation then you will not be able to use "#" temp table directly. (note: you need to use collation type in the time of creation)

    2. If your application DB table/Column level collations are different then you need to use collation type in the JOIN and other query.



    If you want to use collation, then you can go region wise database collation But please note that when use the temp DB you will face issues if you are using multiple collation DBs for same instances. So you need to consider the multiple instances also.



    "I would like to make them independent from each other."



    I would suggest to check alternative solution rather than to change the database collation.



    Thanks,

    Sandeep

    Sunday, July 25, 2010 4:16 AM
  • Sylvain

    If  you migrate a database and the server collation does not match to then alter all procedure where  you reference to temporary table could be a big headache 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 7:11 AM
  • Agreeing with all above that mixed collation is a big headache.

    Dynamic SQL is the best way to go:

    USE tempdb;
    SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
    INTO Product
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID < 700
    UNION ALL
    SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID >= 700
    ORDER BY New_ID
    
    DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE Latin1_General_100_BIN'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    /*
    ProductID	ProductName	ListPrice	Color
    1	Adjustable Race	0.00	NULL
    3	BB Ball Bearing	0.00	NULL
    2	Bearing Ball	0.00	NULL
    316	Blade	0.00	NULL
    324	Chain Stays	0.00	NULL ....
    */
    
    SET @SQL='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    GO
    /* ProductID	ProductName	ListPrice	Color
    1	Adjustable Race	0.00	NULL
    879	all-purpose bike stand	159.00	NULL
    712	awc logo cap	8.99	Multi
    3	BB Ball Bearing	0.00	NULL
    2	Bearing Ball	0.00	NULL
    877	bike wash - dissolver	7.95	NULL
    316	Blade	0.00	NULL .....
    */
    DROP TABLE tempdb.dbo.Product
    
    
    

    Dynamic SQL link: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    Collation link: http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 7:59 AM

All replies

  • Hmn, does it relate to SQL Server? You can use N letter and NAVARCHAR(MAX) datatype to store unicode data such as INSERT INTO tbl VALUES (N'tetststs')
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 18, 2010 7:09 AM
  • Sorry if I was not clear. Yes, it does relate to SQL Server. All the textual data is actually already stored in Unicode. The problem is that you cannot change the collation of a database in SQL Server on the fly. Currently when the application shows a table, the table's collation is always same as the database's collation. I would like to make them independent from each other.

    Since the application uses large set of data, caching is necessary. You can't just sort the table in memory especially if you have a large set of data since you would get a stack overflow.

    Monday, July 19, 2010 5:39 PM
  • Ok,   you can change COLLATION on the column level. You will need to change a data type to NVARCHAR(c) to have multilanguage support
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 21, 2010 5:49 AM
  • Please do not change the collation of Table/Column level because if your database has multiple collations then you need to specify the collation type in JOIN and other sql query.

     

    I have an idea, it may help you. Just have a table with the entire UI display label text message. And show them in browser. But in database when you store the actual data, store the data in NVARCHAR type.

     

    Issue you will face if you change your database collation:

    1.       If your application DB collation is not same as SQL Server Instance Collation then you will not be able to use “#” temp table directly. (note: you need to use collation type in the time of creation)

    2.       If your application DB table/Column level collations are different then you need to use collation type in the JOIN and other query.

     

    If you want to use collation, then you can go region wise database collation But please note that when use the temp DB you will face issues if you are using multiple collation DBs for same instances. So you need to consider the multiple instances also.

     

    “I would like to make them independent from each other.”

     

    I would suggest to check alternative solution rather than to change the database collation.

     

    Thanks,

    Sandeep

    Wednesday, July 21, 2010 11:33 AM
  • Sandeep

    Only if you join on THAT column  you need to specify COLLATION .... so if you join on PK =Int there is no need to specify COLLATION


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 21, 2010 11:51 AM
  • Agreed, Uri Dimant and But for INT (other few) data type column, the collation is the Database default collation <database default>....that is why the collation need not to specify in the JOIN.

    Adding to my above post, the collation is for "Region Specific" DB otherwise MS could have come up with a “mixed collation” type to use any language. I mean the collation is basically used for specific region that does not mean multiple collations in a DB.

    Thanks,

    Sandeep

     

     

    Wednesday, July 21, 2010 12:38 PM
  • 1. If your application DB collation is not same as SQL Server Instance Collation then you will not be able to use "#" temp table directly. (note: you need to use collation type in the time of creation)

    Not a problem: you need to use the statement « COLLATE DATABASE_DEFAULT » when creating your #temp table; without forgetting that the current database in use must not be the tempdb database but the real database; for example:

    -- Won't work if the current database is tempdb or not the target database;
    -- so we make sure to have the correct database in use:
    Use TheDatabase
    Go

    Create Table #TemporaryTable (t nvarchar(100) collate database_default, ....)
    ...


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Sandeep Mishra> wrote in message news:de0ae06b-f337-4b57-ab08-4bac6912b6b2@communitybridge.codeplex.com...

    Please do not change the collation of Table/Column level because if your database has multiple collations then you need to specify the collation type in JOIN and other sql query.



    I have an idea, it may help you. Just have a table with the entire UI display label text message. And show them in browser. But in database when you store the actual data, store the data in NVARCHAR type.



    Issue you will face if you change your database collation:

    1. If your application DB collation is not same as SQL Server Instance Collation then you will not be able to use "#" temp table directly. (note: you need to use collation type in the time of creation)

    2. If your application DB table/Column level collations are different then you need to use collation type in the JOIN and other query.



    If you want to use collation, then you can go region wise database collation But please note that when use the temp DB you will face issues if you are using multiple collation DBs for same instances. So you need to consider the multiple instances also.



    "I would like to make them independent from each other."



    I would suggest to check alternative solution rather than to change the database collation.



    Thanks,

    Sandeep

    Sunday, July 25, 2010 4:16 AM
  • You have to dynamically build your query string and add the appropriate Collate to the Order By statement;
    for example:

    Select * from MyTable Order By Column1 Collate French_CI_AS;

    There shouldn't be any problem doing that in C#.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <lambdaprime> wrote in message news:f79233e8-e8db-4783-b30d-697ff9995ff9@communitybridge.codeplex.com...

    I'm trying to find the best way to implement a globalization feature where the user can change sort order of a table depending on the language of UI. Note that the application has SQL backend, which its collation cannot be changed on the fly. The data used in the application can be very large (>8000 records) so we cache x number of records instead of loading everything into the memory. Then it is displayed in DataGrid in a Form. Could someone give me a recommendation? The application is written in C#.

    Sunday, July 25, 2010 4:22 AM
  • Sylvain

    If  you migrate a database and the server collation does not match to then alter all procedure where  you reference to temporary table could be a big headache 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 7:11 AM
  • Agreeing with all above that mixed collation is a big headache.

    Dynamic SQL is the best way to go:

    USE tempdb;
    SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
    INTO Product
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID < 700
    UNION ALL
    SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID >= 700
    ORDER BY New_ID
    
    DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE Latin1_General_100_BIN'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    /*
    ProductID	ProductName	ListPrice	Color
    1	Adjustable Race	0.00	NULL
    3	BB Ball Bearing	0.00	NULL
    2	Bearing Ball	0.00	NULL
    316	Blade	0.00	NULL
    324	Chain Stays	0.00	NULL ....
    */
    
    SET @SQL='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    GO
    /* ProductID	ProductName	ListPrice	Color
    1	Adjustable Race	0.00	NULL
    879	all-purpose bike stand	159.00	NULL
    712	awc logo cap	8.99	Multi
    3	BB Ball Bearing	0.00	NULL
    2	Bearing Ball	0.00	NULL
    877	bike wash - dissolver	7.95	NULL
    316	Blade	0.00	NULL .....
    */
    DROP TABLE tempdb.dbo.Product
    
    
    

    Dynamic SQL link: http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

    Collation link: http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

     

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 7:59 AM
  • Hi Kalman

    You saw my post to the puzzel:-)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 8:59 AM
  • Yes. Thanks. Thread:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f26a587d-ae3d-4bc1-84ff-0669aa7a3c8e

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 12:30 PM
  • Not really if you have took the precaution of using "COLLATE DATABASE_DEFAULT" in the creation scripts of your temporary tables.  The default collation of the current database will be used instead of the default collation of the tempdb; so there shouldn't be any problem even after migrating to a different server or database with a different default collation.

    Louis Davidson has posted a full example on his old blog:

    http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1368.entry


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Uri Dimant MVP> wrote in message news:e36b0b20-8ed0-4f94-bcaf-0c08a214f090@communitybridge.codeplex.com...

    Sylvain

    If you migrate a database and the server collation does not match to then alter all procedure where you reference to temporary table could be a big headache


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, July 25, 2010 6:15 PM
  • Sylvain

    Yes, I do have but not all of our developers arround the world:-)))))))))))))))

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 26, 2010 5:17 AM