Can you expand the filegroup, or perhaps create a separate filegroup for the indexes?

Either will work. I use my version, because someone who doesn't know the tricks of dateadd won't have the faintest clue what that one does. Could be documented...

That's already been suggested and apparently isn't a viable option.

Just keep in mind that indexes on large tables take up large amounts of disk space.

john.arnott (7/28/2008)

---

jims posted code:

Set Nocount On

Create Table #Temp

(

Val int

)

Insert Into #Temp values (1)

Insert Into #Temp values (2)

Insert Into #Temp values (3)

Insert Into #Temp values (4)

Insert Into #Temp values (5)

Set...

Taking indexing offline mainly reduces the hit on tempdb. I don't think it makes any particular speed difference, but I haven't tested that so can't be sure. Certainly...

Every tool has it's right place. For what you're talking about, I'd use dynamic SQL too (and probably crib it from your workbench - I use a lot of...

If it starts with a value, it will have that value until something resets it, updates it, or it goes out of scope. I don't see anything in your...

Have you tested the value? Added a select or print command after the assignment to see what value it is?

If you don't want time data in there, make sure to put a check constraint on the column that forces the hours, minutes, seconds and milliseconds to be zero. ...

The first thing I'd do is break it apart a little bit. Try running the derived table queries separately.

The one thing I see in the execution plan that might...

A clustered index on ProgramDate and half_hour_id looks like it might be a good candidate for the clustered index. You use both in Where and Join clauses, and they'll...

I didn't bother looking at the second one, so I missed that. The whole thing looks like a mess to me.

Phil, here's why I recommend pivoting data outside of SQL.

Using your sample, I inserted 1-million rows of data into the same table structure you used.

Then I added three more months...

I don't know of a formal method to mark a thread as done. Usually, they just end with the original poster saying something like you just did. "Hey,...