I have met the following table design for recording references in non-normalized way:
RecordGUID uniqueidentifier DestinationEntity uniqueidentifier DestinationRecordGUID uniqueidentifier
For example for
Users table to describe references to
Countries, we will have:
RecordGUID DestinationEntity DestinationRecordGUID 79282C80-4474-4617-9C40-C21E97F5AC52 3A462EF8-DAB6-4B98-BA1B-FBD957A1737C 5842E524-8C5D-4DEE-A553-0857FB9C12F4 B25DA064-8DAF-4EEB-8894-D540B3A929EA 3A462EF8-DAB6-4B98-BA1B-FBD957A1737C C2B0AA1A-E688-4E2C-BD5F-16309C496774
C2B0AA1A-E688-4E2C-BD5F-16309C496774 are referring records in
We are having many relations here - for example,
companies, etc. And because working with
GUID in order to join the tables is not user friendly, we have indexed view for each relation like this:
CREATE VIEW [dbo].[users_countries] WITH SCHEMABINDING AS SELECT [RecordGUID] ,[DestinationEntity] ,[DestinationRecordGUID] FROM (the relationship table) WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C';
as a result one can easily make a join with this view to the
counties table and query it - also, because of using the view (with no expand) and having a index on it, not all records from the original table are read (it's faster).
The system may have hundreds of indexed views and I am worried about the cost of the engine to manage them, compare to filtered indexes on the original table. I mean instead having a
view just to create index like this:
CREATE INDEX (index_Name) ON (the relationship table) ( [RecordGUID] ,[DestinationRecordGUID] ) WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C'
We are not able to query in a friendly way the table anymore, but will this affect the performance?
I was not able to find any topics about indexed view hurting the performance - but facing such design makes me doubting.
I am wondering can we say that the maintenance of the index of a view and the same index on a table (like the above) is the same?