The following script demonstrates how this works, read the comments and run each statement one at a time:

USE tempdb

GO

-- Two boundaries = three ranges

CREATE PARTITION FUNCTION PF(integer) AS RANGE RIGHT

FOR VALUES (1000, 5000);

GO

-- All to [PRIMARY] just to make the test easier

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

GO

-- Test table

CREATE TABLE dbo.Partitioned

(

IntValue integer NOT NULL,

StringValue varchar(50) NOT NULL,

DateValue date NOT NULL,

CONSTRAINT [PK dbo.Partitioned IntValue]

PRIMARY KEY (IntValue)

ON PS (IntValue)

);

GO

-- Test data

INSERT dbo.Partitioned

(IntValue, StringValue, DateValue)

VALUES

(999, '100', '2012-01-01'),

(1999, '200', '2012-01-02'),

(2999, '300', '2012-01-03'),

(3999, '400', '2012-01-04'),

(4999, '500', '2012-01-05'),

(5999, '600', '2012-01-06');

GO

-- Show the two partiton boundaries and the range values

SELECT

prv.boundary_id,

prv.value

FROM sys.partition_functions AS pf

JOIN sys.partition_range_values AS prv ON

prv.function_id = pf.function_id

WHERE

pf.name = N'PF'

ORDER BY

prv.boundary_id;

-- Show the three partitons, and number of rows in each

SELECT

p.partition_number,

p.rows

FROM sys.partitions AS p

WHERE

p.object_id = OBJECT_ID(N'dbo.Partitioned')

ORDER BY

p.partition_number;

-- Show partitions and values another way

SELECT

$PARTITION.PF(p.IntValue) AS partition_id,

p.*

FROM dbo.Partitioned AS p

ORDER BY

p.IntValue;

-- Set the next file group to use for a new partition

ALTER PARTITION SCHEME PS

NEXT USED [PRIMARY];

-- Create a new partition at IntValue = 2000

ALTER PARTITION FUNCTION PF()

SPLIT RANGE (2000);

-- Notice the table has automatically been updated

-- There are now four partitions

SELECT

$PARTITION.PF(p.IntValue) AS partition_id,

*

FROM dbo.Partitioned AS p

ORDER BY

p.IntValue;

-- Perform the remaining splits

ALTER PARTITION SCHEME PS

NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION PF()

SPLIT RANGE (3000);

ALTER PARTITION SCHEME PS

NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION PF()

SPLIT RANGE (4000);

-- Six partitions

SELECT

$PARTITION.PF(p.IntValue) AS partition_id,

*

FROM dbo.Partitioned AS p

ORDER BY

p.IntValue;

-- Five partiton boundaries and ranges

SELECT

prv.boundary_id,

prv.value

FROM sys.partition_functions AS pf

JOIN sys.partition_range_values AS prv ON

prv.function_id = pf.function_id

WHERE

pf.name = N'PF'

ORDER BY

prv.boundary_id;

-- Show the six partitons, and number of rows in each

SELECT

p.partition_number,

p.rows

FROM sys.partitions AS p

WHERE p.object_id = OBJECT_ID(N'dbo.Partitioned')

ORDER BY

p.partition_number;

-- Tidy up

DROP TABLE dbo.Partitioned;

DROP PARTITION SCHEME PS;

DROP PARTITION FUNCTION PF;