SQL Server Performance Forum – Threads Archive

Why did I can’t insert data to a partition view?

I create a partition view with following code.but I couldn’t insert the data into the view.please help me. Thanks very much! —————Create table ymdwk200301B begin CREATE TABLE [dbo].[ymdwk200301B] ( [fac_no] [varchar] (4) , [ymday] [varchar] (10) , [pnl] [varchar] (6) , [name] [varchar] (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , [sec_no] [varchar] (5) , constraint pk_ymdwk200301B primary key (fac_no,ymday,pnl), constraint ck_ymdwk200301B check (fac_no=’311′ and (ymday between ‘2003/01/01’ and ‘2003/01/31′) ) ) ON [PRIMARY] GO CREATE INDEX [IX_ymdwk200301B] ON [dbo].[ymdwk200301B]([fac_no], [ymday], [sec_no]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY] GO —————Create table ymdwk200301B end —————Create table ymdwk200301B begin CREATE TABLE [dbo].[ymdwk200302B] ( [fac_no] [varchar] (4) , [ymday] [varchar] (10) , [pnl] [varchar] (6) , [name] [varchar] (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , [sec_no] [varchar] (5) , constraint pk_ymdwk200302B primary key (fac_no,ymday,pnl), constraint ck_ymdwk200302B check (fac_no=’311′ and (ymday between ‘2003/02/01’ and ‘2003/02/31’) ) ) ON [PRIMARY] GO CREATE INDEX [IX_ymdwk200302B] ON [dbo].[ymdwk200302B]([fac_no], [ymday], [sec_no]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY] GO —————Create table ymdwk200301B end —————Create partitions view CREATE VIEW DBO.YMDWKB AS SELECT * FROM YMDWK200301B UNION ALL SELECT * FROM YMDWK200302B ————–Insert data to the view,but a error happened. INSERT INTO YMDWKB VALUES(‘311′,’2003/01/12′,’88888′,’JELLY’,’TEST’); Jelly.

‘2003/02/31’ is not a valid date. Gaurav

But the data type of [YMDAY] is varchar !so …… Jelly.

Are you sure that can be a criteria for partitioning? I mean in this case we’ll have character based comparison involved. I tried working with datetime as well. With datetime, the inserts in two tables are happening but while inserting in view it gives error. I think there is some sql setting that we are overlooking while creating table / view. Gaurav

Dear Gaurav,
Many thanks for your help.
I think maybe the CONSTRAINT of CHECK made this error.I tried to modified the CHECK CONSTRAIT like this:
constraint ck_ymdwk200302B check (ymday between ‘2003/02/01’ and ‘2003/02/31’) . Then the inserts will be successfully.
Does SQL Server support more than one constraints??
Jelly.

Dear Gaurav, I changed my code,now I can insert data into the partition view!! drop table dbo.ymdwk200301B drop table dbo.ymdwk200302B create table dbo.ymdwk200301B (fac_no varchar(4) , ymday varchar(10) not null, pnl varchar(6), name varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , sec_no varchar(5) , constraint pk_ymdwk200301B primary key (fac_no,ymday,pnl), constraint ck_t1 check( fac_no=’311′ ), constraint ck_ymdwk200301B check( ymday between ‘2003/01/01’ and ‘2003/01/31′) ) create table dbo.ymdwk200302B (fac_no varchar(4) , ymday varchar(10) not null, pnl varchar(6), name varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , sec_no varchar(5) , constraint pk_ymdwk200302B primary key (fac_no,ymday,pnl), constraint ck_t2 check( fac_no=’311′ ), constraint ck_ymdwk200302B check(ymday between ‘2003/02/01’ and ‘2003/02/28’) ) drop view DBO.YMDWKB create view DBO.YMDWKB as select * from ymdwk200301B union all select * from ymdwk200302B INSERT INTO YMDWKB VALUES(‘311′,’2003/01/12′,’88888′,’JELLY’,’TEST’); Jelly.

Cool man… When you have partitioned tables then the requirement is to have a constraint on the partitioning column which should define in what table the insered data dfrom view will be stored. From this example it is proved that this constraint will support value checking for only one column abd not more than that. Nice thing to know… Cheers, Gaurav

]]>