0

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?

1

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.

| improve this answer | |
  • I know. I am more interested what's the cost of having, so many indexed views and if it can be compared with having indexes. I have never met something like this before (system with hundreds indexed views) and want to understand the internals of managing them. – gotqn May 22 at 16:40
  • Indexed views are indexes, so the cost should be similar. – David Browne - Microsoft May 22 at 16:43
  • Thanks for confirming this. I will try to change the design, but for now the indexed views will be used... – gotqn May 22 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.