locked
Migration error from Oracle to SQL server 2014 using SSMA RRS feed

  • Question

  • Hello,

    I am using SSMA for the first time. Can anyone help me in solving this error. Functional index can't be converted. O2SS0269 error. Any help will be much appreciated.

    Monday, June 13, 2016 7:26 PM

Answers

  • SQL Server doesn't have function-based indexes.  As an alternative you can consider an index on a computed column or an indexed view.

    And a Function-Based Index is just physical structure that optimizes a particular predicate.  So leaving it out won't affect the functioning of the application.  You just need to ensure that you can provide reasonable performance for the queries that use the Function-Based Index in Oracle.

    If you share the function based index in Oracle you may get more specific advice.

    For instance a common case for Function-Based Indexes in Oracle is to create an index on the UPPER() of a column.  Here the function-based index is a workaround for the fact that Oracle doesn't support case-insensitive string comparisons.  SQL Server does, so the correct migration in this case is to use the appropriate column collation in SQL Server, and remove the UPPER() from the queries.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, June 13, 2016 7:33 PM
  • In that case you should just omit the index and use a case-insensitive collation for the NAME column on RM_ROOM, and not use UPPER(name) at all.

    You can also add a calculated column, but that would be more like

    Create table RM_ROOM
    ( 
    id int,
    --other columns...
    name varchar(90),
    upper_name AS UPPER(name) 
    );
    
    create index RM_ROOM_NAME_UNQ1 on RM_ROOM(upper_name);
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 14, 2016 2:44 AM

All replies

  • SQL Server doesn't have function-based indexes.  As an alternative you can consider an index on a computed column or an indexed view.

    And a Function-Based Index is just physical structure that optimizes a particular predicate.  So leaving it out won't affect the functioning of the application.  You just need to ensure that you can provide reasonable performance for the queries that use the Function-Based Index in Oracle.

    If you share the function based index in Oracle you may get more specific advice.

    For instance a common case for Function-Based Indexes in Oracle is to create an index on the UPPER() of a column.  Here the function-based index is a workaround for the fact that Oracle doesn't support case-insensitive string comparisons.  SQL Server does, so the correct migration in this case is to use the appropriate column collation in SQL Server, and remove the UPPER() from the queries.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, June 13, 2016 7:33 PM
  • Hi David, Thank for helping me out. This is the error I am getting. Yeah I came to know that SQL server does not support the function-based indexes.

    Oracle query was this

    CREATE UNIQUE INDEX RM.RM_ROOM_NAME_UNQ1 ON RM.RM_ROOM

    (

          UPPER("NAME")

    );

    I am trying to change the query to this

    Create table RM.RM_ROOM

    ( upper_name AS UPPER(name) );

    create index RM_ROOM_NAME_UNQ1 on RM_ROOM((upper_name));

    Does this work? Do I need to change anything else ? Thank You.



    Tuesday, June 14, 2016 1:18 AM
  • In that case you should just omit the index and use a case-insensitive collation for the NAME column on RM_ROOM, and not use UPPER(name) at all.

    You can also add a calculated column, but that would be more like

    Create table RM_ROOM
    ( 
    id int,
    --other columns...
    name varchar(90),
    upper_name AS UPPER(name) 
    );
    
    create index RM_ROOM_NAME_UNQ1 on RM_ROOM(upper_name);
    
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 14, 2016 2:44 AM
  • Thank You David . solved the error.

    Samson Yerraguntla


    Tuesday, June 14, 2016 1:34 PM