help understanding ALTER PARTITION ... SPLIT RANGE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help understanding ALTER PARTITION ... SPLIT RANGE

Author

Topic

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2008-09-03 : 16:36:24

Hello!

I am testing partitioning feature and trying to understand behavious of ALTER PARTITION FUNCTION... SPLIT RANGE.
I have created following objects:

CREATE PARTITION FUNCTION [PFLogDateRange](datetime) AS
RANGE LEFT FOR VALUES ( N'20071231 23:59:59.997', --before January 1st 2008
N'20080831 23:59:59.997' --between January 1st and August 31st 2008
)

--create partition schema with single filegroup
CREATE PARTITION SCHEME PFLogDateRange
AS
PARTITION PFLogDateRange TO
([FG1],[FG2],[FG3])--,[PRIMARY])

CREATE TABLE [dbo].[SRLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationId] [int] NOT NULL,
[Code] [varchar](50) NULL,
[ExceptionID] [varchar](50) NULL,
[ParentExceptionID] [varchar](50) NULL,
[ExceptionGroupID] [varchar](50) NULL,
[EventType] [varchar](50) NULL,
[SeverityLevel] [varchar](50) NOT NULL,
[MachineName] [varchar](50) NOT NULL,
[Message] [varchar](1000) NOT NULL,
[Detail] [varchar](max) NULL,
[AdditionalData] [xml] NULL,
[CreateDate] [datetime] NOT NULL --CONSTRAINT OrdersRangeYearCK CHECK (CreateDate>='19000601' AND CreateDate<'20081001')
,
[CreatorMemberId] [int] NOT NULL ,
[UpdateDate] [datetime] NOT NULL ,
[UpdaterMemberId] [int] NOT NULL ,
[AppVersion] [varchar](32) NOT NULL ,
[AreaId] [int] NULL,
[LogStatusId] [int] NOT NULL

) ON PFLogDateRange(CreateDate)

GO
--Unique indexes must contain the partitioning column as an indexed column
--Non-unique indexes do not need to have the partitioning column as part of the index, it can be an INCLUDE column. If it is not explicitly included, then the column will be automatically added
ALTER TABLE dbo.SRLog ADD CONSTRAINT
PK_SRLog PRIMARY KEY NONCLUSTERED
(

logID ,CreateDate
--CreateDate
) --ON PFLogDateRange(CreateDate)
--include (CreateDate)
GO

CREATE CLUSTERED INDEX Ind_SRLog_CreateDate ON SRLog(CreateDate)
GO

I have populated table with sample data. I have following number of records in Partitions/File Groups:

Partition Number FileGroupNume Number of Rows
1 FG1 7605
2 FG2 2370
3 FG3 25

Next, I am adding some test data (5 rows) for month of October 2008 and add new file group where I would like to store new data:

DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);

--SELECT @data_path

DECLARE @PartitionNumber smallint,
@FilegroupNumber tinyint,
@Location nvarchar(50),
@ExecStr nvarchar(300)

SELECT @ExecStr = N'ALTER DATABASE TestPartition ' +
N' ADD FILE ' +
N' (NAME = N''TestPartitionFG' + CONVERT(nvarchar, 4) + N'File1'',' +
N' FILENAME = N''' + @data_path + N'\TestPartitionFG' + CONVERT(nvarchar, 4) + 'File1.ndf'',' +
N' SIZE = 1MB,' +
N' MAXSIZE = 100MB,' +
N' FILEGROWTH = 5MB)' +
N' TO FILEGROUP ' + N'[FG4]'
--SELECT (@ExecStr)
EXEC (@ExecStr) -- DO NOT UNCOMMENT THIS UNTIL YOU ARE SURE THE NAMES and LOCATIONS are correct.

GO

--Currently we have 30 rows associated with partition 3.

I assign FG4 as next used partition:

ALTER PARTITION SCHEME PFLogDateRange
NEXT USED [FG4]
GO

Then, I perform split range operation:

DECLARE @strDate DATETIME
SELECT @strDate='20080930 23:59:59.997'
ALTER PARTITION FUNCTION [PFLogDateRange] () split RANGE (@strDate)
GO

All rows starting October 1st 2008 (5 rows) should be moved to new partition.
My expectation were that newly added 5 rows should end up in new partition (and FG4 respectively), but this is not the case:

1 FG1 7605
2 FG2 2370
3 FG4 25
4 FG3 5

QUESTION:
I was under impression after reading BOL that rows that fall into new range should be moved to new file group. But this is not what's happening. Any help is greately appreciated.

Thanks,
Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources