-- creating the database and the original table
CREATE DATABASE DB_STACK_OVERFLOW;
GO

USE DB_STACK_OVERFLOW;
GO

CREATE SCHEMA questions_76231541;
GO

CREATE TABLE questions_76231541.PriceNodeLookupIndex
(
    Id              int IDENTITY(1,1) NOT NULL,
    PriceNodeId     int NOT NULL,
    ItemId          int NOT NULL,
    OptionValueId1  int NULL,
    OptionValueId2  int NULL,
    OptionValueId3  int NULL,
    OptionValueId4  int NULL,
    OptionValueId5  int NULL,
    OptionValueId6  int NULL,
    OptionValueId7  int NULL,
    OptionValueId8  int NULL,
    OptionValueId9  int NULL,
    OptionValueId10 int NULL,
    OptionValueId11 int NULL,
    OptionValueId12 int NULL,
    OptionValueId14 int NULL,
    OptionValueId15 int NULL,
    OptionValueId13 int NULL,
    OptionValueId16 int NULL,
    OptionValueId17 int NULL,
    OptionValueId18 int NULL,
    OptionValueId19 int NULL,
    OptionValueId20 int NULL,

    CONSTRAINT PK_PriceNodeLookupIndex 
        PRIMARY KEY NONCLUSTERED (Id)  
)
GO

