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

SQLServerselect區(qū)會對數據庫產生不必要的開銷，除啦性能還會增加實現對象的用和復雜性。alterdatabase<數據庫名>addfilegroup<文件組名 ---創(chuàng)建數據庫文件組---創(chuàng)建數據庫文件組alterdatabasetestSplitaddfilegroupByIdGroup1alterdatabasetestSplitaddfilegroupByIdGroup2alterdatabasetestSplitaddfilegroupByIdGroup3alterdatabasetestSplitaddfilegroupByIdGroup4altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6altertestSplitaddfilegroupByIdGroup5alterdatabasetestSplitaddfilegroupByIdGroup6alterdatabasetestSplitaddfilegroupByIdGroup7alterdatabasetestSplitaddfilegroupByIdGroup8alterdatabasetestSplitaddfilegroupByIdGroup9alterdatabasetestSplitaddfilegroupalterdatabasealterdatabase數據庫名稱addfile數據標識tofilegroup文件組名稱--<數據標識（name:文件名，fliename:物理路徑文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自動增量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ū)--->>顯示向導視圖--->>下一步--->>下一步。。100W--200Wdatatime2014-01-01--2014-01-31一個分區(qū)，2014-02-01--2014-02-28區(qū)下來。orderidint下一步USE[testSplit]GOBEGIN--創(chuàng)建分區(qū)函數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ū)函數名(<分區(qū)列類型>)asrange[left/right]forvalues --創(chuàng)建分區(qū)函數CREATEPARTITIONFUNCTIONbgPartitionFun](intASRANGELEFT ',',',',',',',',----刪除分區(qū)語法droppartitionfunction分區(qū)函數名--bgPartitionFundroppartitionfunction--創(chuàng)建分區(qū)方案語法--創(chuàng)建分區(qū)方案語法createpartitionscheme<分區(qū)方案名稱>aspartition<分區(qū)函數名稱>[all]to(文件組名稱, --創(chuàng)建分區(qū)方案,所有分區(qū)在一個組里面CREATEPARTITIONSCHEMEbgPartitionSchemaASPARTITION[bgPartitionFun]TO([ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],[ByIdGroup1],----刪除分區(qū)方案語法droppartitionscheme<分區(qū)方案名稱--bgPartitionSchemadroppartitionscheme--創(chuàng)建分區(qū)表語法create--創(chuàng)建分區(qū)表語法createtable表名--創(chuàng)建分區(qū)表createtableBigOrdernotnotnulldefaultnotnulldefaultnotnulldefault)on----創(chuàng)建分區(qū)索引語法create索引分類index索引名稱on表名>(列名)on分區(qū)方案名>(分----創(chuàng)建分區(qū)索引CREATECLUSTERED]ON()WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,DROP_EXISTING=OFF,=OFF)ON的數據在哪個分區(qū)上 )--22--查看分區(qū)表中，每個非空分區(qū)存在的行數--查看分區(qū)表中，每個非空分區(qū)存在的行數select$partition.bgPartitionFun(orderidaspartitionNum,count(*)asrecordCountfrombigordergroupby------查看指定分區(qū)中的數據記錄select*frombigorder結果：數據 開始到200W結----分區(qū)拆分alterpartitionfunctionsplit')--2----合并分區(qū)alterpartitionfunctionmerge')------創(chuàng)建表createtable表名)on文件組名從分區(qū)表中數據到普通----將bigorder分區(qū)表中的第一分區(qū)數 到普通表中altertablebigorderpartition1to普通表名 到bigorder分區(qū)表中的第一分區(qū)altertable<普通表名>switchtobigorderpartition10--100W,第二表為101萬到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