SQL Server need to partition data, but only have standard edition

Question

Is there a way that I can in code (Sproc ,etc) distribute the data for a table into multiple filegroups without actually having SQL Server partitioning available (Only have Standard Edition)? I wanted to be able to breakout my FileStream data into different "Partitions", but without an Enterprise license I can't actually use the partitioning functionality.

Any suggestions would be greatly appreciated.

Thanks,

S

Answer 1

You can distribute your data into different databases and join them with views. The tricky part of that will be to keep the views updated as you add/remove data.

You need to do this "partition" on a logical key (like a calendar date) where each DB has data within a certain range. If you cluster on this field, the query analyzer will be able to determine which DB to pull data from without issue.

At my workplace we are using this technique for a very large (multi-billion row) data set that we get monthly additions to and it works great.