Thanks, that looks like it will do the job and is along the lines of what I had thought, but it seems expensive.

If you imagine 16000 header records and then...

Great, a calendar table is a good idea.

So lets assume I've plugged the date gaps using a calendar table.

If I do something like this :

SELECT tbl_Calendar.Date, Min(tbl_Report.MyValue) as MyVal

FROM tbl_Calendar...

Also I just noticed my example output is erroneous....

It should be

value dd/mm

12 01/01

12 02/01

14 03/01

14 04/01

15 05/01

There isn't a real table yet, but there will be no nulls just missing dates in the range as in the example I gave.

This seems to be the cause but as yet no resolution :

The component 'MSFTE.DLL' reported error while indexing.

I've still not got any further with this despite reinstalling SQL Server and the indexing service, dropping the table and catalog/index and recreating everything it still hangs up.

this seems to relate to internet access which is not our issue.

It started a number of weeks ago, but can't link it to anything on the server. It's pretty much...

I've since checked the logs (below).

I've tried dropping and recreating both the catalog and index.

2011-07-10 21:42:43.92 spid27s The full-text catalog monitor reported catalog "Catalog_DB_SALES" (12) in...

I already have that thread started as I didn't realise it was the index blocking the backup.

Thanks.

I checked the activity monitor and it is being suspended with a wait type of MSSEARCH.

So I think the other problem I'm having with my full text not populating is...

thanks for the tip...

"adjacency list" I knew it had a name!

ok thanks, point taken, I will put it into future design considerations.

what I managed to establish last night was that adding the deleted flag to the index I had for...

Basically it's taking a hierarchy and representing it in a flatter view so I can query up and down it and do aggregations etc.

It's a typical "CategoryId, ParentId, Name" type...

This view is querying underlying views however they are relatively simple.

dbo.vw_tbl_Product_Categories equates to

SELECT Column names

FROM tbl_Product_Categories

WHERE Deleted = 0

PK / FK relatations are indexed.

When I viewed the execution...

The view contains a CTE and I just realised that prevents indexing.

Therefore I need to optimise the underlying tables / view design.

ALTER VIEW [dbo].[vw_Product_Category_Levels] WITH SCHEMABINDING

AS

WITH TopCat (ParentId,CategoryId, LevelId)

AS

(

SELECT tbl1.CategoryId,...