When it comes to manipulating data in SQL Server, the INSERT statement is one of the fundamental operations. Alongside SELECT, UPDATE, and DELETE, it allows us to add new records to a table. While the basic syntax of the INSERT statement may seem straightforward, there are some lesser-known features that can greatly enhance its functionality.

In SQL Server 2005, the VALUES keyword was primarily used for inserting records through the INSERT statement. However, with the release of SQL Server 2008, the syntax of T-SQL was expanded, allowing for multiline VALUES statements in various contexts. In this article, we will explore several use cases of the VALUES statement and discuss its advantages.

Case 1: Finding the Maximum Amount of Medals

Let’s consider a scenario where we have a table containing player information, including the number of gold, silver, and bronze medals they have won. Our goal is to find the player with the maximum number of medals, regardless of their value.

To accomplish this task, we need to convert the columns into rows so that we can apply the MAX aggregation function. One common approach, often used by beginners, involves reading the data multiple times from the table:

SELECT t.PlayerID, MaxBadgeCount = MAX(t.Badge)
FROM (
    SELECT PlayerID, Badge = GoldBadge FROM dbo.Players
    UNION ALL
    SELECT PlayerID, SilverBadge FROM dbo.Players
    UNION ALL
    SELECT PlayerID, BronzeBadge FROM dbo.Players
) t
GROUP BY t.PlayerID

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

SELECT t.PlayerID, MaxBadgeCount = MAX(t.BadgeCount)
FROM (
    SELECT * FROM dbo.Players
    UNPIVOT (BadgeCount FOR Badge IN (GoldBadge, SilverBadge, BronzeBadge)) unpvt
) t
GROUP BY t.PlayerID

While this approach reduces the number of times we read data from the table, it still involves a resource-consuming Sort operation to detect the element with the maximum value.

Alternatively, we can avoid row sorting by using the VALUES statement:

SELECT p.PlayerID, MaxBadgeCount = (
    SELECT MAX(BadgeCount)
    FROM (
        VALUES (p.GoldBadge), (p.SilverBadge), (p.BronzeBadge)
    ) t (BadgeCount)
)
FROM dbo.Players p

By using the VALUES statement, we eliminate the need for sorting, resulting in improved query performance.

Case 2: Formatting Row-Wise Data Output

Let’s consider another scenario where we have a table containing student information, including their scores in different subjects. Our goal is to format the data output in a grouped table.

One approach to achieve this is by reading the data multiple times using UNION ALL with sorting:

SELECT t.StudentID, t.[Subject], t.Total
FROM (
    SELECT ID = StudentID, StudentID, [Subject] = Subject1, Total, rn = 1 FROM dbo.Students
    UNION ALL
    SELECT StudentID, NULL, Subject2, NULL, 2 FROM dbo.Students
    UNION ALL
    SELECT StudentID, NULL, Subject3, NULL, 3 FROM dbo.Students
    UNION ALL
    SELECT StudentID, NULL, Subject4, NULL, 4 FROM dbo.Students
) t
ORDER BY t.ID, t.rn

Another approach is to use the UNPIVOT statement with line numbers:

SELECT Student_Name = CASE WHEN rn = 1 THEN t.StudentID END, t.[Subject], Total = CASE WHEN rn = 1 THEN t.Total END
FROM (
    SELECT StudentID, [Subject], Total, rn = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY 1/0)
    FROM dbo.Students s
    UNPIVOT ([Subject] FOR tt IN (Subject1, Subject2, Subject3, Subject4)) unpvt
) t

However, the VALUES statement provides a more concise and elegant solution:

SELECT t.*
FROM dbo.Students
OUTER APPLY (
    VALUES
        (StudentID, Subject1, Total),
        (NULL, Subject2, NULL),
        (NULL, Subject3, NULL),
        (NULL, Subject4, NULL)
) t (Student_Name, [Subject], Total)

By using the VALUES statement, we can achieve the desired output without the need for complex subqueries or multiple reads from the table.

It’s important to note that the VALUES statement is not a full substitute for the UNPIVOT statement or a solution for every scenario. The choice between these approaches depends on the specific requirements of your query and the query plan generated by the SQL Server optimizer.

By exploring the various use cases of the VALUES statement, we can leverage its power to optimize our SQL queries and improve overall performance.