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.
Author: Ben Richardson
Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training the UK and offers a full range of SQL training from introductory training to advanced administration courses.
Understanding Pivot Operator in SQL
The pivot operator in SQL Server converts each row in the aggregated result set into corresponding columns in the output set. The pivot operator is particularly useful in writing cross-tabulation queries.
Let’s take a look at how it works in practice.
Understanding Transactions in SQL
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 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.
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
Introducing Common Table Expressions in SQL Server
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.
Introduction to Temporary Tables in SQL Server
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.