Rahuul (1/26/2015)
Thanks Jeff. I got it.On another note for the sake curiosity,
What would be your recommendation had it been a Standard OR Enterprise version of SQL Server (not Azure)?
Single Large table with multiple filegroups for better maintenance and a good archival process?
Thanks,
Rahul
Correct. Partitioned View with one month per filegroup/file for either version or Partitioned Table with one month per partition/filegroup/file. Both have advantages and disadvantages. If you can make older partitions "READ -ONLY) without sealing up a bunch of free space, then even better. I've got a 340GB telephone log at work that I have to keep forever. It's got a little over 5 years of data in it. We're backing up to iSCSI NAS (to get it away from the databases... not good to store backups in the same place as the databases) and so it's a bit slow. It also seem stupid to me to backup 340 GB of data that will never change every night. It was taking 6-10 hours to back it all up. Now, I have a permanent set of the previous months already backed up and I only have to backup the current month, which only takes minutes. It also made index maintenance a whole lot shorter because I only have to defrag the current month NCI's. The CI is ever increasing and it never gets fragged unless there was a proper (and that's a very rare occurrence now).
The partitioning would make the archival process a milli-second breeze but I'm not allowed to archive the data. I have to keep all of it online all the time.
As a bit of a sidebar, partitioning will allow you to do online piecemeal restores so that's a bonus if any corruption occurs.
--Jeff Moden
Change is inevitable... Change for the better is not.