1

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
3

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.

  • Add triggers to the underlying tables if you're prepared to sacrifice write performance for more up to date aggregates. – Michael Green Nov 12 '15 at 0:48
  • @MichaelGreen - are you suggesting to put the code in my answer into an "after trigger"? That might be cataclysmic for performance, depending on how often writes occur, and how many rows exist in the table. – Max Vernon Nov 12 '15 at 16:22
  • 2
    I was hoping to make the OP aware of an alternative he may like to consider and evaluate in his particular circumstances, with his data, load, SLA, use cases etc. etc. While cataclysmic performance should be avoided, apocalyptic stale data is a circumstance which may also be worthy of some cogitation, I would posit. – Michael Green Nov 12 '15 at 18:21
  • 1
    @MichaelGreen Well done on that retort, and you made a good point! – Max Vernon Nov 13 '15 at 4:26
  • It "feels" like this indexing is a bad idea in my case. I appreciate these options and I am investigating others. – Steven Nov 13 '15 at 20:21

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.