请问SQL SERVER2005中能否将普通表修改为分区表

时间：2023-02-13 11:09:38
请问SQL SERVER2005中能否将普通表修改为分区表


我遇到的问题是把SQL SERVER2000的数据升级到SQL SERVER2005后,
想把已有数据的大表改为分区表,请问可否直接修改而不需要先新建一张表来导表操作?

谢谢.

14 个解决方案

#1

做视图不行吗？

#2

可以, 前提是你的表上有聚焦索引(一般主键默认就是聚焦索引)

而且改的时候要把普通索引删除掉(因为改是通过删除聚焦索引实现数据切换的, 所以不删除普通索引会导致普通索引被重建, 而切换到分区表再建立聚焦索引的时候又会导致普通索引重建一次, 所以删除普通索引再重建可以避免两次重建普通索引)

#3

示例 


USE tempdb

GO


-- 测试表

CREATE TABLE dbo.tb(

	id int,

	CONSTRAINT PK_id PRIMARY KEY CLUSTERED(

		id)

)

INSERT dbo.tb

SELECT 1 UNION ALL

SELECT 10

GO


-- 切换为分区表

-- 分区函数

CREATE PARTITION FUNCTION PF_test(int)

AS RANGE LEFT

FOR VALUES(5)


-- 分区架构

CREATE PARTITION SCHEME PS_test

AS PARTITION PF_test

ALL TO(

	[PRIMARY])


-- 切换到分区表

ALTER TABLE dbo.tb 

	DROP CONSTRAINT PK_id 

		WITH(

			MOVE TO PS_test(id))

#4

上面的示例建立测试表 dbo.tb , 并切换到分区表

切换完成后, 可以查询 dbo.tb, 并检查其执行计划来确定是否使用了分区信息, 以此来确定是否正确切换到了分区表

最后可以用下面的语句删除测试 
-- 删除测试

DROP TABLE dbo.tb

DROP PARTITION SCHEME PS_test

DROP PARTITION FUNCTION PF_test

#5

谢谢解答，但表分区所用的键必须是主键的一部分吗?

我刚才试了一下,

我的表是
MyTable
(_ID VARCHAR(100)　NOT NULL,
MyDate DateTime NOT NULL,
KeepFlag Int NOT NULL)

其中_ID是主键，有非聚集索引，MyDate有聚集索引,KeepFlag是作为分区列的（因为应用的关系，不便于把MyDate作为分区列）

CREATE PARTITION FUNCTION [Data_Range](int)
AS RANGE LEFT FOR VALUES (0,1,2)

GO


CREATE PARTITION SCHEME [Data_Scheme]
AS PARTITION [Data_Range]
TO ([Data1], [Data2], [Data3],[Data4]);


GO

因为最初直接写
ALTER TABLE MYTALBE
ON Data_Scheme(KeepFlag)

报有语法错：
消息 156，级别 15，状态 1，第 2 行
关键字 'ON' 附近有语法错误。


于是先删除了主键

改为

ALTER TABLE MyTable
ADD
PRIMARY KEY NONCLUSTERED(_ID)
ON Data_Scheme(KeepFlag)
GO

这时又报错:

消息 1908，级别 16，状态 1，第 1 行
列 'KeepFlag' 是索引 'PK__MyTable__3224D676' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。
消息 1750，级别 16，状态 0，第 1 行
无法创建约束。请参阅前面的错误消息。


于是改为


ALTER TABLE MyTable
ADD
PRIMARY KEY NONCLUSTERED(_ID,KeepFlag)
ON Data_Scheme(KeepFlag)
GO

终于ＯＫ

于是想问一下：

表分区所用的键必须是主键的一部分

#6

是, 分区键列必须是主键的一部分, 联机帮助上有说明的

#7

同时，我又测试了下，删去MYDATE上的聚集索引，直接用


ALTER   TABLE   MyTable 
ADD 
PRIMARY   KEY   NONCLUSTERED(_ID,KeepFlag) 
ON   Data_Scheme(KeepFlag) 
GO 

同样可以创建成功，打开表发现并无聚集索引

#8

晕，要５天后才可加分啊

#9

非常感谢　zjcxc 的答复

#10

晕，要５天后才可加分啊

-------
已经100了，５天后你也加不了（除非你５天内得１０００分），，，

#11

崇拜呀

#12

引用 6 楼 zjcxc 的回复:
是, 分区键列必须是主键的一部分, 联机帮助上有说明的

是【创建分布式分区视图】中的一句话吧，而不是说创建分区表所用的分区列一定要是主键的一部分
在（create）创建分区表时，对分区列要求很低，但是便于性能优化，最好是聚集索引的一部分。
如果alter将已有表变为分区表的话，应该是将分区列加入到聚集索引中
如果是分区索引：分区列包含在聚集索引/唯一约束/唯一索引的键列中

