Database Reference
In-Depth Information
Figure 15-15.
Tiered Storage in Action: Further steps
All of these operations can be done online with the Enterprise Edition of SQL Server 2012 and above. They can
also be done online with SQL Server 2005-2008R2, as long as the tables do not contain LOB columns.
There is still the possibility of a lengthy hold of the schema modification (SCH-M) lock at the time when you
switch
Orders2014_05
into the
Orders2013
table. One of the things you need to do during this process is to change the
CHECK constraint on the
Orders2014
table, indicating that the table now stores May 2014 data. Unfortunately, SQL
Server always scans one of the indexes in the table to validate CHECK constraints and holds the schema modification
(SCH-M) lock during the scan.
One of the ways to work around such a problem is to create multiple CHECK constraints at the
CREATE TABLE
stage and drop them later. In the example shown in Listing 15-26, we create 12 CHECK constraints in the
Orders2014
table. Every time we switch the operational table as the partition, we are dropping a constraint, a metadata operation,
rather than creating a new one.
