分区方案名不同和分区函数名不同的情况下交换分区 交换会失败！！

分区方案名不同和分区函数名不同的情况下交换分区 交换会失败！！

如果分区方案Sch_ClassifyResult_ClassId_Temp的第二个文件组是另一个文件组而不是primary，那么应该也可以交换分区

这样湖州那台微博库应该可以交换

SCHEME [Sch_ClassifyResult_ClassId_Temp] AS PARTITION [Fun_ClassifyResult_ClassId] TO ([primary],[primary],[primary])

 

 

USE [sss]

 


/****** Object: PartitionFunction [Fun_ClassifyResult_ClassId] Script Date: 2014/6/25 22:02:54 ******/
CREATE PARTITION FUNCTION [Fun_ClassifyResult_ClassId](INT) AS RANGE LEFT FOR VALUES (7809, 51504)
GO

 

/****** Object: PartitionScheme [Sch_ClassifyResult_ClassId] Script Date: 2014/6/25 22:02:39 ******/
CREATE PARTITION SCHEME [Sch_ClassifyResult_ClassId] AS PARTITION [Fun_ClassifyResult_ClassId] TO ([primary],[primary],[primary])
GO

CREATE TABLE testshcema(id INT ) ON [Sch_ClassifyResult_ClassId](id)

INSERT INTO testshcema
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 
------------------------------------------------------------------------------
/****** Object: PartitionFunction [Fun_ClassifyResult_ClassId] Script Date: 2014/6/25 22:02:54 ******/
CREATE PARTITION FUNCTION [Fun_ClassifyResult_ClassId_Temp](INT) AS RANGE LEFT FOR VALUES (7809, 51504)
GO

 

/****** Object: PartitionScheme [Sch_ClassifyResult_ClassId] Script Date: 2014/6/25 22:02:39 ******/
CREATE PARTITION SCHEME [Sch_ClassifyResult_ClassId_Temp] AS PARTITION [Fun_ClassifyResult_ClassId] TO ([primary],[primary],[primary])
GO

CREATE TABLE testshcema_temp(id INT ) ON [Sch_ClassifyResult_ClassId_Temp](id)


ALTER TABLE testshcema SWITCH PARTITION 1 TO testshcema_temp PARTITION 1

SELECT * from testshcema_temp

 

 

USE [master]
GO
CREATE DATABASE TESTSHECMA


--1.创建文件组
ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_01]

ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_02]

ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_03]

ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_04]

ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_05]

ALTER DATABASE [TESTSHECMA]
ADD FILEGROUP [FG_TESTSHECMA_Id_06]


--2.创建文件
ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_01_data',FILENAME = N'E:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_01_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_01];

ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_02_data',FILENAME = N'E:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_02_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_02];

ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_03_data',FILENAME = N'E:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_03_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_03];

ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_04_data',FILENAME = N'D:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_04_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_04];

ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_05_data',FILENAME = N'D:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_05_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_05];

ALTER DATABASE [TESTSHECMA]
ADD FILE
(NAME = N'FG_TESTSHECMA_Id_06_data',FILENAME = N'D:\DataBase\TESTSHECMA\FG_TESTSHECMA_Id_06_data.ndf',SIZE = 10MB, FILEGROWTH = 1024MB )
TO FILEGROUP [FG_TESTSHECMA_Id_06];

USE [TESTSHECMA]
GO

--3.创建分区函数
CREATE PARTITION FUNCTION
Fun_TESTSHECMA_Id(INT) AS
RANGE LEFT
FOR VALUES(2,4)



--4.创建分区方案
CREATE PARTITION SCHEME
[Sch_TESTSHECMA_Id] AS
PARTITION [Fun_TESTSHECMA_Id]
TO([FG_TESTSHECMA_Id_01],[FG_TESTSHECMA_Id_02],[FG_TESTSHECMA_Id_03])

CREATE PARTITION SCHEME
[Sch_TESTSHECMA_Id_TEMP] AS
PARTITION [Fun_TESTSHECMA_Id]
TO([FG_TESTSHECMA_Id_04],[FG_TESTSHECMA_Id_05],[FG_TESTSHECMA_Id_06])



USE TESTSHECMA
CREATE TABLE TEST(ID INT IDENTITY(1,1),name NVARCHAR(200))ON [Sch_TESTSHECMA_Id](ID)

CREATE TABLE TESTTEMP(ID INT IDENTITY(1,1),name NVARCHAR(200))ON [Sch_TESTSHECMA_Id_TEMP](ID)


INSERT INTO TEST (name)
SELECT 'sdf' UNION ALL
SELECT 'ninin' UNION ALL
SELECT '565' UNION ALL
SELECT '9DF' UNION ALL
SELECT 'DF5631' UNION ALL
SELECT '345SDFG465' 

--查看分区架构文件组分布
SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
        p.partition_number ,
        CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
        CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
        STR(p.rows, 9) AS rows
FROM    sys.indexes i
        JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
        JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
        JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
        JOIN sys.partitions p ON dds.destination_id = p.partition_number
                                 AND p.object_id = i.object_id
                                 AND p.index_id = i.index_id
        JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
        LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                  AND v.boundary_id = p.partition_number
                                                  - pf.boundary_value_on_right
WHERE   i.object_id = OBJECT_ID('[dbo].[TEST]')
        AND i.index_id IN ( 0, 1 )
ORDER BY p.partition_number


--分区区间
--SELECT  *  FROM    sys.partition_range_values


ALTER TABLE [dbo].[TEST] SWITCH PARTITION 1 TO [dbo].[TESTTEMP] PARTITION 1

--除非两个分区方案的文件组对应都是一样的，否则交换失败
--消息 4938，级别 16，状态 1，第 1 行
--ALTER TABLE SWITCH 语句失败。表'TESTSHECMA.dbo.TEST' 的分区 1 在文件组 'FG_TESTSHECMA_Id_01' 中，而 表'TESTSHECMA.dbo.TESTTEMP' 的分区 1 在文件组 'FG_TESTSHECMA_Id_04'中。