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

StackExchange Database Administrators Q#191843, answer score: 6