What to Replace Left Join in a View So I Can Have an Indexed View

What to replace left join in a view so i can have an indexed view?

There is a "workaround" here that involves check for NULL in the join and having a NULL representation value in the table

NULL value

INSERT INTO Father (Father_id, Father_name) values(-255,'No father')

The join

JOIN [dbo].[son] s on isnull(s.father_id, -255) = f.father_id

Indexed Views and Left Joins once and for all

Why are you indexing your views? You mentioned "waste of space" in your solution 2, but did you know that when you index your view, you persist it on the DB?

In other words, you make a copy of the data the view would return on the DB and every time the data is updated on the source tables, some internal mechanism of SQL Server has to update it on this new data structure created because now SQL server reads from the view, not the tables anymore.

If you use Profiler + DTA or even DMVS you can come up with the correct indexes to be created on your tables that any view would benefit from

Cannot create CLUSTERED INDEX on a View due to LEFT JOIN or subquery

Instead of creating an exclusionary view, why not try the other way:

CREATE VIEW dbo.HighestCompany
AS
  SELECT t1.CompanyID, t1.Company, t1.Revision, s.StatusName
    FROM dbo.Companies AS t1
    INNER JOIN (
      SELECT Company, HighestCompany = MAX(CompanyID) 
      FROM dbo.Companies GROUP BY Company
    ) AS t2
    ON t1.Company = t2.Company
    AND t1.CompanyID = t2.HighestCompany -- not sure if CompanyID is unique
    INNER JOIN dbo.Statuses AS s
    ON s.StatusID = t1.StatusID;

You still won't be able to create an indexed view on this, but it may work a little better than the versions you currently have (depending on several factors, of course, including index on Company and selectivity).

Aside from that, I think to improve performance you'll need to take a look at the indexing strategy on the base tables. Why does your Companies table allow multiple companies with the same name and a different ID? Maybe that is part of the problem, and you should just store the currently relevant company in a separate table.

You could do this as follows (keep in mind I am guessing at data types and optimal indexes here):

CREATE SCHEMA hold AUTHORIZATION dbo;
GO
CREATE SCHEMA cache AUTHORIZATION dbo;
GO
CREATE TABLE dbo.HighestCompany
(
  CompanyID INT, 
  Company NVARCHAR(255) PRIMARY KEY,
  Revision INT,
  StatusName NVARCHAR(64)
);
GO
CREATE TABLE cache.HighestCompany
(
  CompanyID INT, 
  Company NVARCHAR(255) PRIMARY KEY,
  Revision INT,
  StatusName NVARCHAR(64)
);
GO

Now however often you think this data needs to be refreshed, you can run a job that does the following:

TRUNCATE TABLE cache.HighestCompany;

INSERT cache.HighestCompany(CompanyID, Company, Revision, StatusName)
SELECT t1.CompanyID, t1.Company, t1.Revision, s.StatusName
        FROM dbo.Companies AS t1
        INNER JOIN (
          SELECT Company, HighestCompany = MAX(CompanyID) 
          FROM dbo.Companies GROUP BY Company
        ) AS t2
        ON t1.Company = t2.Company
        AND t1.CompanyID = t2.HighestCompany
        INNER JOIN dbo.Statuses AS s
        ON s.StatusID = t1.StatusID;

-- this is a fast, metadata operation that should result
-- in minimal blocking and disruption to end users:
BEGIN TRANSACTION;
  ALTER SCHEMA hold TRANSFER dbo.HighestCompany;
  ALTER SCHEMA dbo TRANSFER cache.HighestCompany;
  ALTER SCHEME cache TRANSFER hold.HighestCompany;
COMMIT TRANSACTION;

If you find the companies change so often, or the data really needs to be up-to-the-second, that this isn't practical, you could do something similar with a trigger as @Dems suggested.

Alternative for full outer join in indexed view

There is a workaround for indexed views with 'emulated outer joins'. However, it's hideous and I wouldn't advocate it.

