Indexed View (Precise ColProperty Probs...)

[dalski]

Q1 - Why is the simple ID1 clustered index (PK in Test1T) & regurgitated in the view (dbo.vw_TestIndexView) not indexable?
Q2 - A Non-Persisted Calc'd Col (in tbl) needs meet criteria below in order to be indexable as a non-clustered index; assuming true for the Total Calc'd Col:
a - Deterministic - meets criteria
b - Precise - it fails; despite being cast to decimal (38,26) everywhere. To avoid overflow on multiplaction the result of the calculation is cast to (38,26) & nested in a round to 36dp. Regardless if I was using different scaled data-types this would be taken care of in casting the calc'd column.
___________________________________________________________________________________________________________________________________________________

Trying to use the priniciples of a Computed Col In Tbl Redgate Tut by Robert Sheldon to an Indexed View (basic db attached containing only items mentioned) & creation code at very bottom to produce Calc'd Col (Total).

Objects
• dbo.Test1T
• dbo.Test2T
• dbo.vwTestIndexView

Indexed View Criteria

Results

___________________________________________________________________________________________________________________________________________________
Creation Code: Below Irrelevant If Opening Db
Create Tables

USE Test
-- Create 1st Tbl
IF OBJECT_ID('dbo.Test1T','Table') IS NOT NULL
    DROP TABLE dbo.Test1T
GO
CREATE TABLE dbo.Test1T (
    ID1 int NOT NULL PRIMARY KEY
    ,NrOrdered DECIMAL (38,26)
);
GO
INSERT INTO dbo.Test1T (ID1, NrOrdered)
    VALUES (1,5),
    (2,9);


-- Create 2nd Tbl
GO
IF OBJECT_ID('dbo.Test2T','Table') IS NOT NULL
    DROP TABLE dbo.Test2T
GO
CREATE TABLE dbo.Test2T (
    ID2 int NOT NULL PRIMARY KEY
    ,ID1_FK int FOREIGN KEY REFERENCES dbo.Test1T(ID1)
    ,Price DECIMAL (38,26)
);
GO
INSERT INTO dbo.Test2T (ID2, ID1_FK, Price)
    VALUES (1,1,10),
    (2,2,3);

Create View

USE [Test]
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;
GO

-- Del view if exists
IF OBJECT_ID('dbo.vw_TestIndexView', 'view') IS NOT NULL
    DROP VIEW dbo.vw_TestIndexView;

    --Create view with calc'd col
GO
CREATE VIEW dbo.vw_TestIndexView
    WITH SCHEMABINDING
    AS
    SELECT
        dbo.Test1T.ID1
        ,dbo.Test1T.NrOrdered
        ,dbo.Test2T.Price
        ,ROUND(CAST(dbo.Test1T.NrOrdered * dbo.Test2T.Price AS DECIMAL(38,26)),38) AS TOTAL
    FROM dbo.Test1T
        INNER JOIN dbo.Test2T ON dbo.Test1T.ID1 = dbo.Test2T.ID1_FK;

Create Indexes On View

CREATE UNIQUE CLUSTERED INDEX idx_TestID1 ON dbo.vw_TestIndexView (
    ID1
);
GO
CREATE NONCLUSTERED INDEX idx_Total ON dbo.vw_TestIndexView (
    Total
);

Test Column Criteria To Meet Indexable View Criteria

DECLARE @id int = OBJECT_ID('dbo.vw_TestIndexView')
SELECT
    COLUMNPROPERTY(@id, 'ID1', 'Indexable') AS 'ID1-Indexable',
    COLUMNPROPERTY(@id, 'ID1', 'Precise') AS 'ID1-Precise',
    COLUMNPROPERTY(@id, 'NrOrdered', 'Precise') AS 'NrOrdered-Precise',
    COLUMNPROPERTY(@id, 'Total', 'IsDeterministic') AS 'Total-Deterministic',
    COLUMNPROPERTY(@id, 'Total', 'Precise') AS 'Total-Precise';

 

[sonic8]

@dalski , I really like that you provided the SQL Statements to quickly reproduce the problem! This is the prime way to ask this type of question.

I ran your scripts and, as far as I can see, they work without issue.
Here is an query on the system views that show that there are your two indexes created on the view:

I just created a blank 'Test' database with the default settings (of my local server) and copied, pasted, and ran your scripts from above.
So, it appears very likely that your server, database, or connection options are set in a way the cause problems with creating the indexed view.

 

[dalski]

Thanks for testing Sonic, appreciated & for your code; I got the same results so good!

My concern is that both the MSN article & the Redgate tutorial states that if the criteria is not met then the view indexes (clustered & non-clustered) can produce unreliable results. It's the below that really has me worried; stating

• The regurgitated clustered index ID1-Indexable is not indexable
• Non-clustered index (on the calc'd column Total-Precise is not precise)

Maybe my interpretation is incorrect.

DECLARE @id int = OBJECT_ID('dbo.vw_TestIndexView')
SELECT
    COLUMNPROPERTY(@id, 'ID1', 'Indexable') AS 'ID1-Indexable',
    COLUMNPROPERTY(@id, 'ID1', 'Precise') AS 'ID1-Precise',
    COLUMNPROPERTY(@id, 'NrOrdered', 'Precise') AS 'NrOrdered-Precise',
    COLUMNPROPERTY(@id, 'Total', 'IsDeterministic') AS 'Total-Deterministic',
    COLUMNPROPERTY(@id, 'Total', 'Precise') AS 'Total-Precise';

 

[sonic8]

My concern is that both the MSN article & the Redgate tutorial states that if the criteria is not met then the view indexes (clustered & non-clustered) can produce unreliable results.

I skimmed both of the referenced resources and couldn't find any mentioning of "unreliable results".
In my experience it is simply not possible to create an index on a column that is not suitable due to non-met requirements.

The NULL results in your queries of the COLUMNPROPERTY are indeed somewhat surprising. However, the documentation of the COLUMNPROPERTY function states that this function is supposed to be used on tables or stored procedures, but it does not mention views. A NULL return value of the function indicates "invalid input". - Which might be due to the fact that the function is not supposed to be used on a view.

 

[dalski]

Thanks Sonic, MSN state any expressions within an indexed view (amongst other criteria...)
1 - Must be deterministic
2 - Precise - 'Only precise deterministic expressions can participate in key columns & in Where or Group By..' So I read that as 'if it's a calc'd column it must be deterministic & precise'.

documentation of the COLUMNPROPERTY function states that this function is supposed to be used on tables or stored procedures, but it does not mention views. A NULL return value of the function indicates "invalid input". - Which might be due to the fact that the function is not supposed to be used on a view.

No, MSN say to use the ColumnProperty to verify an Indexed View's column. One would think the clustered indexed primary key of the view itself would show as indexable from the view's column property.