So, first of all I want to thank everyone for the tips provided on the Archiving 10 million records thread.

Along those same lines, we currently want to modify our archive database and position ourselves in such a way that would help easily move files around if needed by partitioning our database. We’re thinking file groups may be the answer but I’m not 100% sure. Most examples I see talk about partitioning data horizontally. Meaning; create 3 partitions, create a partition function, and a partition scheme. This would split my table. This would work great if we knew what field we were trying to partition on. For example; A customer table with cid, fname, lname, mi can be partitioned on lname. Letter’s A thru H would be stored on partition 1, I thru P would be stored on partition 2 and the rest on partition 3.

Since we don’t know the structure up front and what we do know about the structure won’t help in the partition we were thinking about partitioning based on customer. Each customer would get their archive tables stored on an individual partition. What I don’t know is if this would give us any advantage down the line when each customer has several hundred GB’s of data while other may only have a few GB’s of data.

Is this path even worth the effort?