Properly Index an UNPIVOT SQL query

Question

If I had a table such as:

CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY,
    StudentName VARCHAR (50),
    Math INT,
    English INT,
    History INT,
    Science INT
)
GO

and an unpivot query such as:

SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
    Score
    FOR Course in (Math, English, History, Science)
) AS SchoolUnpivot

What would an optimal index look like?

Answer 1

I populated 1.000.000 test data into Students table and then I started to test the following queries;

Note Don't use the DBCC DROPCLEANBUFFERS statment in the production environment.

Test environment :

Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64) Aug 16 2019 14:20:53 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 17763: )

Test-1:

The following query takes 34 seconds.

DBCC DROPCLEANBUFFERS
GO
 SELECT StudentName, Course, Score
FROM Students
 CROSS APPLY (
    VALUES 
        ('Math', Math),
        ('English', English),
        ('History', History),
        ('Science', Science)
    ) x(Course, Score)
WHERE Score IS NOT NULL

OPTION (MAXDOP 1)

Test-2:
The following query takes 40 seconds.

DBCC DROPCLEANBUFFERS
GO
 SELECT StudentName, Course, Score
FROM Students
 CROSS APPLY (
    VALUES 
        ('Math', Math),
        ('English', English),
        ('History', History),
        ('Science', Science)
    ) x(Course, Score)
WHERE Score IS NOT NULL

OPTION (MAXDOP 1)

Test-3:

The following query takes 32seconds after the creation of the index , also the execution plan uses the created index in the execution plan.

   CREATE NONCLUSTERED INDEX [PerformanceIndex] ON [dbo].[Students]
(
    [Id] ASC,
    [Math] ASC,
    [English] ASC,
    [History] ASC,
    [Science] ASC
)
INCLUDE([StudentName])
GO
 DBCC DROPCLEANBUFFERS
 GO
SELECT StudentName, Course, Score
FROM Students
UNPIVOT
(
    Score
    FOR Course in (Math, English, History, Science)
) AS SchoolUnpivo

OPTION (MAXDOP 1)

As a result, using the unpivot columns in the nonclustered index helps us to improve the query performance particularly for this case.