对有数据的表进行分区

1.创建新的文件组ALTER DATABASE listest ADD FILEGROUP [lisfq]

 ALTER DATABASE TIOS ADD FILEGROUP [lisfq]

2 创建分区函数

CREATE PARTITION FUNCTION MonthDateRange(datetime)

    AS RANGE LEFT FOR VALUES

      ( '20111231 23:59:59.997','20131231 23:59:59.997','20151231 23:59:59.997')

--创建文件  ALTER DATABASE TIOS

ADD FILE

(NAME = N'lis201112',FILENAME = N'e:/data/lis201112.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

ALTER DATABASE TIOS

ADD FILE

(NAME = N'lis201312',FILENAME = N'e:/data/lis201312.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

ALTER DATABASE TIOS

ADD FILE

(NAME = N'lis201512',FILENAME = N'e:/data/lis201512.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

 --如果语句不能执行就直接改配置

 --创建分区框架，把函数运用到框架上面  CREATE PARTITION SCHEME MonthDateRangeScheme

AS

PARTITION MonthDateRange

ALL TO ([lisfq])

 

---有索引需要先删除索引再创建

SELECT * FROM sys.sysindexes WHERE id=object_id('FengQutest')

drop index FengQutest.PK_FengQutest

create  index IDX_L_TESTRESULT_MEASURETIME

on FengQutest(CreateOn)

on MonthDateRangeScheme(CreateOn)

--6、对于分区表中存在聚集索引，最好将其删除重建：

--重建聚聚集索引主建

ALTER TABLE FengQutest

    DROP CONSTRAINT PK_L_testresult

ALTER TABLE FengQutest

    ADD CONSTRAINT PK_l_testresult

       PRIMARY KEY CLUSTERED(sampleno,testid,sampletype,editstatus)

    --查询分区是否成功     SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('FengQutest')     --查询某段时间的数据位于那个分区     select * ,$PARTITION.MonthDateRange(CreateOn) AS 分区 from FengQutest where CreateOn>='2011-01-01'

    SELECT * FROM FengQutest

    dbcc showcontig('FengQutest')

 

    -----查询索引碎片-----     SELECT * FROM sys.sysindexes          WHERE id=object_id('[dbo].[ZWOIMG]')     dbcc showcontig('[dbo].[ZWOIMG]')     --可以重组和重建

    DBCC DBREINDEX('HXQCMC2.dbo.ZWOIMG',' ',90) --参数对应数据库表，索引名称 '' 默认为该表的所有索引