SQL Server

Best Practice while performing DML operations (insert, update, delete) in SQL Server

SQL Developers get a lot of Adhoc tasks specially from the front end development team to insert, update and delete records (DML Operations) into the database. Although these commands are simple to write and execute but if a developer executes a wrong command then it may create unnecessary trouble to the developer.

In this post I am writing the good practice to follow while executing insert, update and delete operations specially when you are doing changes in test or developer environment .

In the figure below there is a table EMPLOYEEDETAILS and assume a developer got an adhoc task to modify DOB for EMP_NO=100 to ‘1965-12-12’ and modify the name ‘Sunitaa’ to ‘Sunita’.

 

Select-data-from-sql-server-adhoc-command

 

Now developer writes the ADHOC script as follow

UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100

UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’

And after that when the developer checked the table to see the changes,all the values of the NAME column are updated to ‘Sunita’ as shown in the following figure because in the second command written above, the developer forgot to put the filter condition.

 

Select-data-from-sql-server

 

The better way of writing the command would be to write BEGIN TRAN before executing these DML statements as shown below.

BEGIN TRAN

UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100

UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’

This has created an open transaction which is neither committed nor rollbacked. After that the developer will check the table to see the changes. As all the values of the NAME column are updated to ‘Sunita’, SQL developer can execute rollback command.

So now he just needs to type the following command

ROLLBACK

This command will rollback all the changes done by the transaction.

Now developer can modify the command as follow by writing the filter condition in the second statement as shown below.

BEGIN TRAN

UPDATE [EMPLOYEEDETAILS] SET DOB=‘1965-12-12’ WHERE EMP_NO=100

UPDATE [EMPLOYEEDETAILS] SET NAME=‘SUNITA’ WHERE EMP_NO=105

And after executing the statements above, developer can check the changes by using SELECT command as shown below

 

Select-data-from-sql-server-3

 

Now the output is perfect and developer can commit the open transaction by writing the following code.

COMMIT

So the moral of the story is to it is a good practise to write BEGIN Tran in the beginning of DML statement before executing it. Check the changes if the changes are perfect execute COMMIT otherwise execute ROLLBACK.

Important Note : This practice is good when you are doing changes in test or developer environment. But for production it should not be used or used very cautiously because the resources are locked by the transactions until you run commit or rollback which may impact other transactions running on the production server.

If this is required in production, one could simply put the select statements to verify the changes on the same script with a rollback at the end. Then once the script is run and changes verified, just change the rollback to a commit and re-execute.

Leave a Reply