Table Partitioning

Working on learning partitioned tables. We've done it, and it works really well for us, but it's time to start getting clever.

Was reading this, which is an excellent primer:
http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

A few rules I learned the hard way. I deliberately did it from scratch in hopes that I'd remember it better - what to do, what not to do. In particular, working on switching tables into partitions (our current stuff switches out, not in)

* clustered key of the table you're switching in has to be on the same filegroup as the partitioned table.
* indexes have to match. ALL of them.
* indexes are NOT on the partition scheme
* only one constraint (below)
* Add a constraint to make sure the partitioned key is within the partition range (order_date >='20100428' and order_date <'20100429' and order_date is not null)
* Then, SPLIT the range (code below), and SWITCH the table in.

    ALTER PARTITION SCHEME ps_daily NEXT USED your_partitioned_fg
    
    ALTER PARTITION FUNCTION pf_daily() SPLIT RANGE ( @Day) --@day is one day larger than current max date

    ALTER TABLE orders_stg SWITCH TO dbo.orders PARTITION 200

Now, what if you need to add an old day in? You have up to 5/4/2010, but need 5/3?

ALTER PARTITION SCHEME [ps_daily] NEXT USED your_partitioned_fg
ALTER PARTITION FUNCTION [pf_daily] () SPLIT RANGE (N'2010-05-03')