Referencing another table from a computed column I am working on a project where many of the entities in the data model do not have a name column, but instead the "name" needs to be constructed from a concatenation of multiple columns.  Initially I thought this might be a good use case for a computed column, as it would allow me to define this logic in the database and only once.

However with a fully normalized data model, the computed columns often require values from other tables.  Querying other tables can be accomplished through a UDF, but I have read in several places that using UDFs in computed columns prevents parallel execution (see here).  

I could use views to handle the formatting of these names, but would like to define the logic only once, and if the logic is in a view it would likely result in nesting views, which can also cause problems with performance.

I'm hoping someone has an idea for how I might define this formatting logic only once in the database without causing a performance problem.
You could use an indexed view for this. Assuming that the relationships are simple (foreign key) relationships, I don't see why this would cause any issues.

Simple example, tested in dbfiddle.uk:




create table game
( game_id int not null primary key,
  game_name varchar(100) not null  
) ;

create table area
( area_id int not null primary key,
  area_name varchar(100) not null 
) ;

create table player
( player_id int not null primary key,
  player_name varchar(100) not null 
) ;

  
  

insert into game
values
  (1, 'chess'),
  (2, 'go'),
  (3, 'reversi'),
  (4, 'backgammon'),
  (5, 'hex'),
  (6, 'havannah'),
  (7, 'pacman') ;

insert into area
values
  (11, 'usa'),
  (12, 'russia'),
  (13, 'greece'),
  (14, 'uk'),
  (15, 'france'),
  (16, 'hungary'),
  (17, 'ukraine'),
  (18, 'belgium'),
  (19, 'canada'),
  (20, 'new zealand')  ;

insert into player
values
  (7, 'John'),
  (8, 'Mary'),
  (9, 'Alex'),
  (10, 'Anna'),
  (11, 'Fred'),
  (12, 'Fay') ;



23 rows affected






create table playground
( playground_id int not null identity primary key,
  game_id int not null references game,
  area_id int not null references area,
  player_id int not null  references player,
  various_stuff varchar(100) null default 'abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz',
  constraint playground_uq 
    unique (game_id, area_id, player_id)
) ;






create view dbo.play 
  (game_id, area_id, player_id, name)
WITH SCHEMABINDING 
as
select 
    pg.game_id,
    pg.area_id,
    pg.player_id,
    name = g.game_name + '-' + a.area_name + '-' + p.player_name  
from dbo.playground as pg 
  join dbo.game   as g on g.game_id   = pg.game_id 
  join dbo.area   as a on a.area_id   = pg.area_id
  join dbo.player as p on p.player_id = pg.player_id ;






 -- create an index on the view  
create unique clustered index play_cix   
    on play (game_id, area_id, player_id) ;






insert into playground
  (game_id, area_id, player_id)
select game_id, area_id, player_id
from game, area, player ;



420 rows affected






--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;

select -- top (10) 
   game_id, area_id, player_id, name 
from play 
   WITH (NOEXPAND)            -- Hint used because we are in Express edition
  ;
set statistics xml off;



