HOW TO CREATE PARTITION

create database partitiontest on
primary
(
name = db_dat,
filename = 'c:\test\db.mdf',size = 2mb
)
,filegroup fg1
(
name = fg1_dat,filename = 'c:\test\fg1.ndf',size=2mb
)
,filegroup fg2
(
name = fg2_dat,filename='c:\test\fg2.ndf',size = 2mb
)
,filegroup fg3
(
name = fg3_dat,filename='c:\test\fg3.ndf',size = 2mb
)
,filegroup fg4
(
name = fg4_dat,filename='c:\test\fg4.ndf',size = 2mb
)
log on
(
name = db_log,
filename = 'c:\test\log.ndf',size = 2mb,filegrowth = 10%
);

CREATE PARTITION FUNCTION

CREATE PARTITION FUNCTION partfunc (int) AS
RANGE LEFT FOR VALUES(1000,2000)

SELECT * FROM SYS.PARTITION_RANGE_VALUES

CREATE PARTITION SCHEME Partscheme AS
PARTITION partfunc to
(
[fg1],[fg2],[fg3]
)

select * from sys.partition_schemes


CREATE PARTITION TABLE

create table t1
(
id int
,v char(1000) default 'aaaa'
,constraint ci_t1_id primary key clustered (id)
) on partscheme(id);


SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('dbo.t1')

Query partition

SELECT $partition.partfunc(id) as partitionNum,count(*) as NumRows
FROM t1 GROUP BY $Partition.partfunc(id) ORDER BY $Partition.partfunc(id)


SELECT $partition.partfunc(4000) as partitionNum

Get all records in partition 2

SELECT * FROM T1 WHERE $partition.partfunc(id) = 2