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.