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

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

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

view plaincopy to clipboardprint?
  1. CREATE TABLE [dbo].[BA_CACHE_AIR_RESULT](  
  2.     [AIR_ID] [nvarchar](50) NOT NULL,  
  3.     [TRIPINDEX] [nvarchar](50) NULL,  
  4.     [AIR_AGENT_ID] [varchar](36) NULL,  
  5.     [AIR_FROM_AIRPORT_CODE] [nvarchar](10) NULL,  
  6.     [AIR_TO_AIRPORT_CODE] [nvarchar](10) NULL,  
  7.     [AIR_FROM_AIRPORT_NAME] [nvarchar](50) NULL,  
  8.     [AIR_TO_AIRPORT_NAME] [nvarchar](50) NULL,  
  9.     [AIR_FROM_CITY_NAME] [nvarchar](50) NULL,  
  10.     [AIR_TO_CITY_NAME] [nvarchar](50) NULL,  
  11.     [AIR_FROM_DATETIME] [datetime] NULL,  
  12.     [AIR_TO_DATETIME] [datetime] NULL,  
  13.     [AIR_AIRFARE_PRICE] [decimal](18, 2) NULL,  
  14.     [AIR_ADULT_DISCOUNT] [decimal](18, 2) NULL,  
  15.     [AIR_CHILD_DISCOUNT] [decimal](18, 2) NULL,  
  16.     [AIR_BABY_DISCOUNT] [decimal](18, 2) NULL,  
  17.     [AIR_ADULT_DISCOUNT_ORDER] [tinyint] NULL,  
  18.     [AIR_TAX_FEE] [nvarchar](40) NULL,  
  19.     [AIR_OIL_FEE] [nvarchar](40) NULL,  
  20.     [AIR_AIRLINES_INFO] [nvarchar](50) NULL,  
  21.     [AIR_FLIGHT_CODE] [nvarchar](50) NULL,  
  22.     [AIR_FLIGHT_TYPE] [nvarchar](50) NULL,  
  23.     [AIR_CABIN_CODE] [nvarchar](50) NULL,  
  24.     [AIR_CABIN_NAME] [nvarchar](50) NULL,  
  25.     [AIR_AIRPLANE_INFO] [nvarchar](1000) NULL,  
  26.     [AIR_TUIPIAO_DESC] [nvarchar](1000) NULL,  
  27.     [AIR_GENGGAI_DESC] [nvarchar](1000) NULL,  
  28.     [AIR_QIANZHUAN_DESC] [nvarchar](1000) NULL,  
  29.     [AIR_INSURANCE_FEE] [decimal](18, 0) NULL,  
  30.     [AIR_TICKET_COUNT] [nvarchar](10) NULL,  
  31.     [AIR_LEG_TYPE] [nvarchar](1) NULL,  
  32.     [AIR_TRANSIT_AIRPORT_CODE] [nvarchar](50) NULL,  
  33.     [AIR_TRANSIT_AIRPORT_NAME] [nvarchar](50) NULL,  
  34.     [AIR_TRANSIT_CITY_NAME] [nvarchar](50) NULL,  
  35.     [AIR_TRANSIT_TO_DATETIME] [nvarchar](50) NULL,  
  36.     [AIR_TRANSIT_FROM_DATETIME] [nvarchar](50) NULL,  
  37.     [AIR_TRANSIT_AIRLINES_INFO] [nvarchar](50) NULL,  
  38.     [AIR_TRANSIT_FLIGHT_CODE] [nvarchar](50) NULL,  
  39.     [AIR_TRANSIT_AIRPLANE_INFO] [nvarchar](1000) NULL,  
  40.     [AIR_TRANSIT_TAX_FEE] [nvarchar](20) NULL,  
  41.     [AIR_TRANSIT_OIL_FEE] [nvarchar](20) NULL,  
  42.     [AIR_FROM_CITY_CODE] [nvarchar](10) NULL,  
  43.     [AIR_TO_CITY_CODE] [nvarchar](10) NULL,  
  44.     [AIR_AFTER_DISCOUNT_ADULT_PRICE] [decimal](18, 2) NULL,  
  45.     [AIR_AFTER_DISCOUNT_CHILD_PRICE] [decimal](18, 2) NULL,  
  46.     [AIR_AFTER_DISCOUNT_BABY_PRICE] [decimal](18, 2) NULL,  
  47.     [AIR_AIRLINE_RULE_ID] [varchar](5) NULL,  
  48.     [AIR_TOURCODE_ID] [varchar](20) NULL,  
  49.     [AIR_TJ] [nvarchar](1) NULL,  
  50.     [AIR_FAREBASIS] [nvarchar](100) NULL,  
  51.     [AIR_CODESHARE] [varchar](5) NULL,  
  52.     [AIR_CREATETIME] [datetime] NULL,  
  53.     [AIR_OFTEN] [int] NULL,  
  54.  CONSTRAINT [PK_AIR_ID] PRIMARY KEY NONCLUSTERED   
  55. (  
  56.     [AIR_ID] ASC  
  57. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  58. )  
  59.   
  60. GO  
  61.   
  62. SET ANSI_PADDING OFF  
  63. GO  
  64.   
  65. ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD  DEFAULT ('false') FOR [AIR_CODESHARE]  
  66. GO  
  67.   
  68. ALTER TABLE [dbo].[BA_CACHE_AIR_RESULT] ADD  DEFAULT (getdate()) FOR [AIR_CREATETIME]  
  69. GO  

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

view plaincopy to clipboardprint?
  1. ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID  
  2. --创建主键，但不设为聚集索引     
  3. ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED     
  4. (     
  5.     AIR_ID ASC     
  6. ) ON [PRIMARY]  
  7.   
  8.   
  9. IF EXISTS(  
  10. SELECT * FROM SYSINDEXES WHERE name='IX_001' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT')  
  11. )  
  12. BEGIN  
  13.  DROP INDEX IX_001 ON BA_CACHE_AIR_RESULT  
  14. END  
  15. CREATE NONCLUSTERED INDEX [IX_001] ON [dbo].[BA_CACHE_AIR_RESULT]   
  16. (  
  17.     [AIR_FROM_CITY_CODE] ASC,  
  18.     [AIR_TO_CITY_CODE] ASC,  
  19.     [AIR_FROM_DATETIME] ASC  
  20. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   
  21. IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,   
  22. ALLOW_PAGE_LOCKS  = ON)   
  23.   
  24. IF EXISTS(SELECT * FROM SYSINDEXES WHERE name='IX_002' AND id IN(SELECT id FROM sysobjects WHERE name='BA_CACHE_AIR_RESULT'))  
  25. BEGIN  
  26.  DROP INDEX IX_002 ON BA_CACHE_AIR_RESULT  
  27. END  
  28. /****** Object:  Index [IX_002]    Script Date: 03/09/2011 19:03:52 ******/  
  29. CREATE NONCLUSTERED INDEX [IX_002] ON [dbo].[BA_CACHE_AIR_RESULT]   
  30. (  
  31.     [AIR_AGENT_ID] ASC,  
  32.     [AIR_FLIGHT_CODE] ASC,  
  33.     [AIR_TRANSIT_FROM_DATETIME] ASC,  
  34.     [AIR_FROM_CITY_CODE] ASC,  
  35.     [AIR_TO_CITY_CODE] ASC  
  36. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,   
  37. IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,   
  38. ALLOW_PAGE_LOCKS  = ON)   
  39. GO  

接着我们来创建分区方案

view plaincopy to clipboardprint?
  1. CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO ([CITY04], [CITY05], [CITY06], [CITY07], [CITY07], [CITY07], [CITY07], [CITY08], [CITY08], [CITY08], [CITY08], [CITY01], [CITY02], [CITY03], [PRIMARY])  
  2. GO  

接着创建分区函数

view plaincopy to clipboardprint?
  1. 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')  
  2. GO  

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

数据库文件组

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

view plaincopy to clipboardprint?
  1. CREATE DATABASE [Demo] ON  PRIMARY   
  2. ( NAME = N'Demo', FILENAME = N'D:/EMS_DATA/Demo.mdf' , SIZE = 234880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  3.  FILEGROUP [CITY01]   
  4. ( NAME = N'CITY01', FILENAME = N'E:/AirData/CITY01.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  5.  FILEGROUP [CITY02]   
  6. ( NAME = N'CITY02', FILENAME = N'E:/AirData/CITY02.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  7.  FILEGROUP [CITY03]   
  8. ( NAME = N'CITY03', FILENAME = N'F:/AirData/CITY03.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  9.  FILEGROUP [CITY04]   
  10. ( NAME = N'CITY04', FILENAME = N'F:/AirData/CITY04.ndf' , SIZE = 333824KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  11.  FILEGROUP [CITY05]   
  12. ( NAME = N'CITY05', FILENAME = N'D:/AirData/CITY05.ndf' , SIZE = 330880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  13.  FILEGROUP [CITY06]   
  14. ( NAME = N'CITY06', FILENAME = N'D:/AirData/CITY06.ndf' , SIZE = 330176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  15.  FILEGROUP [CITY07]   
  16. ( NAME = N'CITY07', FILENAME = N'D:/AirData/CITY07.ndf' , SIZE = 981120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),   
  17.  FILEGROUP [CITY08]   
  18. ( NAME = N'CITY08', FILENAME = N'D:/AirData/CITY08.ndf' , SIZE = 651840KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  19.  LOG ON   
  20. ( NAME = Demo_log', FILENAME = N'D:/EMS_DATA/Demo.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
  21. GO  

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

view plaincopy to clipboardprint?
  1. BEGIN TRANSACTION  
  2. CREATE PARTITION FUNCTION [FromCityCodePartitionFunction](datetime) AS RANGE LEFT FOR VALUES (  
  3. N'2011-03-01T00:00:00',  
  4. N'2011-03-05T00:00:00',  
  5. N'2011-03-10T00:00:00',  
  6. N'2011-03-15T00:00:00',  
  7. N'2011-03-20T00:00:00',  
  8. N'2011-03-25T00:00:00',  
  9. N'2011-03-30T00:00:00',  
  10. N'2011-04-05T00:00:00',  
  11. N'2011-04-10T00:00:00',  
  12. N'2011-04-15T00:00:00',   
  13. N'2011-04-20T00:00:00',  
  14. N'2011-04-25T00:00:00',  
  15. N'2011-04-30T00:00:00')  
  16.   
  17.   
  18. CREATE PARTITION SCHEME [FromCityCodePartition] AS PARTITION [FromCityCodePartitionFunction] TO (  
  19. [CITY01], [CITY01], [CITY01], [CITY01],   
  20. [CITY02], [CITY02], [CITY02], [CITY02],   
  21. [CITY03], [CITY03], [CITY03], [CITY03],   
  22. [CITY03], [CITY04], [CITY04], [CITY04],   
  23. [PRIMARY])  
  24.   
  25.   
  26. ALTER TABLE BA_CACHE_AIR_RESULT DROP constraint PK_AIR_ID  
  27. --创建主键，但不设为聚集索引     
  28. ALTER TABLE BA_CACHE_AIR_RESULT ADD CONSTRAINT PK_AIR_ID PRIMARY KEY NONCLUSTERED     
  29. (     
  30.     AIR_ID ASC     
  31. ) ON [PRIMARY]  
  32.   
  33. CREATE CLUSTERED INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT]   
  34. (  
  35.     [AIR_FROM_DATETIME]  
  36. )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FromCityCodePartition]([AIR_FROM_DATETIME])  
  37.   
  38.   
  39. DROP INDEX [ClusteredIndex_on_FromCityCodePartition_634352976321562500] ON [dbo].[BA_CACHE_AIR_RESULT] WITH ( ONLINE = OFF )  
  40.   
  41.   
  42. COMMIT TRANSACTION  

等待这个执行完毕后。。

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

view plaincopy to clipboardprint?
  1. SELECT PARTITION = $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)  
  2.      ,ROWS      = COUNT(*)  
  3.      ,MINMINVAL    = MIN(AIR_FROM_DATETIME)  
  4.      ,MAXMAXVAL    = MAX(AIR_FROM_DATETIME)  
  5.  FROM DBO.BA_CACHE_AIR_RESULT  
  6. GROUP BY $PARTITION.FROMCITYCODEPARTITIONFUNCTION(AIR_FROM_DATETIME)  
  7. ORDER BY PARTITION  
  8. O  

大功告成。。。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/fx2008/p/2280316.html