Outcome from the ideas - thanks.

Stage 1 was to select directly against the heavily indexed FactPV table. This reduced the processing from 6:14 to 3:24

I then created a summary table...

Cute, but I agree with Ian, if you are using a table why not maintain the correct dates in the table, it is MUCH easier to work with.

I did not...

Thanks Mike

For some reason SourceSafe was not showing the differences between the 2 files (or I was not saving them correctly) and I could not locate the Row Delimiter line,...

~nano

I'm glad you got into this one first - completely contradicts the madness article a few months ago. Having been down both tracks (as have we all) I'm still in...

I was only after a general rule, most developers will use varchar because it is easier to handle in UI and business layers, but if it is going to give me...

Remi

The index was not unique, only clustered. I have since removed the index and replaced it with the following clustered index

Datadate DESC, PortfolioID

These being the 2 most used fields for reporting...

Remi

DDL - thanks

CREATE TABLE [dbo].[FactSensi] (

[FileMapID] [numeric](10, 0) NOT NULL ,

[SourceID] [numeric](10, 0) NOT NULL ,

[ProductFamily] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[RiskType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[ProcessDate]...

Sorry Remi, I deleted the clustered index so I have no data on the size. And yes there were a number of additional indexes but they were not related to...

As the DB is in UAT we can guage the report requirements so the indexes were based in the queries.

I intend to defrag and reindex all the tables and see...

Brian

Thanks for the response.

I am trying to understand why my server suddenly died and the most likely culprit is the clustered indexes I added recently. I have removed them, restarted...

AJ

What is the difference between a 4 column primary key (what I call a complex PK) and a CLUSTERED UNIQUE INDEX of 4 columns?