2

According to this question/answer and to MSDN documentation it is not possible to use the UNION keyword when designing an Indexed view.

But why is it technically an issue for DBMS?

In this case, could be very useful to be allowed to add an index on VUnion(ID):

CREATE TABLE T1 (ID INT, Name NVARCHAR(100))
CREATE TABLE T2 (ID INT, Info NVARCHAR(1000))

CREATE VIEW VUnion
AS
SELECT ID FROM T1
    UNION
SELECT ID FROM T2
END
Community
  • 1
  • 1
Askolein
  • 3,250
  • 3
  • 28
  • 40

1 Answers1

0

I can't answer the "why" technicalities, probably only the Microsoft developers on the SQL-Server project can shed full light on that.

But why don't you have another table, say T0 which will hold all the IDs that are in either of the 2 tables? You could even have Foreign Key constraints, from T1 and T2, referencing that T0 table.

Of course that would mean extra work for your Insert and Delete procedures on the 2 tables but the problem is solved: you'll have the primary key on T0 (id) which means automatically an index on it.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • That's what I would do. Using triggers or custom well-trimmed store procedures. It seems a bit overkill for such a _simple_ aim. But I obvioulsy miss something when I assert "_simple"_. – Askolein Jul 11 '13 at 15:10