【转载】SQL2008分区表的应用策略 .

由于公司项目上要使用分区表来对表进行分区管理。。。

首先创建表结构，其中我们用AIR_FROM_DATETIME字段来进行分区。。。

CREATE TABLE [dbo].[BA_CACHE_AIR_RESULT](
[AIR_ID] [nvarchar](50) NOT NULL,
[TRIPINDEX] [nvarchar](50) NULL,
[AIR_AGENT_ID] [varchar](36) NULL,
[AIR_FROM_AIRPORT_CODE] [nvarchar](10) NULL,
[AIR_TO_AIRPORT_CODE] [nvarchar](10) NULL,
[AIR_FROM_AIRPORT_NAME] [nvarchar](50) NULL,
[AIR_TO_AIRPORT_NAME] [nvarchar](50) NULL,
[AIR_FROM_CITY_NAME] [nvarchar](50) NULL,
[AIR_TO_CITY_NAME] [nvarchar](50) NULL,
[AIR_FROM_DATETIME] [datetime] NULL,
[AIR_TO_DATETIME] [datetime] NULL,
[AIR_AIRFARE_PRICE] [decimal](18, 2) NULL,
[AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL,
[AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL,
[AIR_BABY_DISCOUNT] [decimal](18, 2) NULL,
[AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL,
[AIR_TAX_FEE] [nvarchar](40) NULL,
[AIR_OIL_FEE] [nvarchar](40) NULL,
[AIR_AIRLINES_INFO] [nvarchar](50) NULL,
[AIR_FLIGHT_CODE] [nvarchar](50) NULL,
[AIR_FLIGHT_TYPE] [nvarchar](50) NULL,
[AIR_CABIN_CODE] [nvarchar](50) NULL,
[AIR_CABIN_NAME] [nvarchar](50) NULL,
[AIR_AIRPLANE_INFO] [nvarchar](1000) NULL,
[AIR_TUIPIAO_DESC] [nvarchar](1000) NULL,
[AIR_GENGGAI_DESC] [nvarchar](1000) NULL,
[AIR_QIANZHUAN_DESC] [nvarchar](1000) NULL,
[AIR_INSURANCE_FEE] [decimal](18, 0) NULL,
[AIR_TICKET_COUNT] [nvarchar](10) NULL,
[AIR_LEG_TYPE] [nvarchar](1) NULL,
[AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) NULL,
[AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) NULL,
[AIR_TRANSIT_CITY_NAME] [nvarchar](50) NULL,
[AIR_TRANSIT_TO_DATETIME] [nvarchar](50) NULL,
[AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) NULL,
[AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) NULL,
[AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) NULL,
[AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) NULL,
[AIR_TRANSIT_TAX_FEE] [nvarchar](20) NULL,
[AIR_TRANSIT_OIL_FEE] [nvarchar](20) NULL,
[AIR_FROM_CITY_CODE] [nvarchar](10) NULL,
[AIR_TO_CITY_CODE] [nvarchar](10) NULL,
[AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL,
[AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL,
[AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL,
[AIR_AIRLINE_RULE_ID] [varchar](5) NULL,
[AIR_TOURCODE_ID] [varchar](20) NULL,
[AIR_TJ] [nvarchar](1) NULL,
[AIR_FAREBASIS] [nvarchar](100) NULL,
[AIR_CODESHARE] [varchar](5) NULL,
[AIR_CREATETIME] [datetime] NULL,
[AIR_OFTEN] [int] NULL,
CONSTRAINT [PK_AIR_ID] PRIMARY KEY NONCLUSTERED
(
[AIR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD DEFAULT ('false') FOR [AIR_CODESHARE]
GO
ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD DEFAULT (getdate()) FOR [AIR_CREATETIME]
GO

CREATE TABLE [dbo].[BA_CACHE_AIR_RESULT](
	[AIR_ID] [nvarchar](50) NOT NULL,
	[TRIPINDEX] [nvarchar](50) NULL,
	[AIR_AGENT_ID] [varchar](36) NULL,
	[AIR_FROM_AIRPORT_CODE] [nvarchar](10) NULL,
	[AIR_TO_AIRPORT_CODE] [nvarchar](10) NULL,
	[AIR_FROM_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_TO_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_FROM_CITY_NAME] [nvarchar](50) NULL,
	[AIR_TO_CITY_NAME] [nvarchar](50) NULL,
	[AIR_FROM_DATETIME] [datetime] NULL,
	[AIR_TO_DATETIME] [datetime] NULL,
	[AIR_AIRFARE_PRICE] [decimal](18, 2) NULL,
	[AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_BABY_DISCOUNT] [decimal](18, 2) NULL,
	[AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL,
	[AIR_TAX_FEE] [nvarchar](40) NULL,
	[AIR_OIL_FEE] [nvarchar](40) NULL,
	[AIR_AIRLINES_INFO] [nvarchar](50) NULL,
	[AIR_FLIGHT_CODE] [nvarchar](50) NULL,
	[AIR_FLIGHT_TYPE] [nvarchar](50) NULL,
	[AIR_CABIN_CODE] [nvarchar](50) NULL,
	[AIR_CABIN_NAME] [nvarchar](50) NULL,
	[AIR_AIRPLANE_INFO] [nvarchar](1000) NULL,
	[AIR_TUIPIAO_DESC] [nvarchar](1000) NULL,
	[AIR_GENGGAI_DESC] [nvarchar](1000) NULL,
	[AIR_QIANZHUAN_DESC] [nvarchar](1000) NULL,
	[AIR_INSURANCE_FEE] [decimal](18, 0) NULL,
	[AIR_TICKET_COUNT] [nvarchar](10) NULL,
	[AIR_LEG_TYPE] [nvarchar](1) NULL,
	[AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) NULL,
	[AIR_TRANSIT_CITY_NAME] [nvarchar](50) NULL,
	[AIR_TRANSIT_TO_DATETIME] [nvarchar](50) NULL,
	[AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) NULL,
	[AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) NULL,
	[AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) NULL,
	[AIR_TRANSIT_TAX_FEE] [nvarchar](20) NULL,
	[AIR_TRANSIT_OIL_FEE] [nvarchar](20) NULL,
	[AIR_FROM_CITY_CODE] [nvarchar](10) NULL,
	[AIR_TO_CITY_CODE] [nvarchar](10) NULL,
	[AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL,
	[AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL,
	[AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL,
	[AIR_AIRLINE_RULE_ID] [varchar](5) NULL,
	[AIR_TOURCODE_ID] [varchar](20) NULL,
	[AIR_TJ] [nvarchar](1) NULL,
	[AIR_FAREBASIS] [nvarchar](100) NULL,
	[AIR_CODESHARE] [varchar](5) NULL,
	[AIR_CREATETIME] [datetime] NULL,
	[AIR_OFTEN] [int] NULL,
 CONSTRAINT [PK_AIR_ID] PRIMARY KEY NONCLUSTERED 
(
	[AIR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD  DEFAULT ('false') FOR [AIR_CODESHARE]
GO

ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD  DEFAULT (getdate()) FOR [AIR_CREATETIME]
GO

表结构创建好了后，创建三个非聚集索引，如果应用了分区表的话，最好是不要使用聚集索引的。如果在其中一个分区使用了，则数据又会回到那个分区上。。。分区表就没作用了。。

view plain copy to clipboard print ?

ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID
--创建主键，但不设为聚集索引
ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED
(
AIR_ID ASC
) ON [PRIMARY]
IF EXISTS(
SELECT * FROM SYSINDEXES WHERE name='IX_001' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT')
)
BEGIN
DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT
END
CREATE NONCLUSTERED INDEX [IX_001] ON [dbo].[BA_CACHE_AIR_RESULT]
(
[AIR_FROM_CITY_CODE] ASC,
[AIR_TO_CITY_CODE] ASC,
[AIR_FROM_DATETIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
IF EXISTS(SELECT * FROM SYSINDEXES WHERE name='IX_002' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT'))
BEGIN
DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT
END
/****** Object: Index [IX_002] Script Date: 03/09/2011 19:03:52 ******/
CREATE NONCLUSTERED INDEX [IX_002] ON [dbo].[BA_CACHE_AIR_RESULT]
(
[AIR_AGENT_ID] ASC,
[AIR_FLIGHT_CODE] ASC,
[AIR_TRANSIT_FROM_DATETIME] ASC,
[AIR_FROM_CITY_CODE] ASC,
[AIR_TO_CITY_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID
--创建主键，但不设为聚集索引   
ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED   
(   
    AIR_ID ASC   
) ON [PRIMARY]

IF EXISTS(
SELECT * FROM SYSINDEXES WHERE name='IX_001' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT')
)
BEGIN
 DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT
END
CREATE NONCLUSTERED INDEX [IX_001] ON [dbo].[BA_CACHE_AIR_RESULT] 
(
	[AIR_FROM_CITY_CODE] ASC,
	[AIR_TO_CITY_CODE] ASC,
	[AIR_FROM_DATETIME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON)

IF EXISTS(SELECT * FROM SYSINDEXES WHERE name='IX_002' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT'))
BEGIN
 DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT
END
/****** Object:  Index [IX_002]    Script Date: 03/09/2011 19:03:52 ******/
CREATE NONCLUSTERED INDEX [IX_002] ON [dbo].[BA_CACHE_AIR_RESULT] 
(
	[AIR_AGENT_ID] ASC,
	[AIR_FLIGHT_CODE] ASC,
	[AIR_TRANSIT_FROM_DATETIME] ASC,
	[AIR_FROM_CITY_CODE] ASC,
	[AIR_TO_CITY_CODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) 
GO

接着我们来创建分区方案

view plain copy to clipboard print ?

CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO ([CITY04], [CITY05], [CITY06], [CITY07], [CITY07], [CITY07], [CITY07], [CITY08], [CITY08], [CITY08], [CITY08], [CITY01], [CITY02], [CITY03], [PRIMARY])
GO

接着创建分区函数

view plain copy to clipboard print ?

CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES (N'2011-03-15T00:00:00.000', N'2011-03-20T00:00:00.000', N'2011-03-25T00:00:00.000', N'2011-03-30T00:00:00.000', N'2011-04-05T00:00:00.000', N'2011-04-10T00:00:00.000', N'2011-04-15T00:00:00.000', N'2011-04-20T00:00:00.000', N'2011-04-25T00:00:00.000', N'2011-04-30T00:00:00.000', N'2011-05-01T00:00:00.000', N'2011-05-03T00:00:00.000', N'2011-05-05T00:00:00.000', N'2011-05-07T00:00:00.000')
GO

到这里部分哥们可能要问了：这些CITY04，CITY05等是什么？这些呢是数据库的文件组啦。。上图

数据库文件组

如果还不清楚的话，哥就把数据库的创建脚本也贴上。。。

view plain copy to clipboard print ?

CREATE DATABASE [Demo] ON PRIMARY
( NAME = N'Demo', FILENAME = N'D:/EMS_DATA/Demo.mdf' , SIZE = 234880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY01]
( NAME = N'CITY01', FILENAME = N'E:/AirData/CITY01.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY02]
( NAME = N'CITY02', FILENAME = N'E:/AirData/CITY02.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY03]
( NAME = N'CITY03', FILENAME = N'F:/AirData/CITY03.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY04]
( NAME = N'CITY04', FILENAME = N'F:/AirData/CITY04.ndf' , SIZE = 333824KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY05]
( NAME = N'CITY05', FILENAME = N'D:/AirData/CITY05.ndf' , SIZE = 330880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY06]
( NAME = N'CITY06', FILENAME = N'D:/AirData/CITY06.ndf' , SIZE = 330176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY07]
( NAME = N'CITY07', FILENAME = N'D:/AirData/CITY07.ndf' , SIZE = 981120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [CITY08]
( NAME = N'CITY08', FILENAME = N'D:/AirData/CITY08.ndf' , SIZE = 651840KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = Demo_log', FILENAME = N'D:/EMS_DATA/Demo.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

CREATE DATABASE [Demo] ON  PRIMARY 
( NAME = N'Demo', FILENAME = N'D:/EMS_DATA/Demo.mdf' , SIZE = 234880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY01] 
( NAME = N'CITY01', FILENAME = N'E:/AirData/CITY01.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY02] 
( NAME = N'CITY02', FILENAME = N'E:/AirData/CITY02.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY03] 
( NAME = N'CITY03', FILENAME = N'F:/AirData/CITY03.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY04] 
( NAME = N'CITY04', FILENAME = N'F:/AirData/CITY04.ndf' , SIZE = 333824KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY05] 
( NAME = N'CITY05', FILENAME = N'D:/AirData/CITY05.ndf' , SIZE = 330880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY06] 
( NAME = N'CITY06', FILENAME = N'D:/AirData/CITY06.ndf' , SIZE = 330176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY07] 
( NAME = N'CITY07', FILENAME = N'D:/AirData/CITY07.ndf' , SIZE = 981120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [CITY08] 
( NAME = N'CITY08', FILENAME = N'D:/AirData/CITY08.ndf' , SIZE = 651840KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = Demo_log', FILENAME = N'D:/EMS_DATA/Demo.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

好了,到这里我们就开始创建分区了。。。

view plain copy to clipboard print ?

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES (
N'2011-03-01T00:00:00',
N'2011-03-05T00:00:00',
N'2011-03-10T00:00:00',
N'2011-03-15T00:00:00',
N'2011-03-20T00:00:00',
N'2011-03-25T00:00:00',
N'2011-03-30T00:00:00',
N'2011-04-05T00:00:00',
N'2011-04-10T00:00:00',
N'2011-04-15T00:00:00',
N'2011-04-20T00:00:00',
N'2011-04-25T00:00:00',
N'2011-04-30T00:00:00')
CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO (
[CITY01], [CITY01], [CITY01], [CITY01],
[CITY02], [CITY02], [CITY02], [CITY02],
[CITY03], [CITY03], [CITY03], [CITY03],
[CITY03], [CITY04], [CITY04], [CITY04],
[PRIMARY])
ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID
--创建主键，但不设为聚集索引
ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED
(
AIR_ID ASC
) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT]
(
[AIR_FROM_DATETIME]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FromCityCodePartition]([AIR_FROM_DATETIME])
DROP INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] WITH ( ONLINE = OFF )
COMMIT TRANSACTION

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES (
N'2011-03-01T00:00:00',
N'2011-03-05T00:00:00',
N'2011-03-10T00:00:00',
N'2011-03-15T00:00:00',
N'2011-03-20T00:00:00',
N'2011-03-25T00:00:00',
N'2011-03-30T00:00:00',
N'2011-04-05T00:00:00',
N'2011-04-10T00:00:00',
N'2011-04-15T00:00:00', 
N'2011-04-20T00:00:00',
N'2011-04-25T00:00:00',
N'2011-04-30T00:00:00')

CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO (
[CITY01], [CITY01], [CITY01], [CITY01], 
[CITY02], [CITY02], [CITY02], [CITY02], 
[CITY03], [CITY03], [CITY03], [CITY03], 
[CITY03], [CITY04], [CITY04], [CITY04], 
[PRIMARY])

CREATE CLUSTERED INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] 
(
	[AIR_FROM_DATETIME]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FromCityCodePartition]([AIR_FROM_DATETIME])

DROP INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

等待这个执行完毕后。。

我们就通过这个SQL语句来查看各分区的情况了。。。

view plain copy to clipboard print ?

SELECT PARTITION = $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)
,ROWS = COUNT(*)
,MINMINVAL = MIN(AIR_FROM_DATETIME)
,MAXMAXVAL = MAX(AIR_FROM_DATETIME)
FROM DBO.BA_CACHE_AIR_RESULT
GROUP BY $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)
ORDER BY PARTITION
O

大功告成。。。

posted @ 2011-12-08 10:16 火腿骑士阅读(276) 评论(0) 编辑收藏举报

刷新页面返回顶部

【转载】SQL2008分区表的应用策略 .

公告