Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Right partition functions w/Sliding Window Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, 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
Post #1033992
Posted Monday, December 13, 2010 12:28 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 710, Visits: 4,534
Not enough information. Post your Partition Scheme and Partition Function. Are you returning only the month in the Partition Function?
Post #1034024
Posted Monday, December 13, 2010 12:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 3:17 AM
Points: 29, Visits: 191
It will help if you can share your partition specs along with script that you are using.


_____________
Vikas S. Rajput
Post #1034025
Posted Monday, December 13, 2010 12:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, 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]
Post #1034030
Posted Tuesday, December 14, 2010 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, 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]
Post #1034381
Posted Tuesday, December 14, 2010 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, 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...
Post #1034492
Posted Tuesday, December 14, 2010 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 11:46 AM
Points: 22, 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]
Post #1034551
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse