title: SQL Server How to properly create a view upon dependent tables?
body: <p>Given the following tables (see below) I need to create a View that shows each code (SOC, HLGT, HLT, PT AND LLT) from table <code>MedDRARelations</code> and his respective value (Soc_Value, HLGT_value, HLT_Value, PT_Value and LLT_Value) from the table <code>MedDRANames</code>.</p>
  
  <p>I've tried by joining multiple times the <code>MedDRANames</code> table and creating a scalar function which returns value given the code (key).</p>
  
  <p>Table definitions:</p>
  
  <pre><code>CREATE TABLE dbo.MedDRANames 
  (
      [Key]   INT NOT NULL,
      [Value] VARCHAR(100) NULL,
      CONSTRAINT [PK_MedDRANames] PRIMARY KEY CLUSTERED ([Key])
  );
  
  CREATE TABLE dbo.MedDRARelations 
  (
      [SOC]  INT NOT NULL,
      [HLGT] INT NOT NULL,
      [HLT]  INT NOT NULL,
      [PT]   INT NOT NULL,
      [LLT]  INT NOT NULL,    
      CONSTRAINT [PK_MedDRARelations] 
          PRIMARY KEY CLUSTERED ([SOC] ASC, [HLGT] ASC, [HLT] ASC, [PT] ASC, [LLT] ASC),
      CONSTRAINT [FK_MedDRANameSOC] 
          FOREIGN KEY ([SOC]) REFERENCES dbo.MedDRANAmes([Key]),
      CONSTRAINT [FK_MedDRANameHLGT] 
          FOREIGN KEY ([HLGT]) REFERENCES dbo.MedDRANAmes([Key]),
      CONSTRAINT [FK_MedDRANameHLT] 
          FOREIGN KEY ([HLT]) REFERENCES dbo.MedDRANAmes([Key]),
      CONSTRAINT [FK_MedDRANamePT] 
          FOREIGN KEY ([PT]) REFERENCES dbo.MedDRANAmes([Key]),
      CONSTRAINT [FK_MedDRANameLLT] 
          FOREIGN KEY ([LLT]) REFERENCES dbo.MedDRANAmes([Key])
  );
  </code></pre>
  
  <p>Views: </p>
  
  <p>This view executes in 22.1 s (SD 1.25) and affects 156867 rows </p>
  
  <pre><code>SELECT 
      [X].[SOC] AS [Código SOC],
      [SOC].[Value] AS [Término SOC],
      [X].[HLGT] AS [Código HLGT],
      [HLGT].[Value] AS [Término HLGT],
      [X].[HLT] AS [Código HLT],
      [HLT].[Value] AS [Término HLT],
      [X].[PT] AS [Código PT],
      [PT].[Value] AS [Término PT],
      [X].[LLT] AS [Código LLT],
      [LLT].[Value] AS [Término LLT]
  FROM 
      dbo.MedDRARelations AS [X]
  INNER JOIN 
      dbo.MedDRANames AS [SOC] ON [X].[SOC] = [SOC].[Key]
  INNER JOIN 
      dbo.MedDRANames AS [HLGT] ON [X].[HLGT] = [HLGT].[Key]
  INNER JOIN 
      dbo.MedDRANames AS [HLT] ON [X].[HLT] = [HLT].[Key]
  INNER JOIN 
      dbo.MedDRANames AS [PT] ON [X].[PT] = [PT].[Key]
  INNER JOIN 
      dbo.MedDRANames AS [LLT] ON [X].[LLT] = [LLT].[Key]
  </code></pre>
  
  <p>This view executes in 35.3 s (SD 2.1) and affects 156867 rows </p>
  
  <pre><code>SELECT 
      [X].[SOC] AS [Código SOC],
      dbo.FindMedDRA(x.SOC) AS [Término SOC],
      [X].[HLGT] AS [Código HLGT],
      dbo.FindMedDRA(x.HLGT) AS [Término HLGT],
      [X].[HLT] AS [Código HLT],
      dbo.FindMedDRA(x.HLT) AS [Término HLT],
      [X].[PT] AS [Código PT],
      dbo.FindMedDRA(x.PT) AS [Término PT],
      [X].[LLT] AS [Código LLT],
      dbo.FindMedDRA(x.LLT) AS [Término LLT]
  FROM 
      dbo.MedDRARelations AS [X]
  </code></pre>
  
  <p>Scalar functions:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[FindMedDRA]
      (@code INT)
  RETURNS VARCHAR(100)
  AS
  BEGIN
      DECLARE @returning VARCHAR(100)
  
      SELECT @returning = dbo.MedDRANames.Value 
      FROM dbo.MedDRANames 
      WHERE dbo.MedDRANames.[Key] = @code
  
      RETURN @returning
  END
  </code></pre>
  
  <p>I would like to know what is the proper way to achieve this because creating a joined table elapses 12.2 s (SD 0.1) but costs 5 times original space.</p>
  
  <p>Thanks in advance.</p>
  
  <p><b>UPDATE: Execution plans</b></p>
  
  <ol>
  <li>Using function:  <a href="https://www.brentozar.com/pastetheplan/?id=rkso0aKnV" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=rkso0aKnV</a></li>
  <li>Before Ixs creations:  <a href="https://www.brentozar.com/pastetheplan/?id=HkFlyRth4" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=HkFlyRth4</a></li>
  <li>After Ixs creations (as suggested by Laughing Vergil): <a href="https://www.brentozar.com/pastetheplan/?id=B13ZW0t3V" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=B13ZW0t3V</a></li>
  </ol>