A transaction is the logical work unit that performs a single activity or multiple activities in a database. Transactions may consist of a single read, write, delete, or update operations or a combination of these. Suppose that, when we want to withdraw money from the ATM, the ATM application will achieve this operation in three steps. As a first step, the application will check the balance of the account, and then it will deduct the money from the source account. Along with these two processes, it will keep the log of this money withdrawing activity. The following image basically illustrates the working principle of the transactions in the relational database systems.
The main idea of transactions is that when each of the statements returns an error, the entire modifications rollback to provide data integrity. On the other hand, if all statements are completed successfully the data modifications will become permanent on the database. As a result, if we experience any power outage or other problems during the withdrawal of money from an ATM, transactions guarantee our balance consistency. It would be the best method to perform all these steps through a transaction because the four main properties of the transactions enable all operations more accurate and consistent. All these properties are known as the ACID (atomicity, consistency, isolation, durability) in the relational database systems with the first letter of their names.
Atomicity: The entire of the operations that are included by the transaction performed successfully. Otherwise, all operations are canceled at the point of the failure and all the previous operations are rolled back
Consistency: This property ensures that all the data will be consistent after a transaction is completed according to the defined rules, constraints, cascades, and triggers
Isolation: All transactions are isolated from other transactions
Durable: The modification of the commited transactions becomes persist in the database
Pre-Requirements
In this article, we will create a sample table through the following query and will populate some sample data.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATETABLEPerson (
PersonIDintPRIMARYKEYIDENTITY(1,1),
LastNamevarchar(255),
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
AgeINT
)
GO
INSERTINTOPersonVALUES('Hayes','Corey','123 Wern Ddu Lane','LUSTLEIGH',23)
SQL Server can operate 3 different transactions modes and these are:
Autocommit Transaction mode is the default transaction for the SQL Server. In this mode, each T-SQL statement is evaluated as a transaction and they are committed or rolled back according to their results. The successful statements are committed and the failed statements are rolled back immediately
Implicit transaction modeenables to SQL Server to start an implicit transaction for every DML statement but we need to use the commit or rolled back commands explicitly at the end of the statements
Explicit transaction mode provides to define a transaction exactly with the starting and ending points of the transaction
How to define an Implicit Transaction in SQL Server
In order to define an implicit transaction, we need to enable the IMPLICIT_TRANSACTIONS option. The following query illustrates an example of an implicit transaction.
Tip: @@TRANCOUNT function returns the number of BEGIN TRANSACTION statements in the current session and we can use this function to count the open local transaction numbers in the examples
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SETIMPLICIT_TRANSACTIONSON
UPDATE
Person
SET
Lastname='Sawyer',
Firstname='Tom'
WHERE
PersonID=2
SELECT
IIF(@@OPTIONS&2=2,
'Implicit Transaction Mode ON',
'Implicit Transaction Mode OFF'
)AS'Transaction Mode'
SELECT
@@TRANCOUNTASOpenTransactions
COMMITTRAN
SELECT
@@TRANCOUNTASOpenTransactions
The COMMIT TRANSACTION statement applies the data changes to the database and the changed data will become permanent.
How to define an Explicit Transaction in SQL Server
In order to define an explicit transaction, we start to use the BEGIN TRANSACTION command because this statement identifies the starting point of the explicit transaction. It has the following syntax:
transaction_name option is used to assign a specific name to transactions
@trans_var optionis a user-defined variable that is used to hold the transaction name
WITH MARK option enable to mark a particular transaction in the log file
After defining an explicit transaction through the BEGIN TRANSACTION command, the related resources acquired a lock depending on the isolation level of the transaction. For this reason as possible to use the shortest transaction will help to reduce lock issues. The following statement starts a transaction and then it will change the name of a particular row in the Person table.
1
2
3
4
5
6
7
8
BEGINTRAN
UPDATEPerson
SETLastname='Lucky',
Firstname='Luke'
WHEREPersonID=1
SELECT@@TRANCOUNTASOpenTransactions
As we stated in the previous section COMMITTRAN statement applies the data changes to the database and the changed data will become permanent. Now let’s complete the open transaction with a COMMIT TRAN statement.
1
2
3
4
5
6
7
8
BEGINTRAN
UPDATEPerson
SETLastname='Lucky',
Firstname='Luke'
WHEREPersonID=1
SELECT@@TRANCOUNTASOpenTransactions
COMMITTRAN
SELECT@@TRANCOUNTASOpenTransactions
On the other hand, the ROLLBACK TRANSACTION statement helps in undoing all data modifications that are applied by the transaction. In the following example, we will change a particular row but this data modification will not persist.
1
2
3
4
5
6
7
8
9
10
11
BEGIN TRAN
UPDATE Person
SET Lastname='Donald',
Firstname='Duck'WHERE PersonID=2
SELECT *FROM Person WHERE PersonID=2
ROLLBACK TRAN
SELECT *FROM Person WHERE PersonID=2
The following table illustrates the structure of the explicit transactions in SQL Server.
BEGIN TRANSACTION
The starting point of the transaction
SQL commands
DML and SELECT statements
COMMIT TRANSACTION or ROLLBACK TRANSACTION
Apply data changing to the database or Erase data changing to the database
Save Points in Transactions
Savepoints can be used to rollback any particular part of the transaction rather than the entire transaction. So that we can only rollback any portion of the transaction where between after the save point and before the rollback command. To define a save point in a transaction we use the SAVE TRANSACTION syntax and then we add a name to the save point. Now, let’s illustrates an example of savepoint usage. When we execute the following query, only the insert statement will be committed and the delete statement will be rolled back.
1
2
3
4
5
6
7
8
9
BEGINTRANSACTION
INSERTINTOPerson
VALUES('Mouse','Micky','500 South Buena Vista Street, Burbank','California',43)
SAVETRANSACTIONInsertStatement
DELETEPersonWHEREPersonID=3
SELECT*FROMPerson
ROLLBACKTRANSACTIONInsertStatement
COMMIT
SELECT*FROMPerson
Auto Rollback transactions in SQL Server
Generally, the transactions include more than one query. In this manner, if one of the SQL statements returns an error all modifications are erased, and the remaining statements are not executed. This process is called Auto RollbackTransaction in SQL. Now let’s explain this principle with a very simple example.
The SQL CASE expression allows you to evaluate a list of conditions and returns one of the possible results. The CASE expression has two formats: simple CASE and searched CASE.
You can use the CASE expression in a clause or statement that allows a valid expression. For example, you can use the CASE expression in statements such as SELECT, DELETE, and UPDATE or in clauses such as SELECT, ORDER BY, and HAVING.
Simple CASE expression
The following illustrates the simple CASE expression:
CASE expression
WHEN when_expression_1 THEN
result_1
WHEN when_expression_2 THEN
result_2
WHEN when_expression_3 THEN
result_3
...
ELSE
else_result
END
The CASE expression compares an expression to a set of expression (when_expression_1, when_expression_2, when_expression_3, …) using the equality operator (=). If you want to use other comparison operators such as greater than (>), less than (<), etc., you use the searched CASE expression.
The CASE statement returns the result_1, result_2, or result_3 if the expression matches the corresponding expression in the WHEN clause.
If the expression does not match any expression in the WHEN clause, it returns the esle_result in the ELSE clause. The ELSE clause is optional.
If you omit the ELSE clause and the expression does not match any expression in the WHEN clause, the CASE expression returns NULL.
Simple CASE expression example
Let’s take a look at the employees table.
Suppose the current year is 2000.
We can use the simple CASE expression to get the work anniversaries of employees by using the following statement:
The YEAR function returns the year when the employee joined the company. We get the number of years that the employee has been with the company and by subtracting the year when the employee joined the company from the current year (2000).
We get the number of years that the employee has been with the company by subtracting the year when the employee joined the company from the current year (2000).
Then we compare the result with 1, 3, 5, 10, 15, 20, 25, 30 If the year of service equals one of these numbers, the CASE expression returns the work anniversary of the employee.
If the year of services of the employee does not match these numbers, the CASE expression returns NULL.
Searched CASE expression
The following shows the searched CASE expression.
CASE
WHEN boolean_expression_1 THEN
result_1
WHEN boolean_expression_2 THEN
result_2
WHEN boolean_expression_3 THEN
result_3
ELSE
else_result
END;
Search CASE expression example
The following illustrates the searched CASE expression example.
The following statement drops the emergency_contacts table:
DROPTABLE emergency_contacts;
Introduction to the SQL TRUNCATE TABLE statement
To delete all data from a table, you use the DELETE statement without a WHERE clause. For a big table that has few million rows, the DELETE statement is slow and not efficient.
To delete all rows from a big table fast, you use the following TRUNCATE TABLE statement:
When you issue the TRUNCATE TABLE statement, the database system deletes all rows from the table by deallocating the data pages allocated by the table. By doing this, the RDBMS can reduce the resources for logging and the number of locks that need to acquire.
To truncate multiple tables at a time, you can use a list of comma-separated table names after the TRUNCATE TABLE clause as follows:
Not all database systems support this form of the TRUNCATE TABLE statement. If you are using the one that does not, you must issue multiple TRUNCATE TABLE statements to truncate multiple tables.
SQL TRUNCATE TABLE vs. DELETE
Logically the TRUNCATE TABLE statement and the DELETE statement without the WHERE clause gives the same effect that removes all data from a table. However, they do have some differences:
When you use the DELETE statement, the database system logs the operations. And with some efforts, you can roll back the data that was deleted. However, when you use the TRUNCATE TABLE statement, you have no chance to roll back except you use it in a transaction that has not been committed.
To delete data from a table referenced by a foreign key constraint, you cannot use the TRUNCATE TABLE statement. In this case, you must use the DELETE statement instead.
The TRUNCATE TABLE statement does not fire the delete trigger if the table has the triggers associated with it.
Some database systems reset the value of an auto-increment column (or identity, sequence, etc.) to its starting value after you execute the TRUNCATE TABLE statement. It is not the case for the DELETE statement.
The DELETE statement with a WHERE clause deletes partial data from a table while the TRUNCATE TABLE statement always removes all data from the table.
SQL TRUNCATE TABLE examples
Let’s take a look at an example of truncating a table.
First, create a new table named big_table as follows:
CREATETABLE big_table (
idINT AUTO_INCREMENT PRIMARY KEY,
val INT
);
Note that if you use a database system that supports stored procedure, you can put this statement inside a loop. For example, the following stored procedure in MySQL loads data into the big_table table with the number of rows specified by the num parameter.
DELIMITER $$
CREATEPROCEDURE load_big_table_data(INnumint)
BEGINDECLARE counter intdefault0;
WHILE counter < num DOINSERTINTO big_table(val)
VALUES(RAND(1000000));
ENDWHILE;
END$$
As you can see, how fast the TRUNCATE TABLE statement is.
Control User Access
Differentiate system privileges from object privileges Create Users
Grant System Privileges
Create and Grant Privileges to a Role
Change Your Password
Grant Object Privileges
How to pass on privileges?
Revoke Object Privileges
Regular Expression Support
Use the Regular Expressions Functions and Conditions in SQL Use
SQL Regex
Below I have listed down all the Regular Expressions that can be used in SQL.
Pattern
Description
*
Matches zero or more instances of the preceding String
+
Matches one or more instances of the preceding String
.
Matches any single character
?
Matches zero or one instance of the preceding Strings
^
^ matches the beginning of a String
$
$ matches the ending of a String
[abc]
Matches any character listed in between the square brackets
[^abc]
Matches any character not listed in between the square brackets
[A-Z]
Matches any letter in uppercase
[a-z]
Matches any letter in lowercase
[0-9]
Matches any digit between 0-9
[[:<:]]
Matches the beginning of words
[[:>:]]
Matches the end of words
[:class:]
Matches any character class
p1|p2|p3
Mathes any of the specified pattern
{n}
Matches n instances of the preceding element
{m,n}
Matches m through n instances of the preceding element
Meta Characters with Regular Expressions
Perform a Basic Search using the REGEXP_LIKE function Find patterns using the REGEXP_INSTR function Extract Substrings using the REGEXP_SUBSTR function Replace Patterns Using the REGEXP_REPLACE function Usage of Sub-Expressions with Regular Expression Support
Implement the REGEXP_COUNT function
Manage Objects with Data Dictionary Views
Explain the data dictionary
Use the Dictionary Views
USER_OBJECTS and ALL_OBJECTS Views
Table and Column Information
Query the dictionary views for constraint information
Query the dictionary views for view, sequence, index, and synonym information Add a comment to a table
Query the dictionary views for comment information
Manipulate Large Data Sets
Use Subqueries to Manipulate Data
Retrieve Data Using a Subquery as Source
Insert Using a Subquery as a Target
Usage of the WITH CHECK OPTION Keyword on DML Statements List the types of Multitable INSERT Statements
Use Multitable INSERT Statements
Merge rows in a table
Track Changes in Data over a period of time
---------
Create, maintain, and use sequences
Create private and public synonyms
------------
Data Management in Different Time Zones
Time Zones
CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP þÿCompare Date and Time in a Session s Time Zone DBTIMEZONE and SESSIONTIMEZONE
Difference between DATE and TIMESTAMP
INTERVAL Data Types
Use EXTRACT, TZ_OFFSET, and FROM_TZ
Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL
Retrieve Data Using Sub-queries
Multiple-Column Subqueries
Pairwise and Nonpairwise Comparison
Scalar Subquery Expressions
Solve problems with Correlated Subqueries
Update and Delete Rows Using Correlated Subqueries The EXISTS and NOT EXISTS operators
Comments
Post a Comment