#13

MARK一个

#14

MARK！！ 

#1

做视图不行吗？

#2

可以, 前提是你的表上有聚焦索引(一般主键默认就是聚焦索引)

而且改的时候要把普通索引删除掉(因为改是通过删除聚焦索引实现数据切换的, 所以不删除普通索引会导致普通索引被重建, 而切换到分区表再建立聚焦索引的时候又会导致普通索引重建一次, 所以删除普通索引再重建可以避免两次重建普通索引)

#3

示例 


USE tempdb

GO


-- 测试表

CREATE TABLE dbo.tb(

	id int,

	CONSTRAINT PK_id PRIMARY KEY CLUSTERED(

		id)

)

INSERT dbo.tb

SELECT 1 UNION ALL

SELECT 10

GO


-- 切换为分区表

-- 分区函数

CREATE PARTITION FUNCTION PF_test(int)

AS RANGE LEFT

FOR VALUES(5)


-- 分区架构

CREATE PARTITION SCHEME PS_test

AS PARTITION PF_test

ALL TO(

	[PRIMARY])


-- 切换到分区表

ALTER TABLE dbo.tb 

	DROP CONSTRAINT PK_id 

		WITH(

			MOVE TO PS_test(id))

#4

上面的示例建立测试表 dbo.tb , 并切换到分区表

切换完成后, 可以查询 dbo.tb, 并检查其执行计划来确定是否使用了分区信息, 以此来确定是否正确切换到了分区表

最后可以用下面的语句删除测试 
-- 删除测试

DROP TABLE dbo.tb

DROP PARTITION SCHEME PS_test

DROP PARTITION FUNCTION PF_test

#5

谢谢解答，但表分区所用的键必须是主键的一部分吗?

我刚才试了一下,

我的表是
MyTable
(_ID VARCHAR(100)　NOT NULL,
MyDate DateTime NOT NULL,
KeepFlag Int NOT NULL)

其中_ID是主键，有非聚集索引，MyDate有聚集索引,KeepFlag是作为分区列的（因为应用的关系，不便于把MyDate作为分区列）

CREATE PARTITION FUNCTION [Data_Range](int)
AS RANGE LEFT FOR VALUES (0,1,2)

GO


CREATE PARTITION SCHEME [Data_Scheme]
AS PARTITION [Data_Range]
TO ([Data1], [Data2], [Data3],[Data4]);


GO

因为最初直接写
ALTER TABLE MYTALBE
ON Data_Scheme(KeepFlag)

报有语法错：
消息 156，级别 15，状态 1，第 2 行
关键字 'ON' 附近有语法错误。


于是先删除了主键

改为

ALTER TABLE MyTable
ADD
PRIMARY KEY NONCLUSTERED(_ID)
ON Data_Scheme(KeepFlag)
GO

这时又报错:

消息 1908，级别 16，状态 1，第 1 行
列 'KeepFlag' 是索引 'PK__MyTable__3224D676' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。
消息 1750，级别 16，状态 0，第 1 行
无法创建约束。请参阅前面的错误消息。


于是改为


ALTER TABLE MyTable
ADD
PRIMARY KEY NONCLUSTERED(_ID,KeepFlag)
ON Data_Scheme(KeepFlag)
GO

终于ＯＫ

于是想问一下：

表分区所用的键必须是主键的一部分

#6

是, 分区键列必须是主键的一部分, 联机帮助上有说明的

#7

同时，我又测试了下，删去MYDATE上的聚集索引，直接用


ALTER   TABLE   MyTable 
ADD 
PRIMARY   KEY   NONCLUSTERED(_ID,KeepFlag) 
ON   Data_Scheme(KeepFlag) 
GO 

同样可以创建成功，打开表发现并无聚集索引

#8

晕，要５天后才可加分啊

#9

非常感谢　zjcxc 的答复

#10

晕，要５天后才可加分啊

-------
已经100了，５天后你也加不了（除非你５天内得１０００分），，，

#11

崇拜呀

#12

引用 6 楼 zjcxc 的回复:
是, 分区键列必须是主键的一部分, 联机帮助上有说明的

是【创建分布式分区视图】中的一句话吧，而不是说创建分区表所用的分区列一定要是主键的一部分
在（create）创建分区表时，对分区列要求很低，但是便于性能优化，最好是聚集索引的一部分。
如果alter将已有表变为分区表的话，应该是将分区列加入到聚集索引中
如果是分区索引：分区列包含在聚集索引/唯一约束/唯一索引的键列中

#13

MARK一个

#14

MARK！！ 
标签：

相关文章