Sql-server – Cost of indexed view versus filtered index

sql serversql-server-2016sql-server-2019

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

So, the 3A462EF8-DAB6-4B98-BA1B-FBD957A1737C means Countries, while 5842E524-8C5D-4DEE-A553-0857FB9C12F4 and C2B0AA1A-E688-4E2C-BD5F-16309C496774 are referring records in Counries table.

We are having many relations here – for example, users to languages, 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?

Best Answer

That design is terrible. You should have separate tables and real foreign keys. For a quick fix, use the indexed views. If there are perf problems, fix the design.