Referencing another table from a computed column

Question

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.

ypercubeᵀᴹ · Answer 1 · 2018-03-27T15:59:16.507

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

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

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:

score 0 · Answer 2 · answered Jan 24 '18 at 08:51

I don't think there is one correct answer to this. As hinted at in some of the comments, you will almost always have some trade-off between performance and complexity/maintainability.

We had a similar case where the use of views was not an option, and we opted for a UDF in the computed column. The problem we encountered is that you can't persist the column, which is needed to index it if the function references other objects such as tables.

It seems that you are aware of the options available to you, and just need to find the best solution for you and your stakeholders.

Referencing another table from a computed column

2 Answers2