A better solution would be to index the underlying tables and skip the whole view. Or create two indexed views and do the outer join in the query calling the views.

Yet another solution is to not create an indexed view, but an actual table (which you can keep up-to-date with triggers) and put indexes on that. Since you're materializing the view anyway, this might be the best solution.

In all likelihood, an indexed view (whichever method you choose) won't give you the (performance) result you are looking for (though the last method where you create an actual table might). Can you explain why you feel you need an indexed view in this particular case?

By the way, here is a (short) explanation why indexed views don't allow outer joins.

Substitute a LEFT OUTER JOIN in SQL Server (SSMS)

You can use two separate indexed views, like this:

create schema ADVANCED
go

    create table ADVANCED.BIF951_C(id int, I_BILLNUMBER int, D_BILLDATE datetime, C_CUSTOMER INT, C_ACCOUNT INT)

    CREATE TABLE ADVANCED.BIF003ToBeDeleted(ID INT, C_CUSTOMER INT, C_ACCOUNT INT)

    GO

    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_1
    WITH SCHEMABINDING
    AS
        SELECT  
            I_BILLNUMBER 
        FROM 
            ADVANCED.BIF951_C 
        JOIN 
            ADVANCED.BIF003ToBeDeleted ON BIF003ToBeDeleted.C_CUSTOMER = ADVANCED.BIF951_C.C_CUSTOMER 
                                       AND BIF003ToBeDeleted.C_ACCOUNT = ADVANCED.BIF951_C.C_ACCOUNT
     GO

     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_1 ON ADVANCED.BillsToBeDeletedVIEW_1(I_BILLNUMBER)
     GO

    CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW_2
    WITH SCHEMABINDING
    AS

        SELECT I_BILLNUMBER
        FROM  ADVANCED.BIF951_C 
        WHERE D_BILLDATE < CONVERT(DATETIME,'2016-06-01',120)

        GO

     CREATE UNIQUE CLUSTERED INDEX IX_BillsToBeDeletedVIEW_2 ON ADVANCED.BillsToBeDeletedVIEW_2(I_BILLNUMBER)
     GO

     CREATE OR ALTER VIEW ADVANCED.BillsToBeDeletedVIEW
     AS 

       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_1
       UNION
       SELECT I_BILLNUMBER
       FROM BillsToBeDeletedVIEW_2

Indexed view: Union All / Full Join alternatives

Assuming both of your base tables have a unique constraint on 'Name' then the only way uniqueness can be violated is if the same name is in both tables.

i.e. you expect a join against them to return zero rows. So you can cross join the result of that join against a table with 2 rows and create a unique index against that.

CREATE TABLE dbo.Two
  (
     N INT PRIMARY KEY
  )

INSERT INTO dbo.Two
VALUES      (1),
            (2)

GO

CREATE VIEW dbo.UniqueNames
WITH SCHEMABINDING
AS
  SELECT T1.Name
  FROM   dbo.T1
         INNER JOIN dbo.T2
           ON T1.Name = T2.Name
         CROSS JOIN dbo.Two

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON dbo.UniqueNames(Name) 

Indexed View looking for null references without INNER JOIN or Subquery

What you have here is essentially a hierarchical dataset in which you want to pre-traverse the hierarchy and store the result in an indexed view, but AFAIK, indexed views do not support that.

On the other hand, this may not be the only angle of attack to your larger goal of improving performance. First, the most obvious question: can we assume that TransactionId is clustered and ReplacesTransactionId is indexed? If not, those would be my first two changes. If the indexing is already good, then the next step would be to look at the query plan of your left join and see if anything leaps out.

In general terms (not having seen the query plan): one possible approach could be to try and convert your SELECT statement to a "covered query" (see https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/). This would most likely entail some combination of:

• Reducing the number of columns in the SELECT statement (replacing SELECT *)
• Adding a few "included" columns to the index on ReplacesTransactionId (either in SSMS or using the INCLUDES clause of CREATE INDEX).

Good luck!

---

Related Topics