DbSchema | SQL Server - How to Perform Transactions?
SQL Server: How to Perform Transactions in sqlcmd and DbSchema
Table of Contents
- Introduction
- Prerequisites
- Understanding Transactions
- Using Transactions: Advantages and Limitations
- Commit, Savepoints, Rollback: Explained
- Permissions and Restrictions
- Using Transactions in sqlcmd
- Using Transactions in DbSchema
- Conclusion
- 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 machineDbSchema
installed on your machine- Basic understanding of
sqlcmd
andDbSchema
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 orfails
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:
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.
Begin Transaction: Start the transaction with the
BEGIN TRANSACTION
statement:BEGIN TRANSACTION;
Perform operations: Perform your operations, such as
insert
,update
, ordelete
. 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?
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:
Connect to the database:
Start
DbSchema andconnect
to your SQL Server database.Open SQL Editor: Click on the
SQL Editor
icon in the left pane.Begin Transaction: Start the transaction with the
BEGIN TRANSACTION
statement.Perform operations: Perform your operations, such as
insert
,update
, ordelete
.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.