Creating indexed views

Question

When I try to create indexed view I am getting an error "datepart returns indeterministic value, use system defined deterministic function or make the user defined function to return deterministic value" I get this error when i try to create index on the view..

The query is

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
as 
  SELECT dbo.day_dim.date_time AS Date, dbo.order_dim.quantity AS Target_Acheived
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id
   WHERE (DATEPART(wk, CONVERT(datetime, dbo.day_dim.date_time, 101)) <= DATEPART(wk, GETDATE() - 2))
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time
GO
CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I on two_weeks_performance (Date,Target_Acheived)`

In this date_time is varchar(30) type. Give me a solution to over come this problem.

score 1 · Accepted Answer · answered Nov 17 '10 at 04:13

1

Use:

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS 
  SELECT dbo.day_dim.date_time AS Date, 
         dbo.order_dim.quantity AS Target_Acheived
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id   
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time

The issue was the use of GETDATE(); it's non-deterministic, returning a different value every time.

So you'd have to use:

SELECT t.*
  FROM dbo.two_weeks_performance t
 WHERE DATEPART(wk, CONVERT(datetime, t.date, 101)) <= DATEPART(wk, GETDATE() - 2)

answered Nov 17 '10 at 04:13

OMG Ponies

325,700
82
523
502

But i want to filter the results before creating the view. I want just recent 2 weeks report to go into the view. Any solutions for that ? – Deepak Nov 17 '10 at 04:15
@Deepak: You can't, as long as you want to use an non-deterministic value - it's one of the fundamental issues with materialized/indexed views. Using DATEPART/etc also won't be able to use the index created on the view, either. – OMG Ponies Nov 17 '10 at 04:18
so that is a limitation isn't it ? and what happens if I remove the dbo.order_dim.quantity from group by clause and add SUM() near select ? – Deepak Nov 17 '10 at 04:21
now i am getting an error saying that "select list does not use correct usage of COUNT_BIG(). But i am not using it anywhere ... – Deepak Nov 17 '10 at 04:26
@Deepak: [SUM is valid](http://msdn.microsoft.com/en-us/library/ms191432.aspx), but it will be the sum per DATETIME value (including time). – OMG Ponies Nov 17 '10 at 04:27
yeh thats fine.. i want that only.. i am getting this strange error now.. "select list does not use correct usage of COUNT_BIG()". – Deepak Nov 17 '10 at 04:30
@Deepak: With respect, different error -- different question. – OMG Ponies Nov 17 '10 at 04:33
@Deepak: Thank you, sorry I can't help regarding the `COUNT_BIG` error. – OMG Ponies Nov 17 '10 at 04:36

Creating indexed views

1 Answers1