Data Partitioning - Pro SQL Server Internals - page 302

/* Other Columns */
constraint PK_MainData
primary key clustered(ADate, ID)
on psMainData(ADate)
);

create nonclustered index IDX_MainData_CustomerId
on dbo.MainData(CustomerId)
on psMainData(ADate);

create table dbo.StagingData
(
ADate datetime not null,
ID bigint not null,
CustomerId int not null,
/* Other Columns */
constraint PK_StagingData
primary key clustered(ADate, ID),

constraint CHK_StagingData
check(ADate >= '2014-05-01' and ADate < '2014-06-01')
) on [FG2014];

create nonclustered index IDX_StagingData_CustomerId
on dbo.StagingData(CustomerId)
on [FG2014];

/* Switching partition */
alter table dbo.StagingData
switch to dbo.MainData
partition 5;

■ Note We will discuss locking in greater detail in part 3, “Locking, Blocking, and Concurrency.”

Both tables must have exactly the same schema and indexes. The staging table should be placed in the same
filegroup with the destination partition in the partitioned table. Finally, the staging table must have a CHECK constraint,
which prevents values outside of the partition boundaries.
As you probably noticed, all nonclustered indexes have been partitioned in the same way as clustered indexes.
Such indexes are called aligned indexes . Even though there is no requirement to keep indexes aligned, SQL Server
would not be able to switch partitions when a table has non-aligned, nonclustered indexes defined.
Finally, the partition switch operation does not work if a table is referenced by foreign key constraints defined in
other tables. Nevertheless, a partition switch is allowed when the table itself has foreign key constraints referencing
other tables.

Partitioned Views
Unlike partitioned tables, partitioned views work in every edition of SQL Server. In such schema, you create individual
tables and combine data from all of them via a partitioned view using the union all operator.