Creating Dynamic Pivot Table with QUOTENAME Function

In my previous article on the basic pivot operator, we saw how pivot operator could be used to convert rows to columns, resulting in pivot tables. We saw that there were three main steps to create a pivot table. The first step was selecting the base data. The second step was converting the base data to a table-valued expression, and the final step involved applying a pivot operator to the temporary data, which resulted in the pivot table.

Read More

The Lost Update Problem in Concurrent Transactions

The lost update problem occurs when 2 concurrent transactions try to read and update the same data. Let’s understand this with the help of an example.

Suppose we have a table named “Product” that stores id, name, and ItemsinStock for a product.

It is used as part of an online system that displays the number of items in stock for a particular product and so needs to be updated each time a sale of that product is made.

Read More

Understanding Dirty Read Problem with SQL Server

One of the most common problems that occur while running concurrent transactions is the Dirty Read problem. A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction which is running concurrently but which has not yet committed itself.

If the transaction that modifies the data commits itself, the dirty read problem doesn’t occur. However if the transaction that modifies the data is rolled back after the other transaction has read the data, the latter transaction has dirty data that doesn’t actually exist. Read More