-- populating the table with 1 million rows
SET NOCOUNT ON;
DECLARE @I INT = 0, @SQL NVARCHAR(max);
WHILE @I <  1000000 
BEGIN
   SET @SQL = N'INSERT INTO questions_76231541.PriceNodeLookupIndex 
                (ItemId, PriceNodeId, OptionValueId' + CAST(CAST(20 * RAND() AS TINYINT) + 1 AS VARCHAR(16)) 
                + ') VALUES (' + CAST(CAST(2000000000 * RAND() AS INT) AS VARCHAR(16)) 
                        + ', ' + CAST(CAST(2000000000 * RAND() AS INT) AS VARCHAR(16)) 
                        + ', ' + CAST(CAST(2000000000 * RAND() AS INT) AS VARCHAR(16)) + ');'
   EXEC (@SQL);
   SET @I = @I + 1;
END;

-- updating other columns
UPDATE questions_76231541.PriceNodeLookupIndex
SET OptionValueId1  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId2  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId3  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId4  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId5  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId6  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId7  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId8  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId9  = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId10 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId11 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId12 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId13 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId14 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId15 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId16 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId17 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId18 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId19 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND(),
    OptionValueId20 = FLOOR(COALESCE(OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5,  OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10,  OptionValueId11,  OptionValueId12,  OptionValueId14,  OptionValueId15,  OptionValueId13,  OptionValueId16,  OptionValueId17,  OptionValueId18,  OptionValueId19,  OptionValueId20)) * RAND();

-- inserting the values corresponding to the requested answer
INSERT INTO questions_76231541.PriceNodeLookupIndex 
          (PriceNodeId, ItemId, OptionValueId5, OptionValueId11, OptionValueId13)
   VALUES (11111,       2345,   63423,          97543,           39452)
GO

-- mesuring IO
SET STATISTICS IO ON;
GO

SELECT PriceNodeId 
FROM   questions_76231541.PriceNodeLookupIndex 
WHERE  ItemId = 2345
  AND  OptionValueId5  = 63423
  AND  OptionValueId11 = 97543
  AND  OptionValueId13 = 39452;
GO
--> query cost (without index) : 11.0137, logical reads 14667 (may vary slightly from different machine with SQL Server)

--> viewing the storage (may vary slightly from different versions of SQL Server)
EXEC sp_spaceused 'questions_76231541.PriceNodeLookupIndex'
GO
--  questions_76231541.PriceNodeLookupIndex (without index) => 106672 KB (data), 17904 KB (index)

-- adding the optimizer requested index 
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [questions_76231541].[PriceNodeLookupIndex] ([ItemId],[OptionValueId5],[OptionValueId11],[OptionValueId13]);
GO
--> query cost (with index) : 0.00657, logical reads 5 (may vary slightly from different machine with SQL Server)

--> viewing the storage (may vary slightly from different versions of SQL Server)
EXEC sp_spaceused 'questions_76231541.PriceNodeLookupIndex'
GO
--  questions_76231541.PriceNodeLookupIndex (with only one index) => 106672 KB (data), 48608 KB (index)

-- STORAGE : 107 MB for data, 49 MB for indexes (only one index over 3 columns)

-- NOW normaling the table into 2 tables :

-- creating the "values" tables
CREATE TABLE questions_76231541.PriceNodeLookupIndex_values 
( Id            INT NOT NULL REFERENCES questions_76231541.PriceNodeLookupIndex ON DELETE CASCADE, 
  Position      INT NOT NULL, 
  OptionValue   INT NOT NULL, 
  CONSTRAINT PK_PriceNodeLookupIndex_values PRIMARY KEY(Id, Position)); 
GO

-- inserting into the "values" table from the original table
INSERT INTO questions_76231541.PriceNodeLookupIndex_values
SELECT Id,  1, OptionValueId1  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId1  IS NOT NULL
UNION ALL
SELECT Id,  2, OptionValueId2  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId2  IS NOT NULL
UNION ALL
SELECT Id,  3, OptionValueId3  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId3  IS NOT NULL
UNION ALL
SELECT Id,  4, OptionValueId4  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId4  IS NOT NULL
UNION ALL
SELECT Id,  5, OptionValueId5  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId5  IS NOT NULL
UNION ALL
SELECT Id,  6, OptionValueId6  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId6  IS NOT NULL
UNION ALL
SELECT Id,  7, OptionValueId7  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId7  IS NOT NULL
UNION ALL
SELECT Id,  8, OptionValueId8  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId8  IS NOT NULL
UNION ALL
SELECT Id,  9, OptionValueId9  FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId9  IS NOT NULL
UNION ALL
SELECT Id, 10, OptionValueId10 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId10 IS NOT NULL
UNION ALL
SELECT Id, 11, OptionValueId11 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId11 IS NOT NULL
UNION ALL
SELECT Id, 12, OptionValueId12 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId12 IS NOT NULL
UNION ALL
SELECT Id, 13, OptionValueId13 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId13 IS NOT NULL
UNION ALL
SELECT Id, 14, OptionValueId14 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId14 IS NOT NULL
UNION ALL
SELECT Id, 15, OptionValueId15 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId15 IS NOT NULL
UNION ALL
SELECT Id, 16, OptionValueId16 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId16 IS NOT NULL
UNION ALL
SELECT Id, 17, OptionValueId17 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId17 IS NOT NULL
UNION ALL
SELECT Id, 18, OptionValueId18 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId18 IS NOT NULL
UNION ALL
SELECT Id, 19, OptionValueId19 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId19 IS NOT NULL
UNION ALL
SELECT Id, 20, OptionValueId20 FROM questions_76231541.PriceNodeLookupIndex WHERE OptionValueId20 IS NOT NULL;
GO

-- droping unnecessary columns (OptionValueId1 .. OptionValueId20) - need to drop the index before...
DROP INDEX [<Name of Missing Index, sysname,>]
   ON [questions_76231541].[PriceNodeLookupIndex];
GO
ALTER TABLE questions_76231541.PriceNodeLookupIndex
   DROP COLUMN OptionValueId1,  OptionValueId2,  OptionValueId3,  OptionValueId4,  OptionValueId5, 
               OptionValueId6,  OptionValueId7,  OptionValueId8,  OptionValueId9,  OptionValueId10, 
               OptionValueId11, OptionValueId12, OptionValueId13, OptionValueId14, OptionValueId15, 
               OptionValueId16, OptionValueId17, OptionValueId18, OptionValueId19, OptionValueId20; 
GO
-- ...and to clean all unused bytes
ALTER TABLE questions_76231541.PriceNodeLookupIndex REBUILD;
ALTER TABLE questions_76231541.PriceNodeLookupIndex_values REBUILD;
ALTER INDEX PK_PriceNodeLookupIndex ON questions_76231541.PriceNodeLookupIndex REBUILD;
ALTER INDEX PK_PriceNodeLookupIndex_values ON questions_76231541.PriceNodeLookupIndex_values REBUILD;
GO

-- rewriting the query for the new structure
SELECT DISTINCT PriceNodeId 
FROM   questions_76231541.PriceNodeLookupIndex AS PN
       JOIN questions_76231541.PriceNodeLookupIndex_values AS PNV5  ON PN.Id = PNV5.Id
       JOIN questions_76231541.PriceNodeLookupIndex_values AS PNV11 ON PN.Id = PNV11.Id
       JOIN questions_76231541.PriceNodeLookupIndex_values AS PNV13 ON PN.Id = PNV13.Id
WHERE  ItemId = 2345
  AND  PNV5.OptionValue  = 63423 AND PNV5.Position  =  5
  AND  PNV11.OptionValue = 97543 AND PNV11.Position = 11
  AND  PNV13.OptionValue = 39452 AND PNV13.Position = 13;
GO
--> query cost (without index) : 3.55, logical reads 2636

EXEC sp_spaceused 'questions_76231541.PriceNodeLookupIndex'
EXEC sp_spaceused 'questions_76231541.PriceNodeLookupIndex_values'

-- STORAGE without index : 
--    questions_76231541.PriceNodeLookupIndex        =>  21016 KB (data) 18720 KB (index)
--    questions_76231541.PriceNodeLookupIndex_values    415896 KB (data)  1552 KB (index)
--    437 MB for data, 20 MB for indexes

-- créating news indexes (2) for the new structure
CREATE INDEX X002 ON questions_76231541.PriceNodeLookupIndex (ItemId);
CREATE INDEX X001 ON questions_76231541.PriceNodeLookupIndex_values (OptionValue);
GO

--> query cost (with index) : 0.02779, logical reads 13

-- STORAGE with indexes : 
--    questions_76231541.PriceNodeLookupIndex        =>   21016 KB (data)  37480 KB (index)
--    questions_76231541.PriceNodeLookupIndex_values =>  415896 KB (data) 359656 KB (index)
--  437 MB for data, 397 MB for indexes

-- creating a view that is the exact equivalent to the original table :
CREATE VIEW dbo.V_PriceNodeLookupIndex
AS
SELECT Id, PriceNodeId, ItemId, 
       [1] AS OptionValueId1,
       [2] AS OptionValueId2,
       [3] AS OptionValueId3,
       [4] AS OptionValueId4,
       [5] AS OptionValueId5,
       [6] AS OptionValueId6,
       [7] AS OptionValueId7,
       [8] AS OptionValueId8,
       [9] AS OptionValueId9,
       [10] AS OptionValueId10,
       [11] AS OptionValueId11,
       [12] AS OptionValueId12,
       [13] AS OptionValueId13,
       [14] AS OptionValueId14,
       [15] AS OptionValueId15,
       [16] AS OptionValueId16,
       [17] AS OptionValueId17,
       [18] AS OptionValueId18,
       [19] AS OptionValueId19,
       [20] AS OptionValueId20
FROM   (SELECT PN.Id, PN.PriceNodeId, PN.ItemId, V.Position, V.OptionValue
        FROM   questions_76231541.PriceNodeLookupIndex AS PN
               LEFT OUTER JOIN questions_76231541.PriceNodeLookupIndex_values AS V
                  ON PN.Id = V.Id) AS SRC
PIVOT  (MAX(OptionValue)
        FOR Position IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10], 
                         [11], [12], [13], [14], [15], [16], [17], [18], [19], [20])
       ) AS PV;
GO

SELECT PriceNodeId 
FROM   dbo.V_PriceNodeLookupIndex 
WHERE  ItemId = 2345
  AND  OptionValueId5  = 63423
  AND  OptionValueId11 = 97543
  AND  OptionValueId13 = 39452;
GO

--> query cost (with index) : 0.00997, logical reads 64