重新索引分区表索引

您可以尝试的一个选项是将分区视图与表级分区相结合。通过将当前表和历史表合并在一起的分区视图，您将能够拥有不同的表选项，即填充因子等。这也可以更好地统计数据的活动部分，因为它不再与所有历史数据发生偏差数据。

USE master;
GO
DROP DATABASE PartitionDemo
GO
CREATE DATABASE PartitionDemo
GO
USE PartitionDemo;
GO
CREATE PARTITION FUNCTION everyHundredThousand(int) AS RANGE RIGHT FOR VALUES(100000,200000,300000,400000,500000,600000,700000,800000,900000,1000000)
CREATE PARTITION SCHEME allPrimary AS PARTITION everyHundredThousand ALL TO ([PRIMARY]);
GO
CREATE TABLE    dbo.HistoryTable
(
    ID  int NOT NULL /*Note not an identity*/,
    Filler char(200) NOT NULL,
    CONSTRAINT PK_HistoryTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=100) 
) ON allPrimary(ID)
GO
CREATE TABLE    dbo.CurrentTable
(
    ID  int NOT NULL IDENTITY(0,1),
    Filler char(200) NOT NULL,
    CONSTRAINT PK_CurrentTable PRIMARY KEY CLUSTERED(ID) WITH (FILLFACTOR=80) 
) ON allPrimary(ID)
GO
/* Now generate some data */
INSERT  CurrentTable
        (
            Filler
        )
SELECT      TOP 1000000
            ''
FROM        sys.objects     a
CROSS JOIN  sys.objects     b
CROSS JOIN  sys.objects     c
CROSS JOIN  sys.objects     d
GO
/* Rebuild Indexes to set Fill Factor */
ALTER INDEX ALL ON dbo.CurrentTable REBUILD
GO
/* Create Partitioned View */
CREATE VIEW CurrentPlusHistory
AS
SELECT      ID,
            Filler
FROM        HistoryTable
UNION ALL
SELECT      ID,
            Filler
FROM        CurrentTable
GO
/* Move some data to History Table */
ALTER TABLE CurrentTable SWITCH PARTITION 1 TO HistoryTable PARTITION 1
ALTER TABLE CurrentTable SWITCH PARTITION 2 TO HistoryTable PARTITION 2
ALTER TABLE CurrentTable SWITCH PARTITION 3 TO HistoryTable PARTITION 3
ALTER TABLE CurrentTable SWITCH PARTITION 4 TO HistoryTable PARTITION 4
ALTER TABLE CurrentTable SWITCH PARTITION 5 TO HistoryTable PARTITION 5
ALTER TABLE CurrentTable SWITCH PARTITION 6 TO HistoryTable PARTITION 6
ALTER TABLE CurrentTable SWITCH PARTITION 7 TO HistoryTable PARTITION 7
ALTER TABLE CurrentTable SWITCH PARTITION 8 TO HistoryTable PARTITION 8
GO
/* For the Partitioed View to work we need a check constraint to define the key range */
/* as we've not created one this query will look at both tables */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
SELECT MAX(ID) FROM dbo.HistoryTable

/* Notice the range for the each table is define so SQL Server can use this value to select the target table */
ALTER TABLE dbo.HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<800000)
ALTER TABLE dbo.CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=800000)
GO
/* Notice in the output this time SQL Server doesn't look into the current table */
SET STATISTICS IO ON
SELECT      *
FROM        CurrentPlusHistory
WHERE       ID < 100000
AND         Filler = 'One'
SET STATISTICS IO OFF
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o 
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Now rebuild HistoryTable to see that its Partitions take the FillFactor */
ALTER INDEX ALL ON dbo.HistoryTable REBUILD
GO
/* CHECK FILLFACTORS FOR TABLES\PARTITIONS */
SELECT      OBJECT_SCHEMA_NAME(o.object_id)+'.'+o.name as Table_Name,
            s.*
FROM        sys.objects  o 
INNER JOIN  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED')   s
ON          o.object_id                                 = s.object_id
AND         s.index_id                                  = 1
AND         s.index_level                               = 0
AND         s.page_count                                <>0
WHERE       o.name IN ('CurrentTable','HistoryTable')
GO
/* Move some more data into the History Table, the CHECK CONSTRAINT range has to be managed as well so this is a coding overhead */
ALTER TABLE HistoryTable DROP CONSTRAINT HistoryTable_ID_Range
ALTER TABLE HistoryTable ADD CONSTRAINT HistoryTable_ID_Range CHECK(ID<900000)
ALTER TABLE CurrentTable SWITCH PARTITION 9 TO HistoryTable PARTITION 9
ALTER TABLE CurrentTable DROP CONSTRAINT CurrentTable_ID_Range
ALTER TABLE CurrentTable ADD CONSTRAINT CurrentTable_ID_Range CHECK(ID>=900000)
GO