-- 1 创建数据库
create database bigData_test
-- 首先添加四个文件组到数据库
alter database bigdata_test
add filegroup fg1
alter database bigdata_test
add filegroup fg2
alter database bigdata_test
add filegroup fg3
alter database bigdata_test
add filegroup fg4
-- 添加四个文件分别到四个文件组;指定的文件夹需要先创建
alter database bigdata_test
add file (name =N'file1',filename =N'c:\bigdata\file1.ndf',size =2048kb,filegrowth =1024kb)
to filegroup [fg1]
alter database bigdata_test
add file (name =N'file2',filename =N'c:\bigdata\file2.ndf',size =2048kb,filegrowth =1024kb)
to filegroup [fg2]
alter database bigdata_test
add file (name =N'file3',filename =N'e:\bigdata\file3.ndf',size =2048kb,filegrowth =1024kb)
to filegroup [fg3]
alter database bigdata_test
add file (name =N'file4',filename =N'g:\bigdata\file4.ndf',size =2048kb,filegrowth =1024kb)
to filegroup [fg4]
use master ; -- 下面时删除分区函数
drop partition function timefun
--删除分区方案
drop partition scheme timesc
use bigdata_test ;
-- 查看分区表信息 系统运行一段时间或者把以前的数据导入分区表后，
-- 我们需要查看数据的具体存储情况，即每个分区存取的记录数，那些记录存取
--在那个分区等。我们可以通过$partition.SendSMSPF来查看，代码如下：
select * from timesc
-- 查看分区信息 其中 timefun是分区函数名称
SELECT $partition.timefun(o.addtime)as [part num],min(o.addtime)as min ,
max(o.addtime) as max ,count(*) AS [Rows In Partition]
FROM dbo.tb_bigdataTest AS o GROUP BY $partition.timefun(o.addtime)
ORDER BY [part num]
-- 创建分区函数 注意这里需要在指定的数据库中创建
create partition function timeFun(datetime) -- right的含义是分界点的值在右边表中
as range right for values ('2011-01-01','2012-01-01','2013-01-01')
use bigdata_test
-- 创建分区方案到文件组
create partition scheme timeSC as partition timeFun to (fg1,fg2,fg3,fg4)
-- 创建表 并且将分区方案放到表中的字段中,注意类型要匹配;
-- 注意分区表中是以某个字段为条件进行的分区;所以不能指定聚簇索引
create table tb_bigdataTest
(
gui varchar(50) not null default newid(),
id bigint identity(1,1) ,
name varchar(50) ,
sex varchar(2) check(sex in ('男','女')),
age int ,
haveMoney decimal(12,2),
addressInfo varchar(500),
addTime datetime ,
memo varchar(50)
)
on timeSC(addtime)
alter table tb_bigdatatest
drop identity_inserted
-- 定义循环插入数据
declare @aa int =2081148;
declare @sex varchar(2) ='男';
declare @date datetime ='2010-01-01'
set identity_insert tb_bigdataTest on
while @aa<30000000
begin
insert into tb_bigdataTest
(id,name,sex,age,haveMoney,addressInfo,addtime,memo)
values (@aa,
convert(varchar(20),@aa)+'zhang',@sex,@aa/100,@aa/100,'山东济南长清双全村',@date,'')
if (@sex ='男') set @sex ='女';
set @aa=@aa+1;
set @date = case
when (@aa%5=1) then '2010-01-01'
when (@aa%5=2) then '2011-01-01'
when (@aa%5=3) then '2012-01-01'
when (@aa%5=4) then '2013-01-01'
when (@aa%5=0) then '2014-01-01'
else '2010-01-01' end
end
set identity_insert tb_bigdataTest off
SELECT COUNT(*) from tb_bigdatatest
--------- 以上便是表分区的实例；后续会把c#中大数据插入补上