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.
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
, GoldBadge SMALLINT
, SilverBadge SMALLINT
, BronzeBadge SMALLINT
INSERT INTO dbo.Players (PlayerID, GoldBadge, SilverBadge, BronzeBadge)
(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:
, MaxBadgeCount = MAX(t.Badge)
SELECT PlayerID, Badge = GoldBadge
SELECT PlayerID, SilverBadge
SELECT PlayerID, BronzeBadge
GROUP BY t.PlayerID
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:
, MaxBadgeCount = MAX(t.BadgeCount)
BadgeCount FOR Badge IN (
GROUP BY t.PlayerID
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.
, MaxBadgeCount = (
) t (BadgeCount)
FROM dbo.Players p
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.
IF OBJECT_ID ('dbo.Students') IS NOT NULL
DROP TABLE dbo.Students;
CREATE TABLE dbo.Students
, Subject1 SMALLINT
, Subject2 SMALLINT
, Subject3 SMALLINT
, Subject4 SMALLINT
, Total SMALLINT
INSERT INTO dbo.Students (StudentID, Subject1, Subject2, Subject3, Subject4, Total)
(1, 85, 94, 78, 90, 347), (2, 75, 88, 91, 78, 332)
We will get the following result:
We can solve this task by reading the data several times using UNION ALL with sorting:
, t.[Subject] , t.Total
SELECT ID = StudentID, StudentID, [Subject] = Subject1, Total, rn = 1
SELECT StudentID, NULL, Subject2, NULL, 2
SELECT StudentID, NULL, Subject3, NULL, 3
SELECT StudentID, NULL, Subject4, NULL, 4
ORDER BY t.ID, t.rn
We can also go back to the UNPIVOT statement with checking line numbers:
Student_Name = CASE WHEN rn = 1 THEN t.StudentID END
, t.[Subject] , Total = CASE WHEN rn = 1 THEN t.Total END
, [Subject] , Total
, rn = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY 1/0)
FROM dbo.Students s
[Subject] FOR tt IN (
However, the VALUES statement allows creating a more sophisticated query:
OUTER APPLY (
(StudentID, Subject1, Total)
, (NULL, Subject2, NULL)
, (NULL, Subject3, NULL)
, (NULL, Subject4, NULL)
) t (Student_Name, [Subject], Total)
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.
Reference: Pinal Dave (http://blog.sqlauthority.com)