Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

4
  • If the Lookup-Tables seldomly change you could create virtual columns on the parent table, which contain the looked-up values and create an index on them? If sql-Server allows the creation of a virtual index, you wouldn't even need virtual columns and could create an index on the lookup-values directly (maybe using a function, which looks up the value?) Commented Aug 17, 2020 at 12:02
  • 1
    @Falco, as far as I can see, there is no way to create "virtual columns", the closest is computed columns, which don't support this kind of thing as far as I can tell. I tried with a UDF, however, as it's non-deteministic you cannot add an index to a column that uses it. The way to do as you have suggested is to create an indexed view, but this doesn't work in my case for the reasons above. Commented Aug 18, 2020 at 1:14
  • If an index on a persistent computed column on a non-deterministic UDF is not possible, you could try a materialized view, or maybe even create a real column and fill it with a DB-Trigger? This would copy the data from the lookup table into columns in the main table, which would need to be updated on changes in the lookup table. Commented Aug 18, 2020 at 9:27
  • @Falco, SQL Server doesn't have materialized views... indexed views are (more or less) the same thing. As you have said, I could pre-resolve the values during insert/update, however, that creates other performance issues and before I go down that path I'd like to see if there is a way to optimise the query. Commented Aug 19, 2020 at 1:40