James A Skipwith (2/22/2013)

---

In the long run, especially with examples of having 7,500 active out of 20M, you will want to move to partitioned views if you stick on standard edition.

Table A - Status constraint to have only a [RowStatus] of "A" (Active)

Table B - Status constraint to have only a [RowStatus] of "H" (Historic)

Table C - Status constraint to have only a [RowStatus] of "X" (Cancelled)

All default inserts go into Table A and are moved into B or C when the status changes. You then access the records via a unioned view:

CREATE VIEW dbo.vAllOrders

AS

SELECT COL1, cOL2, RowStatus

from Table A

UNION ALL

SELECT COL1, cOL2, RowStatus

from Table B

UNION ALL

SELECT COL1, cOL2, RowStatus

from Table C;

The optimizer is bright enough to know, if your constraints are in place and trusted, to only read data from the correct table. So in the example above, SELECT * FROM dbo.vAllOrders WHERE [RowStatus] = 'A' would only read data from Table A (7.5K rows) and would totally ignore Table's B and C (partition elimination).

There's no massive code change involve and this scales very well. Reading history (20M rows) will be slow but reading active records will be very fast. Your storage costs will be far lower and your indexes much more productive. Google partitioned views/partition elimination, as this would definitely be the way forward in the long run. Partition elimination does work in all versions, including standard, and is a great way of getting robust partitioning in situations like these where you cannot use enterprise table partitioning.