sql-server-分区方案

SQLServer表分区什么是表分区一般情况下，我们建立数据库表时，表数据都存放在一个文件里。但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对数据库产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。跟着做，分区如此简单先跟着做一个分区表（分为11个分区），去除神秘的面纱，然后咱们再逐一击破各个要点要害。分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。1.创建文件组可以点击数据库属性在文件组里面添加T-sql语法：alterdatabase数据库名addfilegroup文件组名---创建数据库文件组alterdatabasetestSplitaddfilegroupByIdGroup1alterdatabasetestSplitaddfilegroupByIdGroup2alterdatabasetestSplitaddfilegroupByIdGroup3alterdatabasetestSplitaddfilegroupByIdGroup4alterdatabasetestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6alterdatabasetestSplitaddfilegroupByIdGroup7alterdatabasetestSplitaddfilegroupByIdGroup8alterdatabasetestSplitaddfilegroupByIdGroup9alterdatabasetestSplitaddfilegroupByIdGroup102.创建数据文件到文件组里面可以点击数据库属性在文件里面添加T-sql语法：alterdatabase数据库名称addfile数据标识tofilegroup文件组名称--数据标识（name:文件名，fliename:物理路径文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited）alterdatabasetestSplitaddfile(name=N'ById1',filename=N'J:\Work\数据库\data\ById1.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup1alterdatabasetestSplitaddfile(name=N'ById2',filename=N'J:\Work\数据库\data\ById2.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup2alterdatabasetestSplitaddfile(name=N'ById3',filename=N'J:\Work\数据库\data\ById3.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup3alterdatabasetestSplitaddfile(name=N'ById4',filename=N'J:\Work\数据库\data\ById4.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup4alterdatabasetestSplitaddfile(name=N'ById5',filename=N'J:\Work\数据库\data\ById5.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup5alterdatabasetestSplitaddfile(name=N'ById6',filename=N'J:\Work\数据库\data\ById6.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup6alterdatabasetestSplitaddfile(name=N'ById7',filename=N'J:\Work\数据库\data\ById7.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup7alterdatabasetestSplitaddfile(name=N'ById8',filename=N'J:\Work\数据库\data\ById8.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup8alterdatabasetestSplitaddfile(name=N'ById9',filename=N'J:\Work\数据库\data\ById9.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup9alterdatabasetestSplitaddfile(name=N'ById10',filename=N'J:\Work\数据库\data\ById10.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup10执行完成后，右键数据库看文件组跟文件里面是不是多出来啦这些文件组跟文件。3.使用向导创建分区表右键到要分区的表---存储---创建分区---显示向导视图---下一步---下一步。。这里举例说下选择列的意思：假如你选择的是int类型的列：那么你的分区可以指定为1--100W是一个分区，100W--200W是一个分区....假如你选择的是datatime类型：那么你的分区可以指定为：2014-01-01--2014-01-31一个分区，2014-02-01--2014-02-28一个分区...根据这样的列数据规则划分，那么在那个区间的数据，在插入数据库时就被指向那个分区存储下来。我这里选用orderidint类型---下一步---左边界右边界：就是把临界值划分给上一个分区还是下一个分区。一个小于号，一个小于等于号。然后下一步下一步最后你会得到分区函数和分区方案。USE[testSplit]GOBEGINTRANSACTION--创建分区函数CREATEPARTITIONFUNCTION[bgPartitionFun](int)ASRANGELEFTFORVALUES(N'1000000',N'2000000',N'3000000',N'4000000',N'5000000',N'6000000',N'7000000',N'8000000',N'9000000',N'10000000')--创建分区方案CREATEPARTITIONSCHEME[bgPartitionSchema]ASPARTITION[bgPartitionFun]TO([PRIMARY],[ByIdGroup1],[ByIdGroup2],[ByIdGroup3],[ByIdGroup4],[ByIdGroup5],[ByIdGroup6],[ByIdGroup7],[ByIdGroup8],[ByIdGroup9],[ByIdGroup10])--创建分区索引CREATECLUSTEREDINDEX[ClusteredIndex_on_bgPartitionSchema_635342971076448165]ON[dbo].[BigOrder]([OrderId])WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ON[bgPartitionSchema]([OrderId])--删除分区索引DROPINDEX[ClusteredIndex_on_bgPartitionSchema_635342971076448165]ON[dbo].[BigOrder]WITH(ONLINE=OFF)COMMITTRANSACTION执行上面向导生成的语句。分区完成。。4.秀一下速度。首先我在表中插入啦1千万行数据。给表分啦11个分区。前十个分区里面一个是100W条数据。。说两句：可见反常现象，扫描次数跟逻辑读取次数都是无分区表的2倍之多，但查询速度却是快啦不少啊。这就是分区的神奇之处啊，所以要相信这世界一切皆有可能。分区函数，分区方案，分区表，分区索引1.分区函数指定分依据区列（依据列唯一），分区数据范围规则，分区数量，然后将数据映射到一组分区上。创建语法：createpartitionfunction分区函数名(分区列类型)asrange[left/right]forvalues(每个分区的边界值,....)--创建分区函数CREATEPARTITIONFUNCTION[bgPartitionFun](int)ASRANGELEFTFORVALUES(N'1000000',N'2000000',N'3000000',N'4000000',N'5000000',N'6000000',N'7000000',N'8000000',N'9000000',N'10000000')然而，分区函数只定义了分区的方法，此方法具体用在哪个表的那一列上，则需要在创建表或索引是指定。删除语法：--删除分区语法droppartitionfunction分区函数名--删除分区函数bgPartitionFundroppartitionfunctionbgPartitionFun需要注意的是，只有没有应用到分区方案中的分区函数才能被删除。2.分区方案指定分区对应的文件组。创建语法：--创建分区方案语法createpartitionscheme分区方案名称aspartition分区函数名称[all]to(文件组名称,....)--创建分区方案,所有分区在一个组里面CREATEPARTITIONSCHEME[bgPartitionSchema]ASPARTITION[bgPartitionFun]TO([ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1])分区函数必须关联分区方案才能有效，然而分区方案指定的文件组数量必须与分区数量一致，哪怕多个分区存放在一个文件组中。删除语法：--删除分区方案语法droppartitionscheme分区方案名称--删除分区方案bgPartitionSchemadroppartitionschemebgPartitionSchema1只有没有分区表，或索引使用该分区方案是，才能对其删除。3.分区表创建语法：--创建分区表语法createtable表名(列定义)on分区方案名(分区列名)--创建分区表createtableBigOrder(OrderIdintidentity,orderNumvarchar(30)notnull,OrderStatusintnotnulldefault0,Or