Understood.

I think I will just drop the index. So far the overhead of querying the view is not so great and data is returned within 400ms compared to 30ms...

Thanks.. Seem like I'm toast then.

So if its flawed by design /cross join, where did i go wrong?

Basically what I want to achieve is a weekly snapshot of products...

Not wanting to lleave the topic closed with incorrect info.. here are the Corrections.. :

Alter FUNCTION [dbo].[FctDateTime_FirstDayOfWeek2]

(

-- Add the parameters for the function here

@Date date

)

RETURNS date

with schemabinding

AS

BEGIN

declare @firstDate date

declare @Daysdiff...

Seems your use of datediff and 19000101 was one of the keys to help me solve the problem. Thank you.

Following IS deterministic:

ALTER FUNCTION [dbo].[FctDateTime_FirstDayOfWeek]

(

@Date date

)

RETURNS date

with schemabinding

AS

BEGIN

declare @firstDate date

declare...

Thanks Lynn,

Date part was used as it is documented as determenistic.

previously I had used:

DATEADD(dd, (DATEPART(dw, Date_Received) - 1) * - 1, Date_Received)

Unfortunately the formulae provided are not determenistic...

Hi Michael,

Currently needs to be in a persisted computed column hence no variables. It's not in a function. Once it works will no doubt put in function also.

quote taken from http://www.sqlballs.com/2012/05/cross-database-views-and-schema-binding.html

An Indexed View is essentially a Materialized View. All of the data in the view instead of existing as a select statement is persisted...