Creating Dynamic Pivot Table with QUOTENAME Function

Creating Dynamic Pivot Table with QUOTENAME Function
Rate this post

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

Understanding Transactions in SQL

Understanding Transactions in SQL
Rate this post

A transaction in SQL is a unit of execution that groups one or more tasks together. A transaction is considered successful if all the tasks within it are executed without error.

However, if any of the tasks within a transaction fails to execute, the whole transaction fails. A transaction has only two results: successful or failed. Read More

The Lost Update Problem in Concurrent Transactions

The Lost Update Problem in Concurrent Transactions
Rate this post

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

Understanding Dirty Read Problem with SQL Server
4.4 (88.57%) 7 votes

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

Introducing Common Table Expressions in SQL Server

Introducing Common Table Expressions in SQL Server
Rate this post

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Read More

Introduction to Temporary Tables in SQL Server

Introduction to Temporary Tables in SQL Server
4.5 (89.14%) 35 votes

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

Read More