For best performance,

Cluster the table by

LAST_DAY_OF_MONTH

and not the dopey identity column. If you still need the identity (??), you can leave it as the nonclustered pk.

You would need to code the time constraints in your WHERE clause. That is, bypass those rows yourself rather than trying to have SQL do it itself.

You might not...

No. CASE is very flexible: you can compare any columns you need to, in any...

ScottPletcher (2/11/2013)

---

Michael Valentine Jones (2/11/2013)

---

ScottPletcher (2/11/2013)

---

Method below works for any and all date and language settings:

DECLARE @startDate datetime

DECLARE @number_of_months int

SET @startDate = GETDATE()

SET @number_of_months = 7

SELECT

DATEADD(DAY,...

[superceded by next post]

Marius.D (2/11/2013)

---

Scott:

I did not, but would that make a difference? I didn't think the missing indexes...

Method below works for any and all date and language settings:

DECLARE @startDate datetime

DECLARE @number_of_months int

SET @startDate = GETDATE()

SET @number_of_months = 7

SELECT

DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7...

Hmm, that still doesn't make sense.

Compare them very closely: there must be some difference somewhere ... or maybe you specified DESC on one of the existing index keys?!

In SQL Server, there is no difference.

I guess the backward compatability would be that you can still use "numeric" rather than the offical "decimal".

Yeah, reasonably close.

Probably the major difference is that the OP and myself don't want to "flatten" the Chain and Store into a single "location".

nick.mcdermaid (2/7/2013)

---

Thanks for your advice Scott.

I did change the PK to

RowNum_Start,SRC_System, SRC_Application

and also

SRC_System, SRC_Application,RowNum_Start

(Yes I know you can create a clustered index that isn't based on the primary key)

In both...

You could move the BLOB(s) to a different table; use views if you need to, to combine the original table columns and the BLOB(s), to keep current apps "happy".

So are you doing a REORG or a REBUILD?

Is it on Enterprise Edition (2008)?

You need to keep this normalized and have a separate table for every relation. You could even create an additional table to control the chain/store incidents if needed.

Here's some...

You could partition the table; if you're lucky, the older data will not even need rebuiilt, and you can greatly reduce rebuild time and overhead.

Even if it all still needs...