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.

• Erik Reasonable Rates Darling: I'll write a bit more up in a bit, but start here for some potential relief.
• Joe Obbish: Nested views don't cause performance problems. They just make it difficult for you to troubleshoot performance problems.
• S M: Unless you have some very strict - and unusual- requirements, it's okay to not have full normalization where clarity and performance would benefit.

Answer 1

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) &#xa; game_id, area_id, player_id, name &#xa;from play &#xa; 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:

execution plan

Answer 2

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.

• Erik Reasonable Rates Darling: Did you run into the problem where all your queries against the table are forced serial because you put a udf in a computed column?
• Heinrich Smit: The client we did it for has a policy of setting MAXDOP to 1 on all of their reporting servers, so all queries were serial anyway.