2

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?

My playground:

  • Table with billion rows and 100 columns
  • Unique clustered index on column C1
  • Filtered index on column C2 covering 90% of rows defined as:

CREATE NONCLUSTERED INDEX IX_NC_F_Tab_C2 ON TAB(C2) WHERE C2 >= 0;

What I would like to achieve is the most optimal way to delete everything from that table which is not covered by the filtered index, like:

DELETE FROM Tab WHERE NOT C2 >= 0;

In this scenario my IX_NC_F_Tab_C2 index won't bring any improvement to the execution plan, therefore is there any way to force the estimator to somehow use it?

1
  • @BartoszX Sorry, my inquiry was not clear enough to produce a meaningful answer for me. If you can tolerate prep work and table modification (not trivial, not fast), the fastest way to rid yourself of the data in one swoop is to partition swap the data out (then truncate that data). This probably does not meet your requirements, however.
    – Graham
    Dec 3 '19 at 16:11
0

There are a couple of possible choices that would make use of the index.

  1. Create a temporary table holding the rows that are in the index, then use that temporary table in an EXCEPT clause on your delete statement. Something like:

    SELECT t.[Key]
    INTO #rows_to_keep
    FROM dbo.Tab t
    WHERE t.C2 >= 0;
    
    ;WITH src AS
    (
        SELECT t.[Key]
        FROM dbo.Tab t
        EXCEPT 
        SELECT rtk.[Key]
        FROM #rows_to_keep rtk
    )
    DELETE t
    FROM dbo.Tab t
    WHERE EXISTS (
        SELECT 1
        FROM src
        WHERE src.[Key] = t.[Key]
    );
    
  2. Use ALTER TABLE ... SWITCH ... to switch out a copy of the table with the rows you want to keep, assuming the number of rows you want to keep is significantly less than the number of rows you want to delete.

    This is a very simplified example of how to accomplish the SWITCH method.

    First we'll create the tables required, populate some sample data, and show the content:

    USE tempdb;
    GO
    
    CREATE TABLE dbo.Tab
    (
        [Key]   int NOT NULL
            IDENTITY(1,1)
            PRIMARY KEY
            CLUSTERED
        , C0    int NULL
        , INDEX c0_keep (C0) WHERE C0>0
    );
    
    INSERT INTO dbo.Tab (C0)
    VALUES (1)
        , (2)
        , (0)
        , (NULL);
    
    SELECT *
    FROM dbo.Tab;
    GO
    

    The results:

    Key C0
    1 1
    2 2
    3 0
    4 NULL

    Next, we'll do the actual switch process:

    CREATE TABLE dbo.Tab_Keep
    (
        [Key]   int NOT NULL
            IDENTITY(1,1)
            PRIMARY KEY
            CLUSTERED
        , C0    int NULL
        , INDEX c0_keep (C0) WHERE C0>0
    );
    GO
    
    SET XACT_ABORT ON;
    
    BEGIN TRANSACTION;
    BEGIN TRY
        ALTER TABLE dbo.Tab SWITCH TO dbo.Tab_keep;
    
        SET IDENTITY_INSERT dbo.Tab ON;
        INSERT INTO dbo.Tab ([Key], C0)
        SELECT *
        FROM dbo.Tab_Keep t
        WHERE t.C0 > 0;
        SET IDENTITY_INSERT dbo.Tab OFF;
        DBCC CHECKIDENT ('dbo.Tab', RESEED);
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    END CATCH
    GO
    

    And, finally, the results:

    SELECT *
    FROM dbo.Tab;
    
    Key C0
    1 1
    2 2

There are some gotchas with the SWITCH method; index names will change, referential integrity can be tricky, etc. Use a development or test copy of the database to determine the best method for your situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.