Home Forums Article Discussions Article Discussions by Author Discuss content posted by Nakul Vachhrajani Deleting large number of rows from a table & a heap in a VLDB RE: Deleting large number of rows from a table & a heap in a VLDB

  • March 24, 2011 at 4:01 pm

    #1303277

    rob.lobbe-964963 (3/24/2011)

    Also you're using SQL2008+. read up on "Filtered Indexes"

    Create a filtered index on your main table that contains your PK, and filters on your delete criteria. This index will be used to determine your deletion candidates, and the overhead should be minimal as the delete candidates should only be a small subset of the bulk of your data.

    Good point! Sometimes the filtered index is a better approach than using a table, temporary or otherwise, to store PK values for transaction that are to be deleted.

    BTW ... How many DBA's get "completed" applications from development teams that have absolutely no databases purging logic at all!?! I have no idea how they get away with this.