• ChrisM@Work - Wednesday, March 7, 2018 5:31 AM

    sks_989 - Tuesday, March 6, 2018 8:33 PM

    I have 2 table i have created index on on both table

    CREATE NONCLUSTERED INDEX [IX_NC_source_t_Test_PCode] ON [Common].[source_t_Test]
    (
        [Code] ASC,
        [P_Code] ASC,
        [RP Code] ASC,
        [ES] ASC
    )
    INCLUDE (     [ItemID],
        [ATime],
        [PrimaryICode],
        [P First Name],
        [P Last Name],
        [P Number],
        [R P First Name],
        [R P Last Name],
        [R Number],
        [CreatedDateTime],
        [CreatedBy],
        [ModifiedDateTime],
        [ModifiedBy],
        [RS],
        [UIndicator]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO
    CREATE NONCLUSTERED INDEX [IX_NC_source_t_Temp_Test_PCode] ON [Common].[source_t_Temp_Test]
    (
        [Code] ASC,
        [P_Code] ASC,
        [RP Code] ASC,
        [SS] ASC
    )
    INCLUDE (        [ATime],
        [PrimaryICode],
        [P First Name],
        [P Last Name],
        [P Number],
        [R P First Name],
        [R P Last Name],
        [R Number],
        [CreatedDateTime],
        [CreatedBy],
        [FName],
        [PVersion]
        [FReload] ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO
               DELETE final
                FROM Common.source_t_Test final
                inner join Common.source_t_Test_temp temp on final.[Code]=temp.[Code]
                and final.[P_Code]=temp.[P_Code] and final.[RP Code]=temp.[RP Code]
                and final.ES=temp.[SS]

    There are 30 mil records on both table. when i delete it takes about 4 hours.
    I was thinking to create 1 more field and update that field as delete indicator and then delete record from final table.
    Before i do this route i want your suggestion.

    What are these indexes used for?
    What key combination guarantees unique rows?
    What clustered indexes do you have on these tables?

    We have use index to process fast.

    This is uniqueness - [Atime] [Code] [P_Code]  [RP Code] [ES] 
    Have cluster index on source_t_Test on Itemid there is no cluster index on source_t_Test_Temp