Listing 1: Code to Create and Populate the Orders Partitioned Table
USE master;
GO
IF DB_ID('testdb') IS NOT NULL
  DROP DATABASE testdb;
GO
CREATE DATABASE testdb;
GO
USE testdb;
GO

ALTER DATABASE testdb ADD FILEGROUP FG_Orders2001;
ALTER DATABASE testdb ADD FILEGROUP FG_Orders2002;
ALTER DATABASE testdb ADD FILEGROUP FG_Orders2003;
ALTER DATABASE testdb ADD FILEGROUP FG_Orders2004;

ALTER DATABASE testdb ADD FILE
(
    NAME = 'testdb_Data_Orders2001_1',
    FILENAME = 'c:\temp\testdb_Data_Orders2001_1.ndf'
) 
TO FILEGROUP FG_Orders2001;

ALTER DATABASE testdb ADD FILE
(
    NAME = 'testdb_Data_Orders2002_1',
    FILENAME = 'c:\temp\testdb_Data_Orders2002_1.ndf'
) 
TO FILEGROUP FG_Orders2002;

ALTER DATABASE testdb ADD FILE
(
    NAME = 'testdb_Data_Orders2003_1',
    FILENAME = 'c:\temp\testdb_Data_Orders2003_1.ndf'
) 
TO FILEGROUP FG_Orders2003;

ALTER DATABASE testdb ADD FILE
(
    NAME = 'testdb_Data_Orders2004_1',
    FILENAME = 'c:\temp\testdb_Data_Orders2004_1.ndf'
) 
TO FILEGROUP FG_Orders2004;
GO

CREATE PARTITION FUNCTION PF_RangeByYear(DATETIME)
AS RANGE RIGHT 
FOR VALUES ('20020101', '20030101', '20040101');

CREATE PARTITION SCHEME PS_Orders
AS PARTITION PF_RangeByYear
TO (FG_Orders2001, FG_Orders2002, FG_Orders2003, FG_Orders2004);

CREATE TABLE Orders
(
  orderid    INT      NOT NULL,
  customerid INT      NOT NULL,
  orderdate  DATETIME NOT NULL,
  contactid  INT      NOT NULL,
  /* ...  other columns ...  */
) ON PS_Orders(orderdate);

INSERT INTO Orders(orderid, customerid, orderdate, contactid)
  SELECT SalesOrderID, CustomerID, OrderDate, ContactID
  FROM AdventureWorks.Sales.SalesOrderHeader
  WHERE OrderDate >= '20010101'
    AND OrderDate < '20040101'

ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY(orderdate, orderid)
  ON PS_Orders(orderdate);
CREATE UNIQUE INDEX idx_nc_orderid ON Orders(orderid, orderdate) ON PS_Orders(orderdate);
CREATE INDEX idx_nc_customerid ON Orders(customerid, orderdate) ON PS_Orders(orderdate);