sql server分區(qū)一般情況下我們建立數(shù)據(jù)庫(kù)表時(shí)表都存放在一個(gè)文件里

SQLServerselect區(qū)會(huì)對(duì)數(shù)據(jù)庫(kù)產(chǎn)生不必要的開銷，除啦性能還會(huì)增加實(shí)現(xiàn)對(duì)象的用和復(fù)雜性。alterdatabase<數(shù)據(jù)庫(kù)名>addfilegroup<文件組名 ---創(chuàng)建數(shù)據(jù)庫(kù)文件組---創(chuàng)建數(shù)據(jù)庫(kù)文件組alterdatabasetestSplitaddfilegroupByIdGroup1alterdatabasetestSplitaddfilegroupByIdGroup2alterdatabasetestSplitaddfilegroupByIdGroup3alterdatabasetestSplitaddfilegroupByIdGroup4altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6alterdatabasetestSplitaddfilegroupByIdGroup7alterdatabasetestSplitaddfilegroupByIdGroup8alterdatabasetestSplitaddfilegroupByIdGroup9alterdatabasetestSplitaddfilegroupalterdatabasealterdatabase數(shù)據(jù)庫(kù)名稱addfile數(shù)據(jù)標(biāo)識(shí)tofilegroup文件組名稱--<數(shù)據(jù)標(biāo)識(shí)（name:文件名，fliename:物理路徑文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自動(dòng)增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小alterdatabasetestSplitadd\data\ById1.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup1alterdatabasetestSplitaddfile\data\ById2.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup2alterdatabasetestSplitaddfile\data\ById3.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup3alterdatabasetestSplitaddfile\data\ById4.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup4alterdatabasetestSplitaddfile\data\ById5.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup5alterdatabasetestSplitaddfile\data\ById6.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup6alterdatabasetestSplitaddfile\data\ById7.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup7alterdatabasetestSplitaddfile\data\ById8.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup8alterdatabasetestSplitaddfile\data\ById9.ndf',size=5Mb,filegrowth=5mb)tofilegroupByIdGroup9alterdatabasetestSplitaddfile\data\ById10.ndf',size=5Mb,filegrowth=5mb)tofilegroup右鍵到要分區(qū)的表--->>--->>創(chuàng)建分區(qū)--->>顯示向?qū)б晥D--->>下一步--->>下一步。。100W--200Wdatatime2014-01-01--2014-01-31一個(gè)分區(qū)，2014-02-01--2014-02-28區(qū)下來(lái)。orderidint下一步USE[testSplit]GOBEGIN--創(chuàng)建分區(qū)函數(shù)CREATEPARTITIONFUNCTIONbgPartitionFun](intASRANGELEFT ',',',',',',',',----創(chuàng)建分區(qū)方案CREATEPARTITIONSCHEME[bgPartitionSchema]AS[bgPartitionFun]TO([PRIMARY],[ByIdGroup1],[ByIdGroup2],[ByIdGroup3],[ByIdGroup4],[ByIdGroup5],[ByIdGroup6],[ByIdGroup7],[ByIdGroup8],[ByIdGroup9],--創(chuàng)建分區(qū)索引CREATECLUSTERED]ON()WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,=OFF)ON--刪除分區(qū)索引DROPWITH(ONLINE=OFF]ONCOMMIT：createcreatepartitionfunction分區(qū)函數(shù)名(<分區(qū)列類型>)asrange[left/right]forvalues --創(chuàng)建分區(qū)函數(shù)CREATEPARTITIONFUNCTIONbgPartitionFun](intASRANGELEFT ',',',',',',',',----刪除分區(qū)語(yǔ)法droppartitionfunction分區(qū)函數(shù)名--bgPartitionFundroppartitionfunction--創(chuàng)建分區(qū)方案語(yǔ)法--創(chuàng)建分區(qū)方案語(yǔ)法createpartitionscheme<分區(qū)方案名稱>aspartition<分區(qū)函數(shù)名稱>[all]to(文件組名稱, --創(chuàng)建分區(qū)方案,所有分區(qū)在一個(gè)組里面CREATEPARTITIONSCHEMEbgPartitionSchemaASPARTITION[bgPartitionFun]TO([ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],----刪除分區(qū)方案語(yǔ)法droppartitionscheme<分區(qū)方案名稱--bgPartitionSchemadroppartitionscheme--創(chuàng)建分區(qū)表語(yǔ)法create--創(chuàng)建分區(qū)表語(yǔ)法createtable表名--創(chuàng)建分區(qū)表createtableBigOrdernotnotnulldefaultnotnulldefaultnotnulldefault)on----創(chuàng)建分區(qū)索引語(yǔ)法create索引分類index索引名稱on表名>(列名)on分區(qū)方案名>(分----創(chuàng)建分區(qū)索引CREATECLUSTERED]ON()WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,=OFF)ON的數(shù)據(jù)在哪個(gè)分區(qū)上 )--22--查看分區(qū)表中，每個(gè)非空分區(qū)存在的行數(shù)--查看分區(qū)表中，每個(gè)非空分區(qū)存在的行數(shù)select$partition.bgPartitionFun(orderidaspartitionNum,count(*)asrecordCountfrombigordergroupby------查看指定分區(qū)中的數(shù)據(jù)記錄select*frombigorder結(jié)果：數(shù)據(jù) 開始到200W結(jié)----分區(qū)拆分alterpartitionfunctionsplit')--2----合并分區(qū)alterpartitionfunctionmerge')------創(chuàng)建表createtable表名)on文件組名從分區(qū)表中數(shù)據(jù)到普通----將bigorder分區(qū)表中的第一分區(qū)數(shù) 到普通表中altertablebigorderpartition1to普通表名 到bigorder分區(qū)表中的第一分區(qū)altertable<普通表名>switchtobigorderpartition10--100W,第二表為101萬(wàn)到200 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