September 11, 2009 at 12:24 am
I have following script
CREATE PARTITION FUNCTION Func_SeatsPartition (INT)
AS RANGE RIGHT FOR VALUES (10, 15, 20, 25)
GO
CREATE PARTITION SCHEME Sch_SeatsPartition
AS PARTITION Func_SeatsPartition
TO (Part1, Part2, Part3, Part4, Part5)
GO
IF(EXISTS(SELECT [OBJECT_ID] FROM sys.tables WHERE [OBJECT_ID] = OBJECT_ID('Classes')))
BEGIN
DROP TABLE [Classes]
END
GO
CREATE TABLE [dbo].[Classes](
[CourseID] [int] NOT NULL,
[CourseDescription] [varchar](30) NULL,
[NumAvailSeats] [int] NOT NULL CHECK ([NumAvailSeats] > 0 AND [NumAvailSeats] <= 30),
[InstructorID] [int] NULL
) ON Sch_SeatsPartition(NumAvailSeats)
GO
CREATE CLUSTERED INDEX PK_Classes_NumAvailSeats ON [Classes](NumAvailSeats)
WITH (DROP_EXISTING = OFF, FILLFACTOR = 60)
ON Sch_SeatsPartition(NumAvailSeats)
GO
INSERT INTO [Classes] (
[CourseID],
[CourseDescription],
[NumAvailSeats],
[InstructorID]
) VALUES ( 10, 'Chemistry', 30, 12 ),
( 10, 'Chemistry', 30, 13 ),
( 11, 'Biology', 25, 14 ),
( 12, 'Maths', 20, 15 ),
( 13, 'Physics', 15, 16 ),
( 14, 'English Grammer', 10, 9 ),
( 15, 'English Litreture', 10, 11 ),
( 16, 'Computer', 20, 12 )
GO
ALTER PARTITION SCHEME Sch_SeatsPartition NEXT USED Part6;
GO
CREATE TABLE ClassesStaging
(
[CourseID] [int] NOT NULL,
[CourseDescription] [varchar](30) NULL,
[NumAvailSeats] [int] NOT NULL CHECK ([NumAvailSeats] > 0 AND [NumAvailSeats] <= 30),
[InstructorID] [int] NULL
) ON Part2
GO
CREATE CLUSTERED INDEX PK_ClassesStaging_NumAvailSeats ON [ClassesStaging](NumAvailSeats)
WITH (DROP_EXISTING = OFF, FILLFACTOR = 60)
GO
ALTER TABLE [Classes]
SWITCH PARTITION 2 TO [ClassesStaging]
GO
SELECT [CourseID],
[CourseDescription],
[NumAvailSeats],
[InstructorID], $Partition.Func_SeatsPartition([NumAvailSeats]) FROM [Classes]
GO
SELECT * FROM [ClassesStaging]
GO
ALTER TABLE ClassesStaging
SWITCH TO [Classes] PARTITION 2
GO
[/code]
When I switch data from partition 2 for Classes table to ClassesStaging table then it works fine.
Then I realize I have done incorrect switch and so want to bring data back to Classes table but the reverse switch from ClassesStaging table to Classes table does not working, but rather gives me following error
Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'MCTS_DB.dbo.ClassesStaging' allows values that are not allowed by check constraints or partition function on target table 'MCTS_DB.dbo.Classes'.
Bhavesh
.NET and SQL Server Blog
September 11, 2009 at 1:05 am
Drive-by post:
From a quick look, it seemed that the check constraints do not match the range values for the *single partition* (number 2 I think it was)...
Check the partition range values on your partitioned table!
Use sys.partition_functions to get the function_id then look in sys.partition_range_values
Paul
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply