Defining a Deletion Query

In many cases, you may want to consider also writing a deletion, or pruning, query to keep the index and the transactional tables in sync with regard to rows that have been deleted from the transactional tables.

For example, assume a row exists in the transactional table for Big Company, and that row has been included in the search query criteria and indexed by the Search Framework crawler. Now, suppose that row gets deleted from the transactional table because Big Company went out of business and no longer exists. Because the row no longer exists in the transactional table, you do not want the previously indexed data to appear in a user’s search results. In a search definition, you associate a deletion query for an index on the Advanced tab in the Define Query to Delete SBO section. There you specify the query name and the Drilling URL field for the deletion query.

The Delete query generates a feed containing only the records that need to be deleted from the index or those documents/records that are marked for deletion from the index. One method of capturing the deleted rows is to create an audit record on the transactional table in which a record of all deleted rows gets inserted into the audit table. The delete query would then capture the rows in the audit table and remove from the index the document entries matching those rows. The delete query also needs a datetime prompt which returns rows deleted after that datetime value.