一般情況下我們建立數(shù)據(jù)庫表時表都存放在一個文件里

SQLServer區(qū)會對數(shù)據(jù)庫產(chǎn)生不必要的開銷，除啦性能還會增加實現(xiàn)對象的用和復雜性。alterdatabase<數(shù)據(jù)庫名>addfilegroup<文件組名 testSplitaddfilegroupByIdGroup2alterdatabasetestSplitaddfilegroupByIdGroup3alterdatabasetestSplitaddfilegroupByIdGroup4altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6alterdatabasetestSplitaddfilegroupByIdGroup7alterdatabasetestSplitaddfilegroupByIdGroup8alterdatabasetestSplitaddfilegroupByIdGroup9alterdatabasetestSplitaddfilegroupalterdatabasealterdatabase數(shù)據(jù)庫名稱addfile數(shù)據(jù)標識tofilegroup文件組名稱--<數(shù)據(jù)標識（name:文件名，fliename:物理路徑文件名，size:kb/mb/gb/tb，filegrowth:kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小alterdatabasetestSplitaddtestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfiletestSplitaddfile\data\ById10.ndf',size=5Mb,filegrowth=5mb)tofilegroup右鍵到要分區(qū)的表--->>--->>創(chuàng)建分區(qū)--->>顯示向?qū)б晥D--->>下一步--->>下一步。。一個分區(qū)，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分區(qū)函數(shù)名(<分區(qū)列類型>)asrange[left/right]forvalues --CREATEPARTITIONFUNCTION[bgPartitionFun](int)ASRANGELEFT ',',',',',',----droppartitionfunction分區(qū)函數(shù)名--bgPartitionFundroppartitionfunction--創(chuàng)建分區(qū)方案語法--創(chuàng)建分區(qū)方案語法createpartitionscheme<分區(qū)方案名稱>aspartition<分區(qū)函數(shù)名稱>[all]to(文件組名稱,. --創(chuàng)建分區(qū)方案,CREATEPARTITIONSCHEME[bgPartitionSchemaASPARTITION[bgPartitionFun]TO([ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],----droppartitionscheme<分區(qū)方案名稱--bgPartitionSchemadroppartitionscheme--create--createtable表名--createtableBigOrdernotnotnulldefaultnotnulldefaultnotnulldefault)on----create索引分類index索引名稱on表名>(列名)on分區(qū)方案名>(分----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分區(qū)表中的第一分區(qū)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