game_id | area_id | player_id | name                       
------: | ------: | --------: | :--------------------------
      1 |      11 |         7 | chess-usa-John             
      1 |      11 |         8 | chess-usa-Mary             

   --- a few hundred rows omitted

  7 |      20 |        11 | pacman-new zealand-Fred    
  7 |      20 |        12 | pacman-new zealand-Fay     

  
  | Microsoft SQL Server 2005 XML Showplan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
  | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | &lt;ShowPlanXML xmlns=&quot;http://schemas.microsoft.com/sqlserver/2004/07/showplan&quot; Version=&quot;1.8&quot; Build=&quot;14.0.3015.40&quot;&gt;&lt;BatchSequence&gt;&lt;Batch&gt;&lt;Statements&gt;&lt;StmtSimple StatementText=&quot;select -- top (10) &amp;#xa;   game_id, area_id, player_id, name &amp;#xa;from play &amp;#xa;   WITH (NOEXPAND)&quot; StatementId=&quot;1&quot; StatementCompId=&quot;2&quot; StatementType=&quot;SELECT&quot; RetrievedFromCache=&quot;true&quot; StatementSubTreeCost=&quot;0.00522548&quot; StatementEstRows=&quot;420&quot; SecurityPolicyApplied=&quot;false&quot; StatementOptmLevel=&quot;TRIVIAL&quot; QueryHash=&quot;0xBA9BAED6D700FD27&quot; QueryPlanHash=&quot;0xE0086846AA2CCE7F&quot; CardinalityEstimationModelVersion=&quot;140&quot;&gt;&lt;StatementSetOptions QUOTED_IDENTIFIER=&quot;true&quot; ARITHABORT=&quot;true&quot; CONCAT_NULL_YIELDS_NULL=&quot;true&quot; ANSI_NULLS=&quot;true&quot; ANSI_PADDING=&quot;true&quot; ANSI_WARNINGS=&quot;true&quot; NUMERIC_ROUNDABORT=&quot;false&quot;&gt;&lt;/StatementSetOptions&gt;&lt;QueryPlan DegreeOfParallelism=&quot;0&quot; NonParallelPlanReason=&quot;NoParallelPlansInDesktopOrExpressEdition&quot; CachedPlanSize=&quot;16&quot; CompileTime=&quot;0&quot; CompileCPU=&quot;0&quot; CompileMemory=&quot;88&quot;&gt;&lt;MemoryGrantInfo SerialRequiredMemory=&quot;0&quot; SerialDesiredMemory=&quot;0&quot;&gt;&lt;/MemoryGrantInfo&gt;&lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant=&quot;419378&quot; EstimatedPagesCached=&quot;26211&quot; EstimatedAvailableDegreeOfParallelism=&quot;1&quot; MaxCompileMemory=&quot;2073864&quot;&gt;&lt;/OptimizerHardwareDependentProperties&gt;&lt;TraceFlags IsCompileTime=&quot;1&quot;&gt;&lt;TraceFlag Value=&quot;8017&quot; Scope=&quot;Global&quot;&gt;&lt;/TraceFlag&gt;&lt;/TraceFlags&gt;&lt;TraceFlags IsCompileTime=&quot;0&quot;&gt;&lt;TraceFlag Value=&quot;8017&quot; Scope=&quot;Global&quot;&gt;&lt;/TraceFlag&gt;&lt;/TraceFlags&gt;&lt;QueryTimeStats ElapsedTime=&quot;15&quot; CpuTime=&quot;15&quot;&gt;&lt;/QueryTimeStats&gt;&lt;RelOp NodeId=&quot;0&quot; PhysicalOp=&quot;Clustered Index Scan&quot; LogicalOp=&quot;Clustered Index Scan&quot; EstimateRows=&quot;420&quot; EstimatedRowsRead=&quot;420&quot; EstimateIO=&quot;0.00460648&quot; EstimateCPU=&quot;0.000619&quot; AvgRowSize=&quot;174&quot; EstimatedTotalSubtreeCost=&quot;0.00522548&quot; TableCardinality=&quot;420&quot; Parallel=&quot;0&quot; EstimateRebinds=&quot;0&quot; EstimateRewinds=&quot;0&quot; EstimatedExecutionMode=&quot;Row&quot;&gt;&lt;OutputList&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;game_id&quot;&gt;&lt;/ColumnReference&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;area_id&quot;&gt;&lt;/ColumnReference&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;player_id&quot;&gt;&lt;/ColumnReference&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;name&quot;&gt;&lt;/ColumnReference&gt;&lt;/OutputList&gt;&lt;RunTimeInformation&gt;&lt;RunTimeCountersPerThread Thread=&quot;0&quot; ActualRows=&quot;420&quot; Batches=&quot;0&quot; ActualExecutionMode=&quot;Row&quot; ActualElapsedms=&quot;15&quot; ActualCPUms=&quot;15&quot; ActualScans=&quot;1&quot; ActualLogicalReads=&quot;5&quot; ActualPhysicalReads=&quot;0&quot; ActualReadAheads=&quot;0&quot; ActualLobLogicalReads=&quot;0&quot; ActualLobPhysicalReads=&quot;0&quot; ActualLobReadAheads=&quot;0&quot; ActualRowsRead=&quot;420&quot; ActualEndOfScans=&quot;1&quot; ActualExecutions=&quot;1&quot;&gt;&lt;/RunTimeCountersPerThread&gt;&lt;/RunTimeInformation&gt;&lt;IndexScan Ordered=&quot;0&quot; ForcedIndex=&quot;0&quot; ForceScan=&quot;0&quot; NoExpandHint=&quot;1&quot; Storage=&quot;RowStore&quot;&gt;&lt;DefinedValues&gt;&lt;DefinedValue&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;game_id&quot;&gt;&lt;/ColumnReference&gt;&lt;/DefinedValue&gt;&lt;DefinedValue&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;area_id&quot;&gt;&lt;/ColumnReference&gt;&lt;/DefinedValue&gt;&lt;DefinedValue&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;player_id&quot;&gt;&lt;/ColumnReference&gt;&lt;/DefinedValue&gt;&lt;DefinedValue&gt;&lt;ColumnReference Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Column=&quot;name&quot;&gt;&lt;/ColumnReference&gt;&lt;/DefinedValue&gt;&lt;/DefinedValues&gt;&lt;Object Database=&quot;[fiddle_9787548beea04054bb68c8990bb6cdab]&quot; Schema=&quot;[dbo]&quot; Table=&quot;[play]&quot; Index=&quot;[play_cix]&quot; IndexKind=&quot;ViewClustered&quot; Storage=&quot;RowStore&quot;&gt;&lt;/Object&gt;&lt;/IndexScan&gt;&lt;/RelOp&gt;&lt;/QueryPlan&gt;&lt;/StmtSimple&gt;&lt;/Statements&gt;&lt;/Batch&gt;&lt;/BatchSequence&gt;&lt;/ShowPlanXML&gt; |
  


dbfiddle here

and the execution plan of the query that selects from the view, showing that the clustered index on the materialized view is used: