Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
Any statement in SQL Server is a transaction in it's own right.
That is, it is atomic: everything succeeds or everything fails
An explicit transaction would be used to group multiple single atomic statements into one big atomic transaction.
This is beauty of MERGE: no need for an explicit transaction and 3 separate statements.
Delete and Insert Inside one Transaction SQL
Within a transaction all write locks (all locks acquired for modifications) must obey the strict two phase locking rule. One of the consequences is that a write (X) lock acquired in a transaction cannot be released until the transaction commits. So yes, the DELETE and INSERT will execute sequentially and all locks acquired during the DELETE will be retained while executing the INSERT.
Keep in mind that deleting 500k rows in a transaction will escalate the locks to one table lock, see Lock Escalation.
Deleting 500k rows and inserting 500k rows in a single transaction, while maybe correct, is a bad idea. You should avoid such large units of works, long transaction, if possible. Long transactions pin the log in place, create blocking and contention, increase recovery and DB startup time, increase SQL Server resource consumption (locks require memory).
You should consider doing the operation in small batches (perhaps 10000 rows at time), use MERGE instead of DELETE/INSERT (if possible) and, last but not least, consider a partitioned sliding window
implementation, see How to Implement an Automatic Sliding Window in a Partitioned Table.
Why is SQL Server upsert with MERGE inserting multiple records?
MERGE, results in multiple SQL statements, that means it can result in possible concurrency conflicts. You should implement locking:
MERGE dbo.TableName WITH (HOLDLOCK) AS target
USING ... AS source ...;
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
When to use Transactions in SQL Server
You use transactions when the set of database operations you are making needs to be atomic.
That is - they all need to succeed or fail. Nothing in between.
Transactions are to be used to ensure that the database is always in a consistent state.
In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.
Try/Catch
blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.
Are Transactions Always Atomic?
The 2 points are unrelated
Sequential
If you insert values 1 to 1000, it will be sequential with an WHERE and ORDER BY to limit you to these 1000 rows in some column. Unless there are duplicates, so you'd need a unique constraint
If you rely on an IDENTITY, it isn't guaranteed: Do Inserted Records Always Receive Contiguous Identity Values.
Atomicity
All transactions are atomic:
- Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
- SQL Server and connection loss in the middle of a transaction
- Does it delete partially if execute a delete statement without transaction?
Are Transactions Always Atomic?
The 2 points are unrelated
Sequential
If you insert values 1 to 1000, it will be sequential with an WHERE and ORDER BY to limit you to these 1000 rows in some column. Unless there are duplicates, so you'd need a unique constraint
If you rely on an IDENTITY, it isn't guaranteed: Do Inserted Records Always Receive Contiguous Identity Values.
Atomicity
All transactions are atomic:
- Is neccessary to encapsulate a single merge statement (with insert, delete and update) in a transaction?
- SQL Server and connection loss in the middle of a transaction
- Does it delete partially if execute a delete statement without transaction?
Related Topics
Renaming a Column in Ms SQL Server 2005
How to Change the Name of the Athena Results Stored in S3
SQL Listing All Column Names Alphabetically
Default Value of Guid in for a Column in MySQL
Insert Data from One Server to Another
There Are No Primary or Candidate Keys in the Referenced Table
Using a Select Statement Within a Where Clause
Range Wildcard Pattern Matching Behaviour with Case-Sensitive Collations
How to Have a Foreign Key Pointing to Two Primary Keys
SQL Server 2005: How to Subtract 6 Month
Make H2 Treat Quoted Name and Unquoted Name as the Same
SQL Recursion Without Recursion
How to Restore SQL Server 2008 Backup in SQL Server 2005
Use Google Bigquery to Build Histogram Graph
Oracle Insert Failure:Not a Valid Month
Using Variables for Asc and Desc in Order By
How to Copy Structure and Contents of a Table, But with Separate Sequence
Testing Postgresql Functions That Consume and Return Refcursor