none
How can I configure SSMA to generate a filtered Unique index that ignores NULL? RRS feed

  • Question

  • How can I configure SSMA to automatically generate a filtered Unique index that ignores NULL?

    The Release Notes of Oracle to SSMA v8.9 says "Generate filtered unique indexes for NULL-able columns", but I cannot find a corresponding setting to enable this new feature.

    Friday, June 26, 2020 12:00 PM

All replies

  • Hi  lbreuss,

    Yes, that new feature was mentioned in What's New in SSMA for Oracle (OracleToSQL).

    I have no Oracle environment. Also limited document could be researched about this new feature.

    Please have a try to enable options like 'Keep nulls' mentioned in Project Settings (Migration) (OracleToSQL) and manually create the filtered index including 'WHERE column IS NOT NULL '.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 29, 2020 8:03 AM
  • Hi,

    This is "ON" by default, it is just a standard conversion now. Is there a particular Oracle DDL, where SSMA does not convert it properly to filtered index?

    Under the covers it actually checks if converted columns (on the target) are NULL-able and only then generates a filtered index with WHERE clause for such columns. This is because you can override the target schema after conversion in SSMA and we still want to produce correct SQL definitions.

    Thanks and regards,
    Alex.

    • Proposed as answer by Naomi N Monday, June 29, 2020 4:41 PM
    Monday, June 29, 2020 4:06 PM
  • I recreated a minimal testcase. SSMA v8.10.0 still does not generate a filtered unique index.

    The SSMA project is all default, no type mapping or other settings changed/configured.

    -- Oracle test table
    CREATE SEQUENCE A_TEST_SEQ;
    
    CREATE TABLE A_TEST (
      ID NUMBER(10) DEFAULT A_TEST_SEQ.nextval PRIMARY KEY,
      UUID VARCHAR2(50),
      DESCRIPTION VARCHAR2(255),
      CONSTRAINT UQ_UUID UNIQUE (UUID)
    );
    
    INSERT INTO "DCM_TD_TEST"."A_TEST" (DESCRIPTION) VALUES ('1st null');
    INSERT INTO "DCM_TD_TEST"."A_TEST" (DESCRIPTION) VALUES ('2nd null');
    INSERT INTO "DCM_TD_TEST"."A_TEST" (UUID, DESCRIPTION) VALUES ('deadbeef', 'the deadbeef');
    commit;

    SSMA Convert Schema generates the following code:

    -- SSMA generated code
    USE [dcm]
    GO
    
    /****** Object:  Table [dcm_td_test].[A_TEST]    Script Date: 6/30/2020 12:29:56 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dcm_td_test].[A_TEST](
    	[ID] [numeric](10, 0) NOT NULL,
    	[UUID] [varchar](50) NULL,
    	[DESCRIPTION] [varchar](255) NULL,
     CONSTRAINT [SYS_C0011746] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
     CONSTRAINT [UQ_UUID] UNIQUE NONCLUSTERED 
    (
    	[UUID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dcm_td_test].[A_TEST] ADD  DEFAULT (NEXT VALUE FOR [DCM_TD_TEST].[A_TEST_SEQ]) FOR [ID]
    GO
    
    -- ... plus some EXEC sys.sp_addextendedproperty

    I cannot insert a second null into UUID.

    INSERT INTO "DCM_TD_TEST"."A_TEST" (UUID, DESCRIPTION) VALUES (null, '1st null');
    -- the next will fail with SQL Sever: INSERT INTO "DCM_TD_TEST"."A_TEST" (UUID, DESCRIPTION) VALUES (null, '2nd null'); INSERT INTO "DCM_TD_TEST"."A_TEST" (UUID, DESCRIPTION) VALUES ('deadbeef', 'the deadbeef');

    What am I missing?

    Thanks and regards,
    Leo



    • Edited by lbreuss Tuesday, June 30, 2020 7:48 AM
    Tuesday, June 30, 2020 7:47 AM
  • Hello,

    What you are missing there is the actual index. If you add an unique index to your table, then it should be converted to a filtered index:

    CREATE UNIQUE INDEX "DCM_TD_TEST"."IX_UQ_UUID" ON "DCM_TD_TEST"."A_TEST"(UUID);

    Unique constraints are not handled yet. Sorry if this was unclear from the release notes.

    There is no such thing as "filtered constraint", so we will basically have to convert every unique constraint to a unique filtered index, if there are NULL-able columns. This is a bigger change and might cause a confusion for some users, although to guarantee functional correctness of the conversion it seems to be the only way. I will put this on the backlog.

    Thanks,
    Alex.

    • Proposed as answer by Naomi N Tuesday, June 30, 2020 6:17 PM
    Tuesday, June 30, 2020 6:04 PM
  • Hi lbreuss,

    Please follow Alex's suggestion and have another try. Thanks.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 3:05 AM