Is it possible to make an Indexed View for a particular result I want?

Question

Hi folks,

Is it possible to create an indexed view which returns the following results :-

• ID | Location Name | Aliases for that Location
• 1 | Some Location | Alias 1, Alias 2, Alias 3
• 2 | Another Location | NULL
• 3 | Yet Another Location | NULL
• 4 | Last location | An Alias

My table structure is

Location Table

• LocationId INTEGER
• Name NVARCHAR(100)

LocationAlias Table

• LocationAliasId INTEGER
• LocationId INTEGER
• Name NVARCHAR(100)

and of course, a Location can have 0<->Many Location Aliases

So, can this be done?

Answer 1

It seems highly doubtful that it is possible to index such a view - SQL Server will not allow indexes to be created on views that use subqueries, nor on views that use self-joins (see the docs here) - so I think you're out of luck.