Search code examples
sql-serversql-server-2008indexed-view

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

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?

Solution

  • 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.