一般情况下我们建立数据库表时表都存放在一个文件里

SQLServer区会对数据库产生不必要的开销，除啦性能还会增加实现对象的用和复杂性。alterdatabase<数据库名>addfilegroup<文件组名 testSplitaddfilegroupByIdGroup2alterdatabasetestSplitaddfilegroupByIdGroup3alterdatabasetestSplitaddfilegroupByIdGroup4altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6alterdatabasetestSplitaddfilegroupByIdGroup7alterdatabasetestSplitaddfilegroupByIdGroup8alterdatabasetestSplitaddfilegroupByIdGroup9alterdatabasetestSplitaddfilegroupalterdatabasealterdatabase数据库名称addfile数据标识tofilegroup文件组名称--<数据标识（name:文件名，fliename:物理路径文件名，size:kb/mb/gb/tb，filegrowth:kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小alterdatabasetestSplitaddtestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfile\data\ById10.ndf',size=5Mb,filegrowth=5mb)tofilegroup右键到要分区的表--->>--->>创建分区--->>显示向导视图--->>下一步--->>下一步。。一个分区，2014-02-01--2014-02-28 orderidint下一步USE[testSplit]GOBEGIN--CREATEPARTITIONFUNCTION[bgPartitionFun](int)ASRANGELEFT ',',',',',',----CREATEPARTITIONSCHEME[bgPartitionSchemaAS[bgPartitionFun]TO([PRIMARY],[ByIdGroup1],[ByIdGroup2],[ByIdGroup3],[ByIdGroup4],[ByIdGroup5],[ByIdGroup6],[ByIdGroup7],[ByIdGroup8],[ByIdGroup9],--CREATECLUSTERED]ON()WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,=OFF)ONWITH(ONLINE=OFF]ONCOMMIT：createcreatepartitionfunction分区函数名(<分区列类型>)asrange[left/right]forvalues --CREATEPARTITIONFUNCTION[bgPartitionFun](int)ASRANGELEFT ',',',',',',----droppartitionfunction分区函数名--bgPartitionFundroppartitionfunction--创建分区方案语法--创建分区方案语法createpartitionscheme<分区方案名称>aspartition<分区函数名称>[all]to(文件组名称,. --创建分区方案,CREATEPARTITIONSCHEME[bgPartitionSchemaASPARTITION[bgPartitionFun]TO([ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],----droppartitionscheme<分区方案名称--bgPartitionSchemadroppartitionscheme--create--createtable表名--createtableBigOrdernotnotnulldefaultnotnulldefaultnotnulldefault)on----create索引分类index索引名称on表名>(列名)on分区方案名>(分----CREATECLUSTERED]ON()WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,=OFF)ON )--22----select$partition.bgPartitionFun(orderidaspartitionNum,count(*)asrecordCountfrombigordergroupby------select*frombigorder ----alterpartitionfunctionsplit')--2----alterpartitionfunctionmerge')------createtable表名)on文件组名 到普通表中altertablebigorderpartition1to普通表名 到bigorder分区表中的第一分区altertable<普通表名>switchtobigorderpartition1 SELECTSELECTOBJECT_NAME(p.object_id)AS AS AS AS AS AS AS ASCASEWHEN1THENELSE'LEFT' ASp.rowsASRowsFROM ASpJOINASONi.object_id=ANDi.index_id=p.index_idJOIN AS ONps.data_space_id=ds.data_space_idJOINsys.partition_functionsASpfONpf.function_id=ps.function_idJOIN ONdds2.partition_scheme_id=ANDdds2.destination_id=p.partition_numberJOINsys.filegroupsASfgONfg.data_space_id=dds2.data_space_idLEFTJOINsys.partition_range_valuesASprv_leftONps.function_id=ANDprv_left.boundary_id=p.partition_number-1LEFTJOINsys.partition_range_valuesASprv_rightONps.function_id=ANDprv_right.boundary_id=p.partition_numberOBJECTPROPERTY(p.object_id,'ISMSShipped')=0UNION AS AS AS AS AS AS AS AS AS ASRowsFROMsys.partit