mqbk (8/14/2014)

---

Yeah I was just wondering if a cte could be used to improve the performance, but I see the census is that it will have no added value; therefore, I will not use it.

As for the question what kind of partition did I use. I am creating a file group for each month. I went with this route because they were having read and write performance issues on the table - the particular table in question was approx 60 GB in size. The reason for that size was because they were trying to keep every transaction possible from the time the database was created. I had to implement some type of historical retention process because simple select queries were a nightmare. At one time it would take nearly 15 minutes for a select where the records were 30 days old from today's date. I am not attributing all of the performance enhancement to the partitioning of the table because along with it I created better indices as well as fixed the fragmentation on some of the key ones. It was actually a combination of things that helped to improve.

Also, we need a quick and clean way to handle record retention. It's the least taxing - in my opinion - on server resources to perform a switch to our historical table than attempt to do some type of insert and purge process.

If there is a better, more efficient, and server resource cost effective way deal with the issue I am opened to the idea.