Is there a way to make this select retrieve the same results with a single seek?

Question

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy?

Code and schema similar to this are currently on SQL Server 2014.

Repro script. Setup:

USE tempdb;
GO
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL 
  DROP TABLE dbo.TestUpload; 


CREATE TABLE dbo.TestUpload(
    JobRunId bigint NOT NULL,
    ThingAName nvarchar(255) NOT NULL,
    ThingAType nvarchar(255) NOT NULL,
    ThingAGranularity nvarchar(255) NOT NULL,
    ThingBName nvarchar(255) NOT NULL,
    ThingBType nvarchar(255) NOT NULL,
    ThingBGranularity nvarchar(255) NOT NULL
);
CREATE CLUSTERED INDEX IX_JobRunId ON dbo.TestUpload (JobRunId);

GO

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'A', 'B', 'C', 'D', 'E', 'F');
GO 10

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'D', 'E', 'F', 'A', 'B', 'C');
GO 10

Query:

DECLARE @JobRunID bigint = 1;

SELECT JobRunId,
  ThingAName AS Name, 
  ThingAType AS [Type], 
  ThingAGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID
UNION
SELECT JobRunId,
  ThingBName AS Name, 
  ThingBType AS [Type], 
  ThingBGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID;

Tear down:

IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL 
  DROP TABLE dbo.TestUpload;

I think this is probably not modeled ideally. I'm trying to get more information from the developer about how the schema was chosen but am curious if there's a TSQL trick I'm overlooking as it will be easier to change the query than the schema.

Answer 1

I'd try this but I have no idea if it will be more efficient. You need the DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations:

SELECT DISTINCT 
    JobRunId = @JobRunID, 
    d.*
FROM dbo.TestUpload
  CROSS APPLY 
    (   SELECT 
          ThingAName AS Name, 
          ThingAType AS [Type], 
          ThingAGranularity AS Granularity
      UNION                            -- or UNION ALL
        SELECT 
          ThingBName, 
          ThingBType, 
          ThingBGranularity
    ) AS d 
WHERE JobRunId = @JobRunID ;

UNION ALL plan:

UNION plan:

Answer 2

Use cross apply to unpivot columns into rows

SELECT --DISTINCT most probably
  JobRunId,
  ut.Name, 
  ut.[Type], 
  ut.Granularity
FROM dbo.TestUpload
CROSS APPLY (
  SELECT ThingBName AS Name, 
  ThingBType AS [Type], 
  ThingBGranularity AS Granularity
  UNION ALL 
  SELECT ThingAName AS Name, 
  ThingAType AS [Type], 
  ThingAGranularity AS Granularity
  ) ut
WHERE JobRunId = @JobRunID