June 24, 2010 at 7:24 am
Hi guys,
I have a table with million rows, i would like to partition this table in 12 partitions (12 moths), but i do not have a column with month, i have a column with date, can someone help me to pass only the month of this column? Following my script test.
use master
create database dbTestePartition
go
ALTER DATABASE dbTestePartition ADD FILEGROUP [fg_01]
GO
ALTER DATABASE dbTestePartition ADD FILE (NAME = N'PDfg_01',FILENAME = N'c:\FILE1.NDF',SIZE = 30mb) TO FILEGROUP [fg_01]
go
use dbTestePartition
go
Create PARTITION FUNCTION PFN_MonthRange (int) -- the datatype is int because I need only the number of months, and as there is a parameter I can not use MONTH(@parameter)
AS
RANGE left FOR VALUES (1, -- part 1 = dados <= 1
2,-- part 2 = dados > 1 e <=2
3,-- part 3 = dados > 2 e <=3
4,-- part 4 = dados > 3 e <=4
5,-- part 5 = dados > 4 e <=5
6,-- part 6 = dados > 5 e <=6
7,-- part 7 = dados > 6 e <=7
8,-- part 8 = dados > 7 e <=8
9,-- part 9 = dados > 8 e <=9
10,-- part 10 = dados > 9 e <=10
11,-- part 11 = dados > 10 e <=11
12)-- part 12 = dados > 11 e <=12
go
CREATE PARTITION SCHEME PS_MonthRange
AS
PARTITION PFN_MonthRange
ALL TO (fg_01)
GO
CREATE TABLE tbParticionada
( id int identity(1,1) ,
[date] DATETIME NOT NULL
)
ON PS_MonthRange (date) -- does not work, why the datatype of the partition function is int
-- does not work using PS_MonthRange MONTH (date)
GO
Tks.
Fernandes, Marcelo
[<o>]Brazil
June 24, 2010 at 7:46 am
Not sure I'd do this the way you are, but have you tried this:
CREATE TABLE tbParticionada
( id int identity(1,1) ,
[date] DATETIME NOT NULL
)
ON PS_MonthRange (MONTH(date)) -- does not work, why the datatype of the partition function is int
-- does not work using PS_MonthRange MONTH (date)
GO
Looking at your comment, not sure if you tried it this way. If it didn't (doesn't) work, what error message are you getting.
June 24, 2010 at 7:58 am
Thanks Lynn for your reply,
I had tried to create the table with the command you posted, shows me the following error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.
tks
Marcelo Fernandes
June 29, 2010 at 3:43 am
See if you can do it this way:
CREATE TABLE tbParticionada
( id int identity(1,1) ,
[date] DATETIME NOT NULL,
mth as MONTH(date) PERSISTED
)
ON PS_MonthRange (mth) -- does not work, why the datatype of the partition function is int
-- does not work using PS_MonthRange MONTH (date)
GO
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply