Create PARTITION FUNCTION F_PARTITION (datetime) AS RANGE RIGHT FOR VALUES ('2008-01-01', '2008-04-02', '2008-07-03', '2008-10-03', '2009-01-03')go Create PARTITION SCHEME S_PARTITION as PARTITION F_PARTITION ALL to ([PRIMARY])go CREATE TABLE dbo.Partition_Table( ID int identity(1,1), Date datetime not null ) ON S_PARTITION (Data) go ---插入数据 insert Partition_Table(Date) select '2008-01-01' union all select '2008-04-02' union all select '2008-07-03' union all select '2008-10-03' union all select '2009-01-03'------------查看分区表存储: select convert(varchar(16), ps.name) as partition_scheme, p.partition_number, convert(varchar(10), ds2.name) as filegroup, convert(varchar(19), isnull(v.value, ‘’), 120) as range_boundary, str(p.rows, 9) as rows from sys.indexes i join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
AS RANGE RIGHT FOR VALUES
('2008-01-01', '2008-04-02', '2008-07-03',
'2008-10-03', '2009-01-03')go
Create PARTITION SCHEME S_PARTITION
as PARTITION F_PARTITION
ALL to ([PRIMARY])go
CREATE TABLE dbo.Partition_Table(
ID int identity(1,1),
Date datetime not null
) ON S_PARTITION (Data)
go
---插入数据
insert Partition_Table(Date)
select '2008-01-01' union all
select '2008-04-02' union all
select '2008-07-03' union all
select '2008-10-03' union all
select '2009-01-03'------------查看分区表存储:
select
convert(varchar(16), ps.name) as partition_scheme,
p.partition_number,
convert(varchar(10), ds2.name) as filegroup,
convert(varchar(19), isnull(v.value, ‘’), 120) as range_boundary,
str(p.rows, 9) as rows
from
sys.indexes i
join
sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join
sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
join
sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id