问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

sqlserver2008怎么实现自动分区表

发布网友 发布时间：2022-04-22 23:58

我来回答

共2个回答

懂视网 时间：2022-04-30 12:38

　　优化思路：采用分区表来实现日志表的自动随时间窗口滚动，即每天新增明天分区，并将7天前数据归档至日志表。以8月份为例子，当日日期为8号，流程如下图：

　　技术分享

　　具体步骤：

　　1.建立32个文件组，32个数据库文件，对应于每月31天，每月对31个文件进行复用

-- 创建文件组
USE [master]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY00]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY01]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY02]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY03]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY04]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY05]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY06]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY07]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY08]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY09]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY10]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY11]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY12]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY13]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY14]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY15]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY16]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY17]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY18]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY19]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY20]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY21]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY22]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY23]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY24]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY25]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY26]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY27]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY28]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY29]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY30]
GO
ALTER DATABASE TClientLog ADD FILEGROUP [FGDAY31]
GO

-- 创建和文件组相对应的文件，由于只有3个盘
USE [master]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY01‘, FILENAME = N‘E:partfileFDAY01.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY01]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY02‘, FILENAME = N‘E:partfileFDAY02.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY02]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY03‘, FILENAME = N‘E:partfileFDAY03.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY03]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY04‘, FILENAME = N‘E:partfileFDAY04.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY04]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY05‘, FILENAME = N‘E:partfileFDAY05.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY05]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY06‘, FILENAME = N‘E:partfileFDAY06.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY06]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY07‘, FILENAME = N‘E:partfileFDAY07.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY07]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY08‘, FILENAME = N‘E:partfileFDAY08.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY08]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY09‘, FILENAME = N‘E:partfileFDAY09.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY09]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY10‘, FILENAME = N‘E:partfileFDAY10.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY10]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY11‘, FILENAME = N‘E:partfileFDAY11.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY11]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY12‘, FILENAME = N‘E:partfileFDAY12.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY12]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY13‘, FILENAME = N‘E:partfileFDAY13.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY13]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY14‘, FILENAME = N‘E:partfileFDAY14.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY14]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY15‘, FILENAME = N‘E:partfileFDAY15.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY15]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY16‘, FILENAME = N‘E:partfileFDAY16.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY16]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY17‘, FILENAME = N‘E:partfileFDAY17.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY17]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY18‘, FILENAME = N‘E:partfileFDAY18.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY18]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY19‘, FILENAME = N‘E:partfileFDAY19.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY19]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY20‘, FILENAME = N‘E:partfileFDAY20.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY20]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY21‘, FILENAME = N‘E:partfileFDAY21.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY21]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY22‘, FILENAME = N‘E:partfileFDAY22.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY22]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY23‘, FILENAME = N‘E:partfileFDAY23.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY23]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY24‘, FILENAME = N‘E:partfileFDAY24.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY24]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY25‘, FILENAME = N‘E:partfileFDAY25.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY25]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY26‘, FILENAME = N‘E:partfileFDAY26.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY26]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY27‘, FILENAME = N‘E:partfileFDAY27.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY27]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY28‘, FILENAME = N‘E:partfileFDAY28.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY28]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY29‘, FILENAME = N‘E:partfileFDAY29.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY29]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY30‘, FILENAME = N‘E:partfileFDAY30.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY30]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY31‘, FILENAME = N‘E:partfileFDAY31.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY31]
GO
ALTER DATABASE TClientLog ADD FILE ( NAME = N‘FDAY00‘, FILENAME = N‘E:partfileFDAY00.ndf‘ , SIZE = 100MB , FILEGROWTH = 500MB ) TO FILEGROUP [FGDAY00]
GO

 

 

 

　　注意：为什么是32个文件组，32个数据库文件。这和分区函数和分区方案有关系，比如分区函数以DATETIME为分区类型，建立20150801和20150802两个边界，那么就会产生如下3个分区。那相应的分区方案中就要有3个文件组和其对应。

技术分享

      在建立分区函数时，边界的归属。请看上图，以20150801为例，如果是right，边界归属于右边，所分的两个区间:数据<20150801和20150801<=数据<20150802；如果是left，边界归属左边，所分区间为:数据<=20150801和20150801<数据<=20150802;总结一句话，关键字是那边，那么边界值就归属于所分区域的那边。

　　对于是否有必要将每个文件建立到不同的磁盘上，我这里没有建立，因为应用中的查询一般不会跨分区，都查近几个小时的，所以没有必要将文件建立到不同的磁盘文件中。

