SQL partitioning on non-enterprise server?

Question

I tried using partition function on my SQL server to partition one of my large table but I got an error saying "Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning." So I was wondering how does everyone else that doesn't have Enterprise version solve this?

Any advice would be great!

Answer 1

The method Oleg Dok is referring to is called a "partitioned view". Microsoft has covered it extensively in the SQL 2000 and 2005 docs. Just Google the term and you'll get lots of advice.

Basically, if you have N tables with identical schemas, you can create a view on top of them that UNIONs them together and exposes a consolidated view of your data. There is a fairly good blog post describing this. You'll hit some hiccups, especially around performance, so choose indexes wisely, keep the stats updated, and make sure to query on them properly.