0

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?

Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • Because i'm not sure HOW to do it .. (yes.. i've tried .. but the subqueries are killing me re: indexing a view). – Pure.Krome Jul 29 '10 at 03:47

1 Answers1

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.

Will A
  • 24,780
  • 5
  • 50
  • 61