DbSchema Database Designer

DbSchema | SQL Server - How to Perform Transactions?

SQL Server: How to Perform Transactions in sqlcmd and DbSchema

SQL Server alt >

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.

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

    BEGIN TRANSACTION;
    
  2. 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?

  3. 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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.


Query Builder alt >

Query Builder

Create SQL Queries using the mouse.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.