OracleToSQL: Collation


Description

Oracle bases its language support on the values of parameters that begin with NLS. These parameters specify, for example, how to display currency or how the name of a day is spelled. Oracle Database provides the following types of collation:

  • Binary
  • Monolingual
  • Multilingual
  • Unicode Collation Algorithm (UCA)

How to find feature enablement?

SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = ‘SORT’

Recommendation

Feature Description

  1. SQL collations are provided for backward compatibility with earlier versions of SQL Server. Windows collations provide consistent string comparisons for both Unicode and for non-Unicode text in SQL Server that are also consistent with string comparisons in the Windows operating system. For all these reasons, Windows collations are preferred unless there are backward compatibility issues or specific performance issues that require a SQL collation.
  2. If you are considering a SQL collation based only on the performance characteristics of a SQL collation, realize that the performance of most applications does not benefit significantly from a change in collation. Make sure that you have isolated queries that show a benefit from a SQL collation. As soon as you identify the affected queries, consider the following alternatives to a change in collation. Both alternatives provide a performance benefit that is greater than what you will see if you change the instance collation to a SQL collation:
    1. If the overhead for the Windows collations is traced to Transact-SQL routines that perform explicit string manipulation or parsing, and if you are using non-Unicode data types, you may want to specify a SQL collation or a binary Windows collation for the operation that is frequently executed and that is most expensive.
    2. If the overhead for the Windows collations is traced to more mundane queries that do not use complex string manipulation functions, improved index or query designs might provide improvements that dwarf those you would see by changing to a SQL collation.

Feature Comparison

SQL Server supports all types of collations in Oracle. Below link provides information on the types of collations supported in SQL Server.

https://technet.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

Migration Approach

SSMA does not support migrating collation. SQL server needs to be configured with proper collation depends on the existing system designs.

Limitations and Restrictions

  • Windows Unicode-only collations can only be used with the COLLATE clause to apply collations to the nchar, nvarchar, and ntext data types on column level and expression-level data. They cannot be used with the COLLATE clause to change the collation of a database or server instance.
  • If the specified collation or the collation used by the referenced object uses a code page that is not supported by Windows, the Database Engine displays an error

Recommendations

  • When you change the database collation, you change the following:
    • Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.
    • All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation.
    • The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation.
  • You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
    SQL Server supports setting collations at the following levels:
    • Server
    • Database
    • Column
    • Expression
  • To set or change the database collation
    1. In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases.
    2. If you are creating a new database, right-click Databases and then click New Database. If you do not want the default collation, click the Options page, and select a collation from the Collation drop-down list.
    Alternatively, if the database already exists, right-click the database that you want and click Properties. Click the Options page, and select a collation from the Collation drop-down list.
    3. After you are finished, click OK

References


Comments (0)

Skip to main content