lestatf4 (1/23/2013)

---

Here is the script, is should return a years worth of data from @month and @year.

Surely the column order in the query is irrelevant to the optimizer?

create view withClusteredIdx

with schemabinding

as

select c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime) mm,YEAR(hhdatetime) yy

from CONTRACTS.MPAN_Contract mc

join CONTRACTS.ContractElementUnitInstance ui

on mc.ContractID = ui.ContractID

join CONTRACTS.Contract c

on ui.ContractID = c.id

and ui.ElementID in (2,3,4)

join CONTRACTS.CalendarAllHH cal

on cal.HHDatetime between ui.Startdate and ui.Enddate

and cal.HHDatetime between mc.startdate and mc.enddate

group by c.Reference, mc.MPAN,ElementID,MONTH(hhdatetime),YEAR(hhdatetime)

CREATE UNIQUE CLUSTERED INDEX [idxTest] ON [dbo].[withClusteredIdx]

(

[ElementID] ASC,

[mm] ASC,

[MPAN] ASC,

[Reference] ASC,

[yy] ASC

) go

select Reference,mpan

from mpanContractMMyyCache

where yy between @YEAR and @YEAR + 1

and ((mm >= @MONTH and yy = @YEAR) or (mm <= @MONTH and yy = @YEAR + 1))