Great. I am going to start working on the dev for this. Appreciate the help.

Let me ask you this. If I was to never tear down any dim and instead use incremental loading in my ETL process to insert/update/delete, could that be a...

Well that is a good point you made. I suppose that these dims do NOT need to be broken down and rebuilt every night. For example, I have...

Unfortunately, YES. I am having to rebuild the dimensions every night because the data in them could change any day. If I cannot do anything given my situation...

As of now, there are several nonclustered indices placed on the fact table. (these were built to help improve the querying of the BO reports) We do have...

Thanks Steve. Last question. Let's say partitioning is not an option and I don't want to use the partition view, are there any other things I can do...

Thanks Jeff. I will read up on this. Sounds like my only option right now and would be better than using just this stand alone table. Thanks...

Is this essentially...

Well, my dreams may have been crushed. I tried to create a Partition Function and when I hit the execute button I got the following message:

Partition function can only...

GilaMonster (5/14/2014)

---

You can partition an existing table. Not by adding partitions to it, but by rebuilding the clustered index onto a partition scheme. This will take as long as rebuilding...

This could be what is confusing me. This is the clustered index I have on the table and there are also several other nonclustered indices.

USE [APSB_STAR]

GO

/****** Object: Index...

Im not really sure how to go about getting the range of keys that I want deleted.

That was definitely the problem. Thanks so much.

I have other indices that are longer in length that return without truncation. I am using this script in an ETL package, btw.

I changed my index to a clustered index with just the School_Yr column and reran my TSQL. It completed in 10 mins.