笔记104 创建分区表的步骤

笔记104 创建分区表的步骤

  1 --创建分区表的步骤
  2 --创建分区函数
  3 USE partionTest
  4 GO
  5 CREATE PARTITION FUNCTION PartionByInt(INT )
  6 AS RANGE LEFT FOR VALUES (100,200,300)
  7 GO
  8 --3个分区点就一定是4段分区
  9 --left：负无穷-100，101-200，201-300，301-正无穷
 10 --right：100-199，200-299，300-399，400-499
 11 
 12 --创建分区方案
 13 USE partionTest
 14 GO
 15 CREATE PARTITION SCHEME PartionByIntScheme
 16 AS PARTITION PartionByInt
 17 TO (FileGroup001,FileGroup002,FileGroup003,FileGroup004);
 18 --这里刚好是4个段，每个段一个文件组，如果加多一个文件组不知道SQL会怎麽分配这些分区到文件组
 19 
 20 
 21 --创建表
 22 USE partionTest
 23 GO
 24 CREATE TABLE testPartionTable
 25 (id INT NOT NULL ,   
 26 ItemNo CHAR( 20),
 27 ItemName CHAR( 40))
 28 ON PartionByIntScheme(ID);
 29 --id为分区列
 30 
 31 
 32 
 33 --插入数据
 34 USE partionTest
 35 GO
 36 
 37 DECLARE @count INT
 38 SET @count =-25
 39 WHILE @count <=100
 40 BEGIN
 41 INSERT INTO dbo.testPartionTable
 42 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>0 and <100'
 43 SET @count =@count +1
 44 END
 45 
 46 SET @count =101
 47 WHILE @count <=200
 48 BEGIN
 49 INSERT INTO dbo.testPartionTable
 50 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>100 and <200'
 51 SET @count =@count +1
 52 END
 53 
 54 SET @count =201
 55 WHILE @count <=300
 56 BEGIN
 57 INSERT INTO dbo.testPartionTable
 58 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>200 and <300'
 59 SET @count =@count +1
 60 END
 61 
 62 SET @count =301
 63 WHILE @count <=400
 64 BEGIN
 65 INSERT INTO dbo.testPartionTable
 66 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>300 and <400'
 67 SET @count =@count +1
 68 END
 69 
 70 SET @count =401
 71 WHILE @count <=500
 72 BEGIN
 73 INSERT INTO dbo.testPartionTable
 74 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>400 and <500'
 75 SET @count =@count +1
 76 END
 77 
 78 
 79 SELECT * FROM dbo.testPartionTable
 80 GO
 81 
 82 
 83 --查看第三分区中所含记录
 84 --$PARTITION系统函数用于为指定的分区函数返回分区号例如：$PARTITION.PartionByInt(ID)=3用于返回给定ID所处的分区号的分区数据
 85 USE partionTest
 86 GO
 87 SELECT * FROM dbo.testPartionTable
 88 WHERE $PARTITION.PartionByInt(ID)=3
 89 GO
 90 
 91 
 92 ----------------------------邹建大侠的分区表方案 计算列作为分区列-----------------------------------------
 93 
 94 USE pratice
 95 GO
 96 -- 分区函数和架构
 97 CREATE PARTITION FUNCTION PF_Year(DATETIME)
 98 AS
 99 RANGE LEFT FOR VALUES (
100      N'2010', N'2011', N'2012'
101 );
102 --负无穷-2010，2011-2011，2012-2012，2012-正无穷
103 
104 CREATE PARTITION SCHEME PS_Year
105 AS
106 PARTITION PF_Year TO (
107      [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]
108 )
109 GO
110 
111 -- 创建表和计算列，并将计算列用做分区列
112 CREATE TABLE dbo.tb_test(
113      date datetime,
114      date_year as YEAR(date)  PERSISTED      -- PERSISTED 选项用于持久化存储
115 )ON PS_Year(date_year);
116 --YEAR(date) 里的date是第一列 date
117 
118 CREATE CLUSTERED INDEX IXC_date_year ON dbo.tb_test(date_year)
119 --加聚集索引不是唯一索引，加索引只是使查询加快速度，可以不加
120 
121     
122 GO

 

posted @ 2013-08-03 22:21  桦仔  阅读(631)  评论(0)    收藏  举报