DbSchema | SQL Server - How to Perform Transactions?

Publish on DbSchema Blog >>>

SQL Server: How to Perform Transactions in sqlcmd and DbSchema

Table of Contents

1. Introduction
2. Prerequisites
3. Understanding Transactions
4. Using Transactions: Advantages and Limitations
5. Commit, Savepoints, Rollback: Explained
6. Permissions and Restrictions
7. Using Transactions in sqlcmd
8. Using Transactions in DbSchema
9. Conclusion
10. References

Introduction

In this tutorial, we’ll be delving into the world of SQL Server, exploring the key concepts and functionalities of transactions and how they can be implemented using sqlcmd and DbSchema.

SQL Server, a relational database management system (RDBMS) developed by Microsoft, is widely used for managing and storing data. It supports a wide range of transaction control commands such as COMMIT, ROLLBACK, and SAVEPOINT which help maintain the consistency and integrity of the data.

Prerequisites

• Basic understanding of SQL Server
• Familiarity with SQL commands
• SQL Server installed on your machine
• DbSchema installed on your machine
• Basic understanding of sqlcmd and DbSchema

For installation and establishing connection you can read our article SQL Server-How to create a database?

Understanding Transactions

A transaction, in SQL Server, is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, known as the ACID properties:

• Atomicity: The transaction completes successfully or fails as a whole.
• Consistency: The transaction brings the database from one consistent state to another.
• Isolation: The results of a transaction are invisible to other transactions until the transaction is complete.
• Durability: Once a transaction is committed, its effects are permanent in the database.

Using Transactions: Advantages and Limitations

Advantages

• Consistency: Transactions ensure that the database remains consistent even after an unexpected system failure.
• Isolation: Each transaction is executed in isolation from other transactions, providing an illusion that each transaction is the only operation in the system.
• Atomicity: Ensures that all operations within a transaction are completed successfully; if not, the transaction is aborted.

Limitations

• Performance: Transactions that hold locks for a long duration can decrease the performance of the database system.
• Complexity: Implementing transactions in a database application increases the complexity of the application.
• Resource-intensive: Long-running transactions can be resource-intensive and could potentially lead to system slowdowns.

Commit, Savepoint and Rollback: Explained

COMMAND	DESCRIPTION
COMMIT	The COMMIT command is used to permanently save any transaction into the database. Once this operation is performed, you can’t roll back the changes.
ROLLBACK	The ROLLBACK command is used to undo transactions that haven’t yet been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
SAVEPOINT	A SAVEPOINT is a point in a transaction that you can roll back to without rolling back the entire transaction. The SAVEPOINT command is used in conjunction with the ROLLBACK TO <savepoint> command to undo portions of a transaction instead of the whole transaction.

Permissions and Restrictions

To use transactions in SQL Server, the user needs db_datareader and db_datawriter permissions to read and write data respectively. The db_ddladmin permission is also needed to create and manipulate database objects. Always remember to use transactions judiciously, as they can lock resources and affect the performance of your database.

Using Transactions in sqlcmd

Let’s say we have a table Orders in our database.

OrderID	CustomerID	OrderDate
1	101	2023-01-01
2	102	2023-01-03
3	103	2023-01-05

Follow the steps below to use transactions in sqlcmd:

1. Start sqlcmd: Open the command prompt and enter the following command to start sqlcmd:

   sqlcmd -S <server_name> -U <username> -P <password>
   

Replace <server_name>, <username>, and <password> with your specific server details and login credentials.

2. Begin Transaction: Start the transaction with the BEGIN TRANSACTION statement:

   BEGIN TRANSACTION;
   

3. Perform operations: Perform your operations, such as insert, update, or delete. For example:

   INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (4, 104, GETDATE());
   

   To know more about creating a table you can read our article SQL Server-How to create a table?

4. Commit or Rollback: If everything is correct, commit the transaction. If there’s an error, rollback the transaction:

   COMMIT;
   

   or

   ROLLBACK;
   

Results from the Query:

Assume that you have the following Orders table in your database:

OrderID	_CustomerID_	OrderDate
1	101	2023-01-01
2	102	2023-01-03
3	103	2023-01-05

1. Using COMMIT:

You start a transaction and insert a new order:

BEGIN TRANSACTION;
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (4, 104, '2023-01-07');
COMMIT;

After this transaction, your Orders table would look like this:

_OrderID_	_CustomerID_	OrderDate
1	101	2023-01-01
2	102	2023-01-03
3	103	2023-01-05
4	104	2023-01-07

2. Using ROLLBACK:

You start another transaction and try to insert a new order:

BEGIN TRANSACTION;
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (5, 105, '2023-01-09');
ROLLBACK;

After this transaction, your Orders table would remain the same as before, because the transaction was rolled back:

OrderID	CustomerID	OrderDate
1	101	2023-01-01
2	102	2023-01-03
3	103	2023-01-05
4	104	2023-01-07

3. Using SAVEPOINT:

You start a transaction, insert a new order, create a savepoint, and insert another order:

BEGIN TRANSACTION;
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (5, 105, '2023-01-09');
SAVEPOINT SP1;
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (6, 106, '2023-01-11');
ROLLBACK TO SP1;
COMMIT;

After this transaction, your Orders table would look like this:

OrderID	CustomerID	_OrderDate_
1	101	2023-01-01
2	102	2023-01-03
3	103	2023-01-05
4	104	2023-01-07
5	105	2023-01-09

The ROLLBACK TO SP1 statement undid the last insert operation after the savepoint, so the order with OrderID 6 is not in the table.

Using Transactions in DbSchema

DbSchema is a visual database designer & manager with interactive diagrams, which also supports the execution of SQL scripts.

Follow the steps below to use transactions in DbSchema:

1. Connect to the database: Start DbSchema and connect to your SQL Server database.

2. Open SQL Editor: Click on the SQL Editor icon in the left pane.

3. Begin Transaction: Start the transaction with the BEGIN TRANSACTION statement.

4. Perform operations: Perform your operations, such as insert, update, or delete.

5. Commit or Rollback: If everything is correct, commit the transaction. If there’s an error, rollback the transaction.

Conclusion

Transactions are a crucial aspect of database management systems, ensuring data consistency and atomicity of operations. They can be effectively used in SQL Server via different interfaces like sqlcmd and DbSchema. It’s essential to understand their advantages and limitations to utilize them effectively.

References

1. Microsoft SQL Server Documentation: Link
2. DbSchema Documentation: Link
3. Understanding SQL Transactions: Link