CREATE SCHEMA Archive AUTHORIZATION dbo
GO
ALTER DATABASE SQL2008SBS
ADD FILEGROUP FG2
ALTER DATABASE SQL2008SBS
ADD FILEGROUP FG3
GO
ALTER DATABASE SQL2008SBS 
ADD FILE
    (NAME = FG2_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_4.ndf', SIZE = 2MB)
TO FILEGROUP FG2
ALTER DATABASE SQL2008SBS 
ADD FILE
    (NAME = FG3_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SQL2008SBS_5.ndf', SIZE = 2MB)
TO FILEGROUP FG3
GO
CREATE PARTITION FUNCTION shipdatepartfunc (date) 
AS 
RANGE RIGHT 
FOR VALUES ('1/1/2007','1/1/2008')
GO
CREATE PARTITION SCHEME shipdatepartscheme 
AS 
PARTITION shipdatepartfunc 
TO 
(FG1, FG2, FG3)
GO
--We want the primary key to be nonclustered so that
-- we can create a clustered index to partition the table
CREATE TABLE Archive.OrderDetail(
	OrderDetailID   INT         IDENTITY(1,1) NOT NULL,
	OrderID         INT         NOT NULL,
	SKU             CHAR(10)    NOT NULL,
	Quantity        INT         NOT NULL,
	UnitPrice       MONEY       NOT NULL,
	ShipDate        DATE        NOT NULL) 
ON shipdatepartscheme(ShipDate)
GO

CREATE CLUSTERED INDEX icx_shipdate
    ON Archive.OrderDetail(ShipDate)
    ON shipdatepartscheme(ShipDate)
GO
   
ALTER TABLE Archive.OrderDetail
    ADD CONSTRAINT pk_orderdetail PRIMARY KEY NONCLUSTERED (OrderDetailID, ShipDate)
ON shipdatepartscheme(ShipDate)
GO
    
CREATE TABLE Archive.OrderHeader(
	OrderID         INT     IDENTITY(1,1) NOT NULL,
	CustomerID      INT     NOT NULL,
	OrderDate       DATE    NOT NULL,
	SubTotal        MONEY   NOT NULL,
	TaxAmount       MONEY   NOT NULL,
	ShippingAmount  MONEY   NOT NULL,
	GrandTotal      AS ((SubTotal+TaxAmount)+ShippingAmount),
	FinalShipDate   DATE    NOT NULL)
ON shipdatepartscheme(FinalShipDate)
GO

ALTER TABLE Archive.OrderHeader
    ADD CONSTRAINT pk_orderheader PRIMARY KEY NONCLUSTERED(OrderID, FinalShipDate)
ON shipdatepartscheme(FinalShipDate)
GO
    
CREATE CLUSTERED INDEX icx_finalshipdate 
    ON Archive.OrderHeader(FinalShipDate)
    ON shipdatepartscheme(FinalShipDate)
GO