In the end I suppose EAV's live or die on the design and implementation like any DB, but we only hear the horror stories of using EAV's when it all...

It might be worth you reading these excelent article by Jeff Moden on Cross Tabs and pivots.

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Bhuvnesh (11/15/2012)

Why you think that partitioning wouldnt be useful here ? i think it will help you here as...

Thankfully I've never had the pleasure of knowingly working with an EVA, and I've heard enough horror stories about them to not want to.

Fair point Aaron, it would mean dipping into Dynamic SQL to inject the value into the Alter table script.

If it can load over midnight then you might have a problem.

Just a thought but cant you alter the default constraint at the start of each load process so that...

Are the ValidFrom and ValidTo Date or DateTime, as you couldput default constraints on them.

That way you dont have to run the update after the load and only have...

Aaron,

I wonder if using a CTE the cross tab trick to generate a wide list for the tagset names and tag_id's for each child_work_id would work, you could then join...

Its a shame you have no scope to be able to add lookups.

Is this for an SQL 2008 (or greater) edition?

You might be able to work with the table...

update [R33HypInfa].[dbo].[DF012_HFM_Staging]

set ICP=

(select B.ICP

from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A

...

I've had a quick look at the query and you have an awful lof of correlated sub queries, this could be where the performance issue is as each query would...

A Clustered Index doesnt need to be unqiue, in fact the only case where you have to have the Unique index (to my knowledge) is on a PK which by...

I take it the bolded part is the issue, ie this statement

(SELECT TOP(1)SQL10.DASHBOARD_STATUS_LEVEL

GROUP BY SQL10.MARKET_PROJECT

ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC) AS [PROJECT_LEVEL],

Which makes no sense to me as

1) you...

I've managed to get a script out and alter it to use temp tables

CREATE TABLE #tblChargeBacks(

[ID] [int] IDENTITY(1,1) NOT NULL,

[amount] [float] NULL,

[OrderID] [nvarchar](50) NULL,

[OperationDate] [datetime] NULL,

[type] [nvarchar](50) NULL,

CONSTRAINT [PK_tblChargeBacks]...

Thanks Anthony, especially for the...