quote:

---

Originally posted by WoodHouse
That is the rule of partition, ie partition column must be part of the primary key

---

USE master
GO

CREATE DATABASE PartitionTest
ON PRIMARY (
	NAME = db_dat, FILENAME = 'D:\test\db.mdf', SIZE = 2MB),
FILEGROUP FG1 (
	NAME = FG1_dat, FILENAME = 'D:\test\FG1.ndf', SIZE = 2MB),
FILEGROUP FG2 (
	NAME = FG2_dat, FILENAME = 'D:\test\FG2.ndf', SIZE = 2MB),
FILEGROUP FG3 (
	NAME = FG3_dat, FILENAME = 'D:\test\FG3.ndf', SIZE = 2MB),
FILEGROUP FG4 (
	NAME = FG4_dat, FILENAME = 'D:\test\FG4.ndf', SIZE = 2MB)
LOG ON (
	NAME = db_log, FILENAME = 'D:\test\db_log.ldf', SIZE = 2MB)
GO

USE PartitionTest
GO

CREATE PARTITION FUNCTION partfunc(DATETIME) AS
RANGE LEFT FOR VALUES ('20100101', '20100201')
GO

CREATE PARTITION SCHEME partscheme AS 
PARTITION partfunc TO
([FG1], [FG2], [FG3])
GO

CREATE TABLE Widgets (
	WidgetID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED ON [FG4],
	WidgetName VARCHAR(100) NOT NULL,
	DateAdded DATETIME NOT NULL
) ON partscheme(DateAdded)
GO

INSERT INTO Widgets (WidgetName, DateAdded)
SELECT 'Widget 1', '20091001' UNION ALL 
SELECT 'Widget 2', '20091001' UNION ALL 
SELECT 'Widget 3', '20100101' UNION ALL 
SELECT 'Widget 4', '20100103' UNION ALL 
SELECT 'Widget 5', '20100112' UNION ALL 
SELECT 'Widget 6', '20100201' UNION ALL 
SELECT 'Widget 7', '20100202' UNION ALL 
SELECT 'Widget 8', '20100208' 
GO

SELECT * FROM Widgets 

/*

USE master 
GO
DROP DATABASE PartitionTest

*/