Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Right partition functions w/Sliding Window


Right partition functions w/Sliding Window

Author
Message
amy.walsh
amy.walsh
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 81
I can't make my 12-month sliding scenario work with my Range Right partition function, and haven't been able to find code anywhere that actually does it. I also posted a question about the issues I was having last week, with the result of no solutions suggested.

So I have to wonder: Has anyone tried this and gotten it to work???

thanks,
Amy
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4067 Visits: 12983
Not enough information. Post your Partition Scheme and Partition Function. Are you returning only the month in the Partition Function?
Vikas S. Rajput
Vikas S. Rajput
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 192
It will help if you can share your partition specs along with script that you are using.

_____________
Vikas S. Rajput
amy.walsh
amy.walsh
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 81
thanks for your interest. See my notes down below where it blows up...

ALTER DATABASE edw ADD FILEGROUP [201001]

ALTER DATABASE edw ADD FILEGROUP [201002]

ALTER DATABASE edw ADD FILEGROUP [201003]

ALTER DATABASE edw ADD FILEGROUP [201004]

ALTER DATABASE edw ADD FILEGROUP [201005]

ALTER DATABASE edw ADD FILEGROUP [201006]

ALTER DATABASE edw ADD FILEGROUP [201007]

ALTER DATABASE edw ADD FILEGROUP [201008]

ALTER DATABASE edw ADD FILEGROUP [201009]

ALTER DATABASE edw ADD FILEGROUP [201010]

ALTER DATABASE edw ADD FILEGROUP [201011]

ALTER DATABASE edw ADD FILEGROUP [201012]



-- Add files to the filegroups

/*
ALTER DATABASE edw
ADD FILE
(NAME = '2009',
FILENAME = 'G:\mssql\data\2009.ndf',
SIZE = 100000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [2009]
*/

