DML STATEMENTS ON OLTP TRANSACTION

Question

DML STATEMENTS ON OLTP TRANSACTION

kiran 4243

Mr or Mrs. 500

Points: 553
More actions
February 22, 2022 at 4:00 am

#3986668
Hi
Good morning.
What is the better approach when applying DML statements on the OLTP transactions?
1. when updating the rows in a table delete the rows and insert new rows or update the rows with a new column value.
Please give your valuable advice.
Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

kiran 4243 wrote:

Hi
Good morning.
What is the better approach when applying DML statements on the OLTP transactions?
when updating the rows in a table delete the rows and insert new rows or update the rows with a new column value.
Please give your valuable advice.
Thank you.

It depends. What approach will work best for you?

If you have relationships between tables, delete may not be possible without considering the relationships first.

If you are attempting to create a "standard" for your organization, these are not something that would become a rule. If you are trying to solve a specific problem, can you provide some more details?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

There are always exceptions to everything, but speaking in general terms, you'll be better off letting SQL Server perform an update through the UPDATE process rather than deleting existing data and then inserting new data. This is absolutely true from a performance standpoint (although, there are exceptions, always, but they're rare, one might even say, exceptional).

It's even more true from a data management standpoint. Think about it.

I want to update a person's first name. Now, that's not a unique value in my Person table (well, it shouldn't be). So, I could update it in place, no additional work needed. Or, I can delete that person's data in order to insert a new value... ah, but the PersonPhone table, it has an enforced referential key to the Person table, so I'll need to either drop the key (yikes) or delete all data in the PersonPhone table and reinsert it. Same for the PersonPayroll, PersonEmail, PersonWhatever tables. So, even before I can begin the delete process on all this, I have to run selects on them to get all the data out. Delete it. Delete the Person table row. Insert the person table row. Reinsert all the other rows, with the new PersonID I just created (assuming I did that, who knows).

Which do you think is easier to code, less error prone and faster? All that stuff, or an UPDATE statement?

Now, make this a heap table without any relationships, yeah, maybe the DELETE/INSERT is faster, sometimes. Probably not most of the time though.

Test it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning