1000万条表的存储和优化(思路)

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