December 20, 2012 at 4:24 am
USE Master;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionDB')
DROP DATABASE PartitionDB;
GO
CREATE DATABASE PartitionDB ON PRIMARY
(NAME='PartitionDB_Part1',FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part1.mdf',SIZE=3, MAXSIZE=100,FILEGROWTH=1 );
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part2
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part3
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part4
GO
-------------------------------------------------------------------------------
alter database PartitionDB
add file (NAME = 'PartitionDB_Part2',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part2.ndf',
SIZE =3,MAXSIZE=100,FILEGROWTH=1 ) to filegroup PartitionDB_Part2;
alter database PartitionDB
add file (NAME = 'PartitionDB_Part3',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part3.ndf',
SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part3;
GO
alter database PartitionDB
add file (NAME = 'PartitionDB_Part4',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part4.ndf',
SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part4;
GO
USE PartitionDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME PartitionDB_PartitionScheme
AS PARTITION PartitionDB_PartitionRange TO ([PRIMARY],[PartitionDB_Part2],[PartitionDB_Part3],[PartitionDB_Part4]);
GO
--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(
ID INT identity
,Date DATETIME
) ON PartitionDB_PartitionScheme (ID);
GO
--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON TestTable(ID)
ON PartitionDB_PartitionScheme (ID);
GO
declare @count int
set @count =1
While (@count < = 5000)
begin
INSERT INTO TestTable (Date) VALUES (GETDATE());
set @count = @count + 1
end
GO
--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO
CREATE TABLE TestTable_Archive
(
ID INT identity
,Date DATETIME
)
GO
declare @count int
set @count =1
While (@count < = 40)
begin
INSERT INTO TestTable_Archive (Date) VALUES (GETDATE());
set @count = @count + 1
end
GO
CREATE UNIQUE CLUSTERED INDEX IX_TestTable_Archive ON TestTable_Archive(ID)
GO
Query
-- make the switch
ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1;
GO
Error
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values
that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.
HOw can i identify that what partition1 range is ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 20, 2012 at 7:20 am
You specified the partition ranges yourself.
CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2012 at 10:56 pm
GilaMonster (12/20/2012)
You specified the partition ranges yourself.CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
So here partition 1 means "Less than 500" ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 21, 2012 at 12:35 am
Bhuvnesh (12/20/2012)
GilaMonster (12/20/2012)
You specified the partition ranges yourself.CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
So here partition 1 means "Less than 500" ?
That's what your partition function says.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 1:25 am
GilaMonster (12/21/2012)
That's what your partition function says.
what steps i followed .
1 I creaed a table TestTable_Archive (NOn partitioned table )
2) then i moved data from testtable by ALTER TABLE TestTable SWITCH PARTITION 1 TO TestTable_Archive ;
3) then i populated the table TestTable_Archive
declare @count int
set @count =31
While (@count < = 40)
begin
INSERT INTO TestTable_Archive (id,Date) VALUES (@count,GETDATE());
set @count = @count + 1
end
GO
4) then i tried ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1;
but failed with error
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values
that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.
Can you please explain why ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 21, 2012 at 1:33 am
Bhuvnesh (12/21/2012)
Can you please explain why ?
What does the error message say you are missing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2012 at 1:48 am
GilaMonster (12/21/2012)
What does the error message say you are missing?
what i can get from error is testtable_archive data is "falling out of range" from partition 1 but actually this table contains data range 31 to 40 (basicaly less then 500) ? then y its failing or i am overlooking something here ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 21, 2012 at 2:00 am
Look at the error message. Read what it says. It says nothing at all about the data being out of range.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply