Improving Modifications :: SQL Server 2012

You work in the database group of a company that has recently upgraded the database from SQL Server 2000 to SQL Server 2005 and then to SQL Server 2012. The code is still SQL Server 2000 --compatible. There are issues with modifications submitted by the application to the database.

The application uses a procedure that accepts as inputs attributes of a row. The procedure then uses logic that checks whether the key already exists in the target table, and if it does, updates the target row. If it doesn’t, the procedure inserts a new row into the target. The problem is that occasionally the procedure fails due to a primary key violation. This happens when the existence check doesn't find a row, but between that check and the insertion, someone else managed to insert a new row with the same key.

The application has a monthly process that archives data that it needs to purge. Currently, the application first copies data that needs to be deleted to the archive table in one statement and then deletes those rows in another statement. Both statements use a filter that is based on a date column called dt. You need to filter the rows where dt is earlier than a certain date. The problem is that sometimes rows representing late arrivals are inserted into the table between the copying and the deletion of rows, and the deletion process ends up deleting rows that were not archived.

You are tasked with finding solutions to the existing problems.

1. Can you suggest a solution to the existing problem with the procedure that updates the row when the source key exists in the target and inserts a row if it doesn't?
   
   
2. Can you suggest a solution to the problem with the archiving process that prevents deleting rows that were not archived?

Answers

1. A recommended solution is to use the MERGE statement. Define the source for the MERGE statement as a derived table based on the VALUES clause, with a row made of the input parameters for the procedure. Specify the table hint HOLDLOCK or SERIALIZABLE against the target to prevent conflicts such as the ones that currently exist in the system. Then use the WHEN MATCHED clause to issue an UPDATE action if the target row exists, and the WHEN NOT MATCHED clause to issue an INSERT action if the target row doesn't exist.
   
   
2. One option is to work with the SERIALIZABLE isolation level, handling both the statement that copies the rows to the archive environment and the statement that deletes the rows in one transaction. But a simpler solution is to do both tasks in one statement— a DELETE with an OUTPUT INTO clause. This ensures that only rows that are copied to the archive table are deleted. And if for whatever reason the copying of the rows to the archive table fails, the delete operation also fails, because both activities are part of the same transaction.