I am using MSSQL Server 2008 R2, and I am trying to optimize my views when I come across indexed views. Unfortunately, most of my views use a left outer join, which is not supported by indexed views. After several studies, I left an embarrassed best way to do this. As I see it, I have the following options:
1) Convert left joins to inner joins using a trick to simulate left joins using "OR (IsNull (a) AND IsNull (b))"
I found this solution in several places, but performance loss was mentioned.
2) Convert the left joins to internal joins and replace the null null null values with empty paths (00000000-0000-0000-0000-000000000000) and add one row to the right table using matching guid.
This seems most obvious in terms of performance, but it seems like a waste of space for each row, which would otherwise be NULL.
3) Divide my view into two types. The first point is most of my logic, which is indexable. And the second view, resulting from the first view and adding left connections.
The idea here is that when indexing a base view, there may be performance gain. And the fact that even a request for a derived view will receive at least part of the performance benefits.
4) Do not index my submissions
Would leave an idea how it will be more productive than any of the above options?
5) An idea that I did not think about
I executed my main script as follows:
CREATE TABLE [dbo].[tbl_Thumbnails]( [ThumbnailId] [uniqueidentifier] NOT NULL, [Data] [image] NULL, [Width] [smallint] NOT NULL, [Height] [smallint] NOT NULL CONSTRAINT [PK_tbl_Thumbnails] PRIMARY KEY CLUSTERED ( [ThumbnailId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[tbl_Tags]( [TagId] [uniqueidentifier] NOT NULL, [ThumbnailId] [uniqueidentifier] NULL CONSTRAINT [PK_tbl_Tags] PRIMARY KEY CLUSTERED ( [TagId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE VIEW [dbo].[v_Tags] WITH SCHEMABINDING AS SELECT dbo.tbl_Tags.TagId, dbo.tbl_Tags.ThumbnailId FROM dbo.tbl_Tags LEFT OUTER JOIN dbo.tbl_Thumbnails ON dbo.tbl_Tags.ThumbnailId = dbo.tbl_Thumbnails.ThumbnailId GO INSERT INTO tbl_Tags VALUES ('16b23bb8-bf17-4784-b80a-220da1163584', NULL) INSERT INTO tbl_Tags VALUES ('e8b50f03-65a9-4d1e-b3b4-268f01645c4e', 'a45e357b-ca9c-449a-aa27-834614eb3f6e') INSERT INTO tbl_Thumbnails VALUES ('a45e357b-ca9c-449a-aa27-834614eb3f6e', NULL, 150, 150)
Now, by executing the following query, you will get "It is not possible to create an index in the view" Test.dbo.v_Tags "because it uses a LEFT, RIGHT or FULL OUTER join, and OUTER joins are not allowed in indexed views. Consider using an INNER join instead." :
CREATE UNIQUE CLUSTERED INDEX [TagId] ON [dbo].[v_Tags] ( [TagId] ASC ) GO
This is expected behavior, but what course of action would you recommend getting the best performance from my scenario? The best indicator is the best result.