七步轻松实现大数据库表的数据转储

/*第一步在数据库存储目录下新建一个分区方案*/
USE [MultiCompany]
GO
CREATE PARTITION SCHEME [SCH_MC] AS PARTITION [FN_MC] TO ([SN2013], [PRIMARY])
GO
/*第二步在数据库存储目录下新建一个分区函数*/
USE [MultiCompany]
GO
CREATE PARTITION FUNCTION [FN_MC](SMALLINT) AS RANGE RIGHT FOR VALUES (2014)
GO
/*第三步在需要转储的表里添加一个标签字段(本处是以年份作为标签的)*/
ALTER TABLE Finance.AvgCostOfGroupByCompany
ADD YEAR SMALLINT
/*第四步为新加的标签字段赋值(可以做默认值处理:按年份的话一年改一次默认值)*/
UPDATE Finance.AvgCostOfGroupByCompany
SET YEAR=2013
/*第五步将标签字段设为非空,为下一步建联合主键做准备*/
ALTER TABLE Finance.AvgCostOfGroupByCompany
ALTER COLUMN YEAR SMALLINT NOT NULL

/*第六步修改原有索引和添加联合主键*/
USE [MultiCompany]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Finance].[AvgCostOfGroupByCompany]') AND name = N'PK_AvgCostOfGroupByCompany')
ALTER TABLE [Finance].[AvgCostOfGroupByCompany] DROP CONSTRAINT [PK_AvgCostOfGroupByCompany]
GO

USE [MultiCompany]
GO
ALTER TABLE [Finance].[AvgCostOfGroupByCompany] ADD CONSTRAINT [PK_AvgCostOfGroupByCompany] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)ON [SCH_MC]([Year])
/*第七步对历史数据进行压缩*/
在要操作的表上右击选择<存储> 然后选择<管理压缩> 之后选择<下一步>跳转到<数据压缩向导页面> 对所有分区选择相同压缩类型 压缩类型选择<PAGE> 之后就默认选择 下一步 完成就OK了