Sql-server – Indexed Complex View

materialized-viewsql serversql server 2014

The aggregate functions MIN,MAX,AVG, and STDEV are not allowed in an Indexed View in SQL Server (Create Indexed Views – MSDN Library).

Does SQL Server have a mechanism to index the results of the following view, even though INSERT, UPDATE, and DELETE operations would become much more expensive?

SELECT 
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM test
INNER JOIN samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id

Best Answer

Create a permanent table to house the computed results:

CREATE TABLE dbo.ViewResult
(
    test_id INT NOT NULL
        CONSTRAINT PK_ViewResult
        PRIMARY KEY CLUSTERED
    , tMin AS DECIMAL(18,5)
    , tMax AS DECIMAL(18,5)
    , tAvg AS DECIMAL(18,5)
    , tStDev AS DECIMAL(18,5)
);

Create a SQL Server Agent job that performs the following actions:

TRUNCATE TABLE dbo.ViewResult;

INSERT INTO dbo.ViewResult    
SELECT 
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM dbo.test
    INNER JOIN dbo.samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id;

Schedule this Job to run as often as you need the "view" updated.

This will allow fast DML operations, while still providing reasonably fresh info about the data.

Related Question