Create an index on SQL view with UNION operators? Will it really improve performance?

You cannot create an index on a view that makes use of a union operator. Really no way around that, sorry!

I would imagine you've seen this, but check out this MSDN page. It gives the requirements for indexed views and explains what they are and how they work.

As to whether or not you'd see a performance benefit if you COULD index the view, that would depend entirely on the size of your tables. I would not expect any impact on creating separate indexed views, as I would assume that your tables are already indexed and you aren't doing any joining or logic in the view.

Why in the WORLD are you using UNION?

With the literals in your SQL there is ZERO chance that you'll have duplicates. So again, why use UNION?

UNION forces a distinct to occur and there's little slower than DISTINCT.

But since you have something that looks like this:

SELECT 'A'UNIONSELECT 'B'UNIONSELECT 'C'

There's no possibility that you'll ever have duplicates.

Change it to UNION ALL and your query will perform much faster.

This is fundamental SQL - writing a well tuned query is more important than creating view indexes. Start with the basics, understand SQL, tune your query, THEN worry about spending space and slowing DML to improve query speed.

EDIT:

The literals in the query prevent dupes between tables. The only remaining possibility is dupes within a table(s). Since the columns look like PKs and there are no joins that could induce duplication and since the tables all look like lookup tables, what I said is correct. If that assumption isn't true than you may have a legitimate use of UNION without an ALL. However I find that 99% of the time people really meant to use ALL and it's a standard at our company to add a comment to SQL with only UNION because it's so often a mistake. i.e. UNION -- yes i need a distinct list.