SQL SERVER – Query Plan Analysis of Pivot and Unpivot with Profiler of dbForge Studio for SQL Server

Probably, everyone who writes SQL code has used INSERT statement, which alongside SELECT, UPDATE and DELETE, is a part of a basic operations’ set for data manipulation. Let us learn about Query Plan Analysis.

At the first sight syntax of the INSERT statement may seem very trivial, especially when inserting one record – INSERT INTO … VALUES … .

It’s not surprising, whereas in SQL Server 2005 basic syntax the VALUES keyword was applied only in the context of inserting records through using the INSERT statement. However, after the release of SQL Server 2008 the basic syntax of T-SQL was considerably expanded. Owing to this, usage of the multiline VALUES statement became possible not only for record insertion.

This article will focus on several possible cases of the VALUES statement usage. To present an unbiased assessment of the advantages of the VALUES statement, we will use built-in Profiler of dbForge Studio for SQL Server to look at the query plan for each particular example.

We have a table with the following structure, and we need to find the maximum amount of medals (disregarding their value) for each player:

IF OBJECT_ID ('dbo.Players') IS NOT NULL
DROP TABLE dbo.Players;
CREATE TABLE dbo.Players
(
PlayerID INT
, GoldBadge SMALLINT
, SilverBadge SMALLINT
, BronzeBadge SMALLINT
);
INSERT INTO dbo.Players (PlayerID, GoldBadge, SilverBadge, BronzeBadge)
VALUES
(1, 5, 4, 0),
(2, 0, 9, 1),
(3, 2, 4, 10);

To accomplish the task we need to convert columns into rows, so that afterwards we are able to apply the MAX aggregation function.

At first, let’s look at the example that is very common among beginners:

Note, instead of reading data once, we are reading it three times from the table.

Starting from SQL Server 2005, the UNPIVOT operator is generally used to convert columns into rows:

Let’s look at the plan:

Now data from the table is read only once and that is what we’ve been trying to achieve. However, there is still one more bottleneck left in our query (the most resource-consuming operation) – it’s the Sort operation which allows a server to detect the element with maximal value.

Let’s try to avoid row sorting using the VALUES statement.

As we can see, sorting is not applied here.

Let’s compare our queries and the results they return

Let’s examine the next sample, where we need to format row-wise data output in the grouped table.

We will get the following result:

We can solve this task by reading the data several times using UNION ALL with sorting:

We can also go back to the UNPIVOT statement, checking line numbers:

However, the VALUES statement allows creating a more sophisticated query:

Let’s take a look at the plan and compare the results:

The VALUES statement cannot be considered a full substitute to UNPIVOT statement as well as a savior in other cases. It all goes down to the query plan, and analyzing it with the help of Profiler, available in dbForge Studio for SQL Server, allows detecting potential bottlenecks in query productivity. Additionally, while we are on the subject of Database Diagram, it does makes sense to mention Database Diagram tool from dbForge. It allows us to visualize a database structure for further analysis as well help us to create and edit database objects on a diagram. You can get the Database Diagram Tool from here.

Reference: Pinal Dave (https://blog.sqlauthority.com)