　　如果对于分区表的基础概念还不清楚，请看SQL Server表分区。

　　2.建立相应的分区函数和分区方案

　　

USE TClientLog;
CREATE PARTITION FUNCTION part_day_rang_func(DATETIME)
AS RANGE right FOR VALUES 
(
‘2015-08-01 00:00:00‘,
‘2015-08-02 00:00:00‘,
‘2015-08-03 00:00:00‘,
‘2015-08-04 00:00:00‘,
‘2015-08-05 00:00:00‘,
‘2015-08-06 00:00:00‘,
‘2015-08-07 00:00:00‘,
‘2015-08-08 00:00:00‘,
‘2015-08-09 00:00:00‘,
‘2015-08-10 00:00:00‘,
‘2015-08-11 00:00:00‘,
‘2015-08-12 00:00:00‘,
‘2015-08-13 00:00:00‘,
‘2015-08-14 00:00:00‘,
‘2015-08-15 00:00:00‘,
‘2015-08-16 00:00:00‘,
‘2015-08-17 00:00:00‘,
‘2015-08-18 00:00:00‘,
‘2015-08-19 00:00:00‘,
‘2015-08-20 00:00:00‘,
‘2015-08-21 00:00:00‘,
‘2015-08-22 00:00:00‘,
‘2015-08-23 00:00:00‘,
‘2015-08-24 00:00:00‘,
‘2015-08-25 00:00:00‘,
‘2015-08-26 00:00:00‘,
‘2015-08-27 00:00:00‘,
‘2015-08-28 00:00:00‘,
‘2015-08-29 00:00:00‘,
‘2015-08-30 00:00:00‘,
‘2015-08-31 00:00:00‘);

CREATE PARTITION SCHEME part_day_rang_scheme
AS PARTITION part_day_rang_func
TO (
FGDAY00,
FGDAY01,
FGDAY02,
FGDAY03,
FGDAY04,
FGDAY05,
FGDAY06,
FGDAY07,
FGDAY08,
FGDAY09,
FGDAY10,
FGDAY11,
FGDAY12,
FGDAY13,
FGDAY14,
FGDAY15,
FGDAY16,
FGDAY17,
FGDAY18,
FGDAY19,
FGDAY20,
FGDAY21,
FGDAY22,
FGDAY23,
FGDAY24,
FGDAY25,
FGDAY26,
FGDAY27,
FGDAY28,
FGDAY29,
FGDAY30,
FGDAY31
);

 

 

 

　　注意：分区方案指定的是文件组不是文件，FGDAY00文件组对应的分区是‘分区数据<20150801‘，FGDAY31对应的分区是‘分区数据>=20150831‘。

 

　　3.建立主日志分区表和历史日志分区表，以表内的时间字段为分区键，索引采用与表分区对齐方式，分区自动化管理脚本如下：

 

CREATE TABLE [dbo].[ClientLog](
 [SynID] [nchar](38) NOT NULL,
 [ParkingId] [int] NOT NULL,
 [ParkingBoxId] [int] NOT NULL,
 [Message] [varchar](max) NULL,
 [OccurTime] [datetime] NOT NULL,
 [UpdateTime] [datetime] NOT NULL,
 [ErrorLevel] [int] NOT NULL,
 [State] [int] NULL,
 [IsSend] [int] NULL,
 CONSTRAINT [PK_ClientLog] PRIMARY KEY NONCLUSTERED 
(
 [SynID] ASC,
 [OccurTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [part_day_rang_scheme]([OccurTime])
) ON [part_day_rang_scheme]([OccurTime])
GO
CREATE NONCLUSTERED INDEX [idx_clientlog_otime] ON [dbo].[ClientLog] 
(
 [ParkingId] 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) ON [part_day_rang_scheme]([OccurTime])
GO

 

 

　　注意：主键中必须包含分区键；分区索引必须使用和表一致的分区方案，即索引必须与表对齐，才能进行分区切换；

 

　　4.主日志分区表保留7天，每天增加后数第7天的分区，前数第7天的数据进行归档，与日志历史表进行分区交换，流程图参考优化思路的图。

-- =============================================
-- Author: zhangkun
-- Create date: <2015.08.07>
-- Description: <根据日志的滑动窗口业务，进行自动化分区管理>
-- =============================================
-- 1.修改分区方案和分区函数
-- 2.进行分区交换，将归档数据放入历史表
alter PROCEDURE [dbo].[sp_PartitionManage] @td DATETIME
AS 
 BEGIN
 DECLARE @flag CHAR(1) --标志位
 IF @td IS NULL --如果@td为null，则默认当天
  SET @td = GETDATE()
-- 1.修改分区方案和分区函数,当天新增后数第七天的日期
 BEGIN 
  DECLARE @td_next7 DATETIME
  DECLARE @day_next7 VARCHAR(2)
  DECLARE @sql NVARCHAR(MAX) --动态sql字符串
  SET @td_next7 = DATEADD(DAY, 7, @td) --7天后日期
  SET @day_next7 = CASE WHEN LEN(DATENAME(DAY, @td_next7)) = 1
     THEN ‘0‘ + DATENAME(DAY, @td_next7)
     ELSE DATENAME(DAY, @td_next7)
    END; --7天后是当月第几天SELECT @flag = COUNT(1)
  FROM sys.partition_functions a ,
   sys.partition_range_values b
  WHERE a.name = ‘part_day_rang_func‘
   AND a.function_id = b.function_id
   AND CONVERT(DATETIME, b.value) = CONVERT(VARCHAR(10), @td_next7, 120)
   + ‘ 00:00:00.000‘; 
  PRINT @flag;  
  IF ( @flag != ‘1‘ ) 
  BEGIN
   SET @sql = ‘alter partition scheme part_day_rang_scheme next used FGDAY‘
   + @day_next7 + ‘;
  alter partition function part_day_rang_func() split range(‘‘‘
   + CONVERT(VARCHAR(10), @td_next7, 120) + ‘‘‘)‘ 
   EXEC sp_executesql @sql;
  END
 END
  
-- 2.进行分区交换，将归档数据放入历史表
 DECLARE @td_before7 DATETIME
 DECLARE @day_before7 VARCHAR(2) 
 SET @td_before7 = DATEADD(DAY, -7
                        
                        热心网友
                        时间：2022-04-30 09:46
                    

                    /*第一步：创建分区函数*/

Create partition function Part_func_Bag(varchar(20)) as range right

/*正式区间

for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714')

/*第二步：创建文件组和文件*/

alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go

/*第三步：创建分区方案并关联到分区函数*/

Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go

/*第四步 重建索引（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组。数据在这一步开始转移。）*/ 

EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go
声明声明：本网页内容为用户发布，旨在传播知识，不代表本网认同其观点，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。E-MAIL:11247931@qq.com
招商银行的美元天添金怎么样? 天添美蒸馒头为什么开裂呢 上海食堂送菜 邮政跨地区取款手续费多? 谁能告诉我邮政储蓄卡跨省取钱要不要收费啊? 自贡市龙盛世纪仿真模型制造有限公司公司简介 学PS真的很难吗 红烧萝卜牛筋 微信怎么转发公众号部分内容 转发公众号部分内容方法 微信公众号内的视频如何下载?一个F12搞定 如何对一个已经存在的SQL Server表分区 如何使用SQLServer数据库按月创建表分区 sql server怎么建立分区表 求救!!!为什么我下载的软件都是文件夹形式 下载文件夹加密软件,急求哇? *文件夹加密软a件下载? 如何下载文件夹加密软件 谁知道 根据下载路径创建本地文件夹的下载软件? 18款宝马3GT招回什么意思 下载了的软件在哪个文件夹里 豪华车和跑车中的GT是啥意思? 下载到手机u盘上的软在哪个文件夹? 怪物猎人3gT10是什么意思? 圆弧齿同步带轮 S3M和3GT是什么区别啊? 交了订金了宝马3gt 有没有电子版的使用手册,先看看,谢谢了 pt950 JD01 3GT是什么意思? 3GM值,3GT值。是什么意思? C:&#92;WINDOWS&#92;SoftwareDistribution&#92;Download文件夹下所有的文件都可以删除吗？ 套餐中的3GM值、3GT值什么意思 电脑上如何下载文件夹 sqlserver 2012 怎么建立分区 SQL server 表分区问题,大家帮忙看看吧,谢啦 SQLSERVER 即将存储大量的数据,怎么设计表好点? 怎么在mysql表中创建分区表 在SQL 中怎么创建表格? 如何利用MySQL数据库命令创建和查看分区情况 如何对已经存在的数据表进行分区 请教关于SQLSERVER表分区的应用。 数据库表分区优化 SQL server 怎样可以让一个数据库用几个磁盘分区 如何每天创建一个表分区 db2数据库 怎么按日或者按月 自动创建表分区? 如何建立DB2分区数据库 小鸡腿的家常做法是什么? 小鸡腿怎么样做好吃 小鸡腿怎么烧比较好吃? 炒鸡腿怎么炒 怎样做小鸡腿,更好吃 如何做出美味的红烧小鸡腿,简单易学 B2驾驶证每年都要年检吗?
懂视 51dongshi.com 版权所有
Copyright © 2019-2024