sql基礎(chǔ)server2005表分區(qū)三步曲

1、SQLServer2005 表分區(qū)三步曲2009-10-2610:545951人閱讀評(píng)論(11sqlSQLServer2005 表分區(qū)三步曲2009-10-2610:545951人閱讀評(píng)論(11sqlserverscheme數(shù)據(jù)庫:SQLServer2005 通過分別檢查同一條返回所有行的、簡單SELECT語句在分區(qū)表和非分區(qū)表上的執(zhí)行計(jì)劃，返回的數(shù)據(jù)范圍通過WHERE語句來指定。同一條語句在這兩個(gè)不同的表上有不同的執(zhí)行計(jì)劃。 分區(qū)請(qǐng)三 3此分區(qū)函數(shù)用于定義你希望SQLServer3此分區(qū)函數(shù)用于定義你希望SQLServer如何對(duì)數(shù)據(jù)進(jìn)行分區(qū)的參數(shù)值(how)。這個(gè)操作并ifobject_id

2、(tb_partition1)isnotnulldrop ifobject_id(tb_partition2)isnotnulldrop Ifexists(Select1 dropifexists(select1 dropfromschemefromsys.partition_functionswherefunctioncreatepartitionfunctionmy_Pfun(datetime) as range leftforvalues(2007-12-createpartitionschememy_psch as partition my_pfun 2，分區(qū)函數(shù)提供的值的數(shù)目n，不能

3、超過 999。所創(chuàng)建的分區(qū)數(shù)等于 ncreatetabletb_partition1 (id int identity,dtnameasName+ltrim(ID), constraint)onmy_psch(dt) createtabletb_partition2 (id int identity,dtnameasName+ltrim(ID), constraint)ondeclare dt datetime set dt=2007-01-01 whiledt=2009-10-01inserttb_partition1selectdt set dt=dateadd(dd,1,dt)-查詢表的分區(qū)狀況 Select*fromsys.partitions Where object_id In(Selectobject_idFromsys.tablesWherenameselect *,$partition.my_pfun(select *,$partition.my_pfun(dt)fromselect*fromtb_partition1 select*from切換分區(qū) AlterTablePartition2To2fromfrom修改分區(qū)-1-2修改分區(qū)-3Alter PartitionFunctionMy_pfun() Alter Partition Scheme my_