August 12, 2009 at 8:48 am
Has any one created an partition function based on days.
I have a requirement where a table on its own is 600GB and the data is kept for 30 days. Every weekend there is a job which triggers 40 others bulk insert jobs which pumps data into the database from different locations.
Since there is no common factor to create the partition I have decided to create it based on day
example
Every months has got 1,2,3,4..........30 or 31
so through of creating 31 file groups and attached 31 file ndf files.
I know how to create a partition based on year but not able create a partition function which can pick up a day from the date column.
Any help would be really appropriated!
August 14, 2009 at 10:33 am
create a Partition Function like the below mentioned code
In below mentioned function the range is based on month & all you need to do is to convert it to dates, rest everything will remain same.
CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- Oct 2002
'20021130 23:59:59.997', -- Nov 2002
'20021231 23:59:59.997', -- Dec 2002
'20030131 23:59:59.997', -- Jan 2003
'20030228 23:59:59.997', -- Feb 2003
'20030331 23:59:59.997', -- Mar 2003
'20030430 23:59:59.997', -- Apr 2003
'20030531 23:59:59.997', -- May 2003
'20030630 23:59:59.997', -- Jun 2003
'20030731 23:59:59.997', -- Jul 2003
'20030831 23:59:59.997', -- Aug 2003
'20030930 23:59:59.997', -- Sep 2003
'20031031 23:59:59.997', -- Oct 2003
'20031130 23:59:59.997', -- Nov 2003
'20031231 23:59:59.997', -- Dec 2003
'20040131 23:59:59.997', -- Jan 2004
'20040229 23:59:59.997', -- Feb 2004
'20040331 23:59:59.997', -- Mar 2004
'20040430 23:59:59.997', -- Apr 2004
'20040531 23:59:59.997', -- May 2004
'20040630 23:59:59.997', -- Jun 2004
'20040731 23:59:59.997', -- Jul 2004
'20040831 23:59:59.997', -- Aug 2004
'20040930 23:59:59.997') -- Sep 2004
eNjoy 🙂
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 10:34 am
For more Info visit:
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 10:47 am
Thanks for reply but I guess u didn't understand my question
I can create partition based on Month or day of the year. But I want partition based on date irrespective of month. for example
if today is 08/14/2009 and if a record is inserted I want the partition function to take it as 14 and insert into fg14.ndf file. So next month when we get data for 09/14/2009 I want the data to sit in the same partition instead of creating a new one.
The function should be intelligent enough to ignore year and month and just pick up the date and insert data according to day date.
Thanks,
Brijesh
August 14, 2009 at 11:00 am
You can use this thing
i think this will definately work.
CREATE PARTITION FUNCTION TwoYear(datetime)
AS
RANGE LEFT FOR VALUES
('01-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'02-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'03-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'04-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'05-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'06-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000',
'07-'+substring(convert(varchar,getdate(),105),4,11)+' 00:00:00.000')
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 11:06 am
brij (8/14/2009)
Thanks for reply but I guess u didn't understand my questionI can create partition based on Month or day of the year. But I want partition based on date irrespective of month. for example
if today is 08/14/2009 and if a record is inserted I want the partition function to take it as 14 and insert into fg14.ndf file. So next month when we get data for 09/14/2009 I want the data to sit in the same partition instead of creating a new one.
The function should be intelligent enough to ignore year and month and just pick up the date and insert data according to day date.
Thanks,
Brijesh
Not that hard:
CREATE PARTITION FUNCTION DayPFN(int)
AS
RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);
A good place to start would be BOL (Books Online, the SQL Server Help System that can be accessed from SSMS by simply pressing the {f1} function key or on the web here: http://msdn.microsoft.com/en-us/library/ms130214.aspx).
If you still have questions after reading there, we are here to help answer and clarify any questions you may have.
Of course, not sure if passing DAY(MyDateColumn) to the partitioning function will work, as I haven't tried this.
August 14, 2009 at 11:55 am
I did think of using your your function but the problem is you are using "int" as the partition function parameter where as my column is in date time format so this won;t work.
August 14, 2009 at 11:57 am
Hi,
Thanks for the function and it works good till I reach 13th date.. after that for some reason function blows up and below is the error message I get
Msg 7705 Level 16, State 2, Line1
Could not implicity convert range values type specified at ordinal 13 to partition function parameter type.
Thanks,
Brijesh
August 14, 2009 at 12:14 pm
I just remember one more thing. If use ur function when I get data for next month it won't work because the function will try to find something with
08-14-2009 instead of 14th date it self..
August 14, 2009 at 12:24 pm
Looks like you may need to implement a sliding window on the table and write a few maintenace routines to implement what you are looking to accomplish. Not sure where, but you may want to do a search on msdn2.microsoft.com as i seem to recall an article there about doing something like that. There may also be some information in BOL as well.
August 14, 2009 at 2:50 pm
Hi Brij,
I've tested this, and this is working as per your requirement:
-- Create filegroups and files
GO
ALTER DATABASE test2 ADD FILEGROUP fg1
ALTER DATABASE test2 ADD FILEGROUP fg2
ALTER DATABASE test2 ADD FILEGROUP fg3
ALTER DATABASE test2 ADD FILEGROUP fg4
ALTER DATABASE test2 ADD FILEGROUP fg5
ALTER DATABASE test2 ADD FILEGROUP fg6
ALTER DATABASE test2 ADD FILEGROUP fg7
ALTER DATABASE test2 ADD FILEGROUP fg8
ALTER DATABASE test2 ADD FILEGROUP fg9
ALTER DATABASE test2 ADD FILEGROUP fg10
ALTER DATABASE test2 ADD FILEGROUP fg11
ALTER DATABASE test2 ADD FILEGROUP fg12
ALTER DATABASE test2 ADD FILEGROUP fg13
ALTER DATABASE test2 ADD FILEGROUP fg14
ALTER DATABASE test2 ADD FILEGROUP fg15
ALTER DATABASE test2 ADD FILEGROUP fg16
ALTER DATABASE test2 ADD FILEGROUP fg17
ALTER DATABASE test2 ADD FILEGROUP fg18
ALTER DATABASE test2 ADD FILEGROUP fg19
ALTER DATABASE test2 ADD FILEGROUP fg20
ALTER DATABASE test2 ADD FILEGROUP fg21
ALTER DATABASE test2 ADD FILEGROUP fg22
ALTER DATABASE test2 ADD FILEGROUP fg23
ALTER DATABASE test2 ADD FILEGROUP fg24
ALTER DATABASE test2 ADD FILEGROUP fg25
ALTER DATABASE test2 ADD FILEGROUP fg26
ALTER DATABASE test2 ADD FILEGROUP fg27
ALTER DATABASE test2 ADD FILEGROUP fg28
ALTER DATABASE test2 ADD FILEGROUP fg29
ALTER DATABASE test2 ADD FILEGROUP fg30
ALTER DATABASE test2 ADD FILEGROUP fg31
GO
ALTER DATABASE test2
ADD FILE
( NAME = data1,
FILENAME = 'c:\test2\AWd1.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg1
ALTER DATABASE test2
ADD FILE
( NAME = data2,
FILENAME = 'c:\test2\AWd2.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg2
ALTER DATABASE test2
ADD FILE
( NAME = data3,
FILENAME = 'c:\test2\AWd3.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg3
ALTER DATABASE test2
ADD FILE
( NAME = data4,
FILENAME = 'c:\test2\AWd4.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg4
ALTER DATABASE test2
ADD FILE
( NAME = data5,
FILENAME = 'c:\test2\AWd5.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg5
ALTER DATABASE test2
ADD FILE
( NAME = data6,
FILENAME = 'c:\test2\AWd6.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg6
ALTER DATABASE test2
ADD FILE
( NAME = data7,
FILENAME = 'c:\test2\AWd7.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg7
ALTER DATABASE test2
ADD FILE
( NAME = data8,
FILENAME = 'c:\test2\AWd8.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg8
ALTER DATABASE test2
ADD FILE
( NAME = data9,
FILENAME = 'c:\test2\AWd9.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg9
ALTER DATABASE test2
ADD FILE
( NAME = data10,
FILENAME = 'c:\test2\AWd10.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg10
ALTER DATABASE test2
ADD FILE
( NAME = data11,
FILENAME = 'c:\test2\AWd11.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg11
ALTER DATABASE test2
ADD FILE
( NAME = data12,
FILENAME = 'c:\test2\AWd12.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg12
ALTER DATABASE test2
ADD FILE
( NAME = data13,
FILENAME = 'c:\test2\AWd13.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg13
ALTER DATABASE test2
ADD FILE
( NAME = data14,
FILENAME = 'c:\test2\AWd14.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg14
ALTER DATABASE test2
ADD FILE
( NAME = data15,
FILENAME = 'c:\test2\AWd15.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg15
ALTER DATABASE test2
ADD FILE
( NAME = data16,
FILENAME = 'c:\test2\AWd16.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg16
ALTER DATABASE test2
ADD FILE
( NAME = data17,
FILENAME = 'c:\test2\AWd17.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg17
ALTER DATABASE test2
ADD FILE
( NAME = data18,
FILENAME = 'c:\test2\AWd18.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg18
ALTER DATABASE test2
ADD FILE
( NAME = data19,
FILENAME = 'c:\test2\AWd19.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg19
ALTER DATABASE test2
ADD FILE
( NAME = data20,
FILENAME = 'c:\test2\AWd20.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg20
ALTER DATABASE test2
ADD FILE
( NAME = data21,
FILENAME = 'c:\test2\AWd21.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg21
ALTER DATABASE test2
ADD FILE
( NAME = data22,
FILENAME = 'c:\test2\AWd22.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg22
ALTER DATABASE test2
ADD FILE
( NAME = data23,
FILENAME = 'c:\test2\AWd23.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg23
ALTER DATABASE test2
ADD FILE
( NAME = data24,
FILENAME = 'c:\test2\AWd24.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg24
ALTER DATABASE test2
ADD FILE
( NAME = data25,
FILENAME = 'c:\test2\AWd25.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg25
ALTER DATABASE test2
ADD FILE
( NAME = data26,
FILENAME = 'c:\test2\AWd26.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg26
ALTER DATABASE test2
ADD FILE
( NAME = data27,
FILENAME = 'c:\test2\AWd27.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg27
ALTER DATABASE test2
ADD FILE
( NAME = data28,
FILENAME = 'c:\test2\AWd28.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg28
ALTER DATABASE test2
ADD FILE
( NAME = data29,
FILENAME = 'c:\test2\AWd29.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg29
ALTER DATABASE test2
ADD FILE
( NAME = data30,
FILENAME = 'c:\test2\AWd30.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg30
ALTER DATABASE test2
ADD FILE
( NAME = data31,
FILENAME = 'c:\test2\AWd31.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg31
-- Create partition function
/*
CREATE PARTITION FUNCTION EmailPF (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('G', 'N')
GO*/
create PARTITION FUNCTION emailpf(datetime)
AS
RANGE right FOR VALUES
(--substring(convert(varchar,getdate(),112),1,6)+'01',
substring(convert(varchar,getdate(),112),1,6)+'02',
substring(convert(varchar,getdate(),112),1,6)+'03',
substring(convert(varchar,getdate(),112),1,6)+'04',
substring(convert(varchar,getdate(),112),1,6)+'05',
substring(convert(varchar,getdate(),112),1,6)+'06',
substring(convert(varchar,getdate(),112),1,6)+'07',
substring(convert(varchar,getdate(),112),1,6)+'08',
substring(convert(varchar,getdate(),112),1,6)+'09',
substring(convert(varchar,getdate(),112),1,6)+'10',
substring(convert(varchar,getdate(),112),1,6)+'11',
substring(convert(varchar,getdate(),112),1,6)+'12',
substring(convert(varchar,getdate(),112),1,6)+'13',
substring(convert(varchar,getdate(),112),1,6)+'14',
substring(convert(varchar,getdate(),112),1,6)+'15',
substring(convert(varchar,getdate(),112),1,6)+'16',
substring(convert(varchar,getdate(),112),1,6)+'17',
substring(convert(varchar,getdate(),112),1,6)+'18',
substring(convert(varchar,getdate(),112),1,6)+'19',
substring(convert(varchar,getdate(),112),1,6)+'20',
substring(convert(varchar,getdate(),112),1,6)+'21',
substring(convert(varchar,getdate(),112),1,6)+'22',
substring(convert(varchar,getdate(),112),1,6)+'23',
substring(convert(varchar,getdate(),112),1,6)+'24',
substring(convert(varchar,getdate(),112),1,6)+'25',
substring(convert(varchar,getdate(),112),1,6)+'26',
substring(convert(varchar,getdate(),112),1,6)+'27',
substring(convert(varchar,getdate(),112),1,6)+'28',
substring(convert(varchar,getdate(),112),1,6)+'29',
substring(convert(varchar,getdate(),112),1,6)+'30',
substring(convert(varchar,getdate(),112),1,6)+'31')
sp_helpfile
-- Create partition scheme
CREATE PARTITION SCHEME EmailPS
AS PARTITION EmailPF TO (fg1, fg2, fg3,fg4, fg5, fg6,fg7, fg8, fg9,fg10, fg11,fg12, fg13,
fg14, fg15, fg16,fg17, fg18, fg19,fg20, fg21, fg22, fg23, fg24, fg25,fg26, fg27, fg28,fg29, fg30, fg31)
GO
-- Create partitioned table
CREATE TABLE CustomerEmail
(CustID int identity(1,2),
cdate datetime)
ON EmailPS (cdate)
GO
-- Insert data
INSERT CustomerEmail(cdate)
VALUES
(getdate())
INSERT CustomerEmail(cdate)VALUES
(getdate()-2)
INSERT CustomerEmail(cdate)VALUES
(getdate()+2)
INSERT CustomerEmail(cdate)VALUES
(getdate()-10)
-- Retrieve partition information
SELECT cdate, $partition.EmailPF(cdate) Partition
FROM CustomerEmail
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 2:52 pm
Do let me know the result.
ALL D BEST 😎
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 3:11 pm
Hi,
I really really appropriate you taking time to write up all the code. I works like a charm for this month. But If i try to insert data from previous month it get confused sits in the first partition as its for previous month :-(.
try below sql statement
insert customeremail(cdate) values (getdate(),-15) -- should sit in partition 30
insert customeremail(cdate) values (getdate(),-25)-- shout sit in partition 20
but both end up in partition 1
Brijesh
August 14, 2009 at 3:19 pm
the reason I am trying previous month is because we keep 30 days calender months worth of data.. but some times we do tend to keep more then 30 days like this month they have kept data for 45 days... so its of a nightmare and the way they have implemented this database.
but I really appropriate your help !
Brijesh
August 14, 2009 at 3:22 pm
Going to say it again, you are going to need a rolling window partitioning for what you are trying to accomplish. Based on what I am seeing you want to keep 31 days of rolling data.
Please do a search on MSDN for an article on just that type of partitioning. I know I saw an article that discussed rolling windows for partitions, I just don't have it handing.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply