--SQL Server 强大的分区技术(使用语句检测和优化数据库 （MSSQL个人笔记之数据库优化之路 三） 
  
/********************************************************************************  
 *主题：SQL Server 强大的分区技术   
 *说明：本文是个人学习的一些笔记和个人愚见  
 *      有很多地方你可能觉得有异议，欢迎一起讨论  
  
 *作者：Stephenzhou(阿蒙)  
 *日期: 2012.07.26  
 *Mail:szstephenzhou@163.com  
 *另外：转载请著名出处。  
**********************************************************************************/ 


/*
昨天已经在900W的测试数据上做了分区 并且查询出分区的内容
说到优化肯定是要设计到索引了，索引在第一章已经说了很多，在这里不做详解 今天就随便说下分区 索引和执行计划如果事件允许
的话我想再把追踪一起结合放到这个案例上来说说，以完成自己对MSSQL的资料的整理和感悟吧。
*/

--继续上昨天的测试数据表如下：
use Erp_System
go

if OBJECT_ID('consume') is not null
drop table consume
go
create table consume (
id varchar(50)    ,
 Shopid int,
 GoodsId int, 
 Amount float,
 ConsumeDate datetime, 
 mark nvarchar(100)
 constraint [pk_cludered_id_date] primary key clustered (id asc,ConsumeDate asc,mark)
 )
 
 
declare @myid uniqueidentifier  
declare @i int
set  @i=0;
declare @id varchar(50);
begin 
 while @i<3000000
 begin 
 set @myid=cast(newid() as nvarchar(100));
set @id='10010xxxxxxx1'
insert into consume  values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid);
 set @i=@i+1
 end; 
 
 
set @i=0
 while @i<3000000
 begin 
set @id='10010xxxxxxx2'
set @myid=cast(newid() as nvarchar(100));
insert into consume  values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid);
 set @i=@i+1
 end;
 
  set @i=0
 while @i<3000000
 begin 
set @id='10010xxxxxxx3'
set @myid=cast(newid() as nvarchar(100));
insert into consume  values(@id,cast(rand()*10 as int),cast(rand()*50 as int),cast(rand()*1000 as int) ,GETDATE(),@myid);
 set @i=@i+1
 end;
 end;
 
 --以上在表中插入随机数据和日期一共900w条数据 方便大家做海量数据的测试，我的笔记本估计跑的很慢 所以要边做别写这个案例
 --时间有点稍长了点。 呵呵 继续
 ---有了数据后我在后面的写的时候就不做过多的解释 ,如果有童鞋不明白的地方可以翻看之前笔记二和笔记一
 
 use Erp_System
 go
alter database Erp_System add filegroup  GF_System01
alter database Erp_System add filegroup  GF_System02
alter database Erp_System add filegroup  GF_System03
alter database Erp_System add filegroup  GF_System04
 go
 alter database Erp_System add file 
 (
 name =N'System_01_02', filename='E:\GroupFileData\System_01_02.ndf',
 size =5mb,maxsize=2gb ,filegrowth=5mb
 ) to filegroup GF_System01 
 
  alter database Erp_System add file 
 (
 name =N'System_03_05', filename='E:\GroupFileData\System_03_05.ndf',
 size =5mb,maxsize=2gb ,filegrowth=5mb
 ) to filegroup GF_System02 
 
 
  alter database Erp_System add file 
 (
 name =N'System_06_08', filename='E:\GroupFileData\System_06_08.ndf',
 size =5mb,maxsize=2gb ,filegrowth=5mb
 ) to filegroup GF_System03 
 
  alter database Erp_System add file 
 (
 name =N'System_09_10', filename='E:\GroupFileData\System_09_10.ndf',
 size =5mb,maxsize=2gb ,filegrowth=5mb
 ) to filegroup GF_System04 
 
-- select top 1 * from consume
 create partition function Par_Shopid_Fuc(int)
 as range right for values(2,5,8);
 
 create partition scheme Shopid_Sec
 as partition Par_Shopid_Fuc 
 to ( GF_System01,GF_System02,GF_System03,GF_System04);
 go
 
 
 
 if OBJECT_ID('consume_Shopid_Range') is not null
drop table consume_Shopid_Range
go
create table consume_Shopid_Range (
 id varchar(50),
 Shopid int,
 GoodsId int, 
 Amount float,
 ConsumeDate datetime, 
 mark nvarchar(100)
  )on Shopid_Sec(Shopid)
  
  --导数据了
  insert into consume_Shopid_Range select * from consume 
 
 --好了数据完全导入到consume_Shopid_Range表中了
 
 

 SET STATISTICS IO ON -- 显示有关由Transact-SQL 语句生成的磁盘活动量的信息

 select * from consume_Shopid_Range  where Shopid=2
 select * from consume  where Shopid=2 
 
 /*
(899690 行受影响)
表 'consume_Shopid_Range'。扫描计数 1，逻辑读取 41528 次，物理读取 1 次，预读 30970 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(899690 行受影响)
表 'consume'。扫描计数 3，逻辑读取 268352 次，物理读取 3681 次，预读 260463 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
*/

分析上面的查询消息 其中表 'consume_Shopid_Range'