none
Selecting right collation - equivalent of Oracle default character set

    Question

  • I need a help. We are migrating an Oracle DB to SQL Server 2008. In oracle the Objects names are case insensitive but the data is case sensitive. Could you please tell me what  collation i should select? As per my understanding in SQL Server there is no equivalent setting. If it is case sensitive, it is for both object name and data. Is that correct? in that case, SQL_Latin1_General_CP1_CS_AS should be fine right ? 

    thanks

     

    Madhu Nair 

     

     


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Saturday, June 12, 2010 1:43 AM
    Moderator

Answers

  • Hi,

    SQL_Latin1_General_Cp1_CS_AS is a case-sensitive collation and SQL_Latin1_General_CP1_CI_AS is a case-insensitive collation. In SQL Server, collations can be specified at the server, database, column, expression, and identifier levels. If  the objects names need to be case-insensitive, you could specify the server collation as SQL_Latin1_General_CP1_CI_AS. Since the objects are stored in the system databases and the server collation acts as the default collation for all system databases. To use case-sensitive collation for the data in a database, you could specify SQL_Latin1_General_Cp1_CS_AS for the database collation.

    Reference:
    Setting and Changing Collations
    http://technet.microsoft.com/en-us/library/ms177439.aspx


    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, June 15, 2010 9:29 AM
    Moderator

All replies

  • Hello,

    I have used SQL_Latin1_General_CP1_CI_AS and works fine. Don’t use Unicode data types on the SQL Server side (use varchar instead of nvarchar for example).

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Sunday, June 13, 2010 7:14 AM
    Moderator
  • Hi,

    SQL_Latin1_General_Cp1_CS_AS is a case-sensitive collation and SQL_Latin1_General_CP1_CI_AS is a case-insensitive collation. In SQL Server, collations can be specified at the server, database, column, expression, and identifier levels. If  the objects names need to be case-insensitive, you could specify the server collation as SQL_Latin1_General_CP1_CI_AS. Since the objects are stored in the system databases and the server collation acts as the default collation for all system databases. To use case-sensitive collation for the data in a database, you could specify SQL_Latin1_General_Cp1_CS_AS for the database collation.

    Reference:
    Setting and Changing Collations
    http://technet.microsoft.com/en-us/library/ms177439.aspx


    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, June 15, 2010 9:29 AM
    Moderator