ALTER DATABASE edw
ADD FILE
(NAME = '201001',
FILENAME = 'H:\mssql\data\201001.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201001]

ALTER DATABASE edw
ADD FILE
(NAME = '201002',
FILENAME = 'H:\mssql\data\201002.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201002]

ALTER DATABASE edw
ADD FILE
(NAME = '201003',
FILENAME = 'H:\mssql\data\201003.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201003]

ALTER DATABASE edw
ADD FILE
(NAME = '201004',
FILENAME = 'H:\mssql\data\201004.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201004]

ALTER DATABASE edw
ADD FILE
(NAME = '201005',
FILENAME = 'H:\mssql\data\201005.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201005]


ALTER DATABASE edw
ADD FILE
(NAME = '201006',
FILENAME = 'H:\mssql\data\201006.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201006]


ALTER DATABASE edw
ADD FILE
(NAME = '201007',
FILENAME = 'H:\mssql\data\201007.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201007]


ALTER DATABASE edw
ADD FILE
(NAME = '201008',
FILENAME = 'H:\mssql\data\201008.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201008]


ALTER DATABASE edw
ADD FILE
(NAME = '201009',
FILENAME = 'H:\mssql\data\201009.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201009]

ALTER DATABASE edw
ADD FILE
(NAME = '201010',
FILENAME = 'H:\mssql\data\201010.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201010]

ALTER DATABASE edw
ADD FILE
(NAME = '201011',
FILENAME = 'H:\mssql\data\201011.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201011]

ALTER DATABASE edw
ADD FILE
(NAME = '201012',
FILENAME = 'H:\mssql\data\201012.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201012]




CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES ('20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')


-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO ([201001],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [PRIMARY])

CREATE TABLE [dbo].[fill_fact]
(
time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null
)
ON dispense_date_pscheme (dispense_date)

--And here is my sliding window manipulation script:

ALTER DATABASE edw ADD FILEGROUP [201101]
ALTER DATABASE edw ADD FILE (NAME = '201101', FILENAME = 'H:\mssql\data\201101.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201101]

CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
--greater than 20110101
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101')
) ON [201101]

CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null
)
ON [201001]

ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201101]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')

/*this next step is where it chokes, because partition 12 was not created on 201101, but on Primary.
partition 13 was created on Filegroup 201101. This is where it confuses me, because when I split the range (added the 20110201 boundary), I think it should have split Primary (the empty end partition 13), and inserted the new partition on 201101 in as partition 12. Is this not correct? */

ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
ALTER DATABASE edw REMOVE FILE [201001]
ALTER DATABASE edw REMOVE FILEGROUP [201001]
amy.walsh
amy.walsh
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 81
FYI, for anyone interested, I did get it to work. I had to deviate from the way that Kimberly Tripp had said to do it, but at least it works. Instead of the final partition being created on Primary, it had to be created on a real filegroup that is eventually going to be utilized. After the final partition is split, the [now] last partition is the one on the filegroup that was indicated as "next used". Here is the new successful code for the Range Right 12-month sliding window update (without repeating all the creation scripts already presented above -- just showing additional or changed code):

Also Note: This code is dynamic in the sense that it was automatically generated from within a script. I just did a whole lot of print statements for the dynamic sql that was being created to get the resulting script for testing...

ALTER DATABASE edw ADD FILEGROUP [201101]
ALTER DATABASE edw
ADD FILE
(NAME = '201101',
FILENAME = 'H:\mssql\data\201101.ndf',
SIZE = 65000MB,
MAXSIZE='UNLIMITED',
FILEGROWTH = 100MB)
TO FILEGROUP [201101]

CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES (/*'2009',*/ '20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')

-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO (/*[2009],*/[201001],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [201101])


ALTER DATABASE edw ADD FILEGROUP [201102]
ALTER DATABASE edw ADD FILE (NAME = '201102', FILENAME = 'H:\mssql\data\201102.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201102]

CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101'
AND [dispense_date] < '20110201')
) ON [201101]

CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null)
ON [201001]

ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201102]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')
ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
--the following doesn't work yet -- there seems to be stuff sticking around in this File/FG...
ALTER DATABASE edw REMOVE FILE [201001]
ALTER DATABASE edw REMOVE FILEGROUP [201001]
amy.walsh
amy.walsh
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 81
wait a minute -- having similar issue on the other side of the range. Maybe Primary needed to be in the first partition... checking that out now...
amy.walsh
amy.walsh
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 81
OK, figured out where the Primary filegroup belongs on a Range Right partitioning function, and why...

here is the new code, for anyone trying to do this:

CREATE PARTITION FUNCTION dispense_date_pfn(date)
AS
RANGE RIGHT FOR VALUES ('20100201','20100301','20100401','20100501','20100601','20100701','20100801','20100901','20101001','20101101','20101201', '20110101')


-- Create partition scheme
CREATE PARTITION SCHEME dispense_date_pscheme
AS
PARTITION dispense_date_pfn
TO ([PRIMARY],[201002],[201003],[201004],[201005], [201006], [201007], [201008], [201009], [201010], [201011], [201012], [201101])

ALTER DATABASE edw ADD FILEGROUP [201102]
ALTER DATABASE edw ADD FILE (NAME = '201102', FILENAME = 'H:\mssql\data\201102.ndf',
SIZE = 65000MB, MAXSIZE='UNLIMITED', FILEGROWTH = 100MB) TO FILEGROUP [201102]

CREATE TABLE [dbo].[fill_fact_add]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null,
CONSTRAINT dispense_date_add_chk_con
CHECK ([dispense_date] >= '20110101'
AND [dispense_date] < '20110201')
) ON [201101]

CREATE TABLE [dbo].[fill_fact_drop]
( time_key int not null,
chain_key int not null,
patient_key int not null,
pharmacy_key int not null,
drug_key int not null,
dispense_date date not null)
ON [PRIMARY]

ALTER TABLE fill_fact SWITCH PARTITION 1 to fill_fact_drop
ALTER PARTITION FUNCTION dispense_date_pfn() MERGE RANGE('20100201')
ALTER PARTITION SCHEME dispense_date_pscheme NEXT USED [201102]
ALTER PARTITION FUNCTION dispense_date_pfn() SPLIT RANGE ('20110201')
ALTER TABLE fill_fact_add SWITCH TO fill_fact PARTITION 12
DROP TABLE fill_fact_drop
DROP TABLE fill_fact_add
ALTER DATABASE edw REMOVE FILE [201002]
ALTER DATABASE edw REMOVE FILEGROUP [201002]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search