SQL Server Performance Forum – Threads Archive

setting a partitioned view constraint

I am trying to create a updateable partitioned view on Enterprise Edition and partition col is res_date. (there are 4 partition tables Q1 – Q4 and a view that does a union all. If I set the contraint on ES_DATE using this syntax CHECK (month(ES_DATE) >= 10 and month(ES_DATE) <= 12), i am able to insert the correct value into the table (table insert) but I am not able to insert into the view – unable to use a function in distributed view constarint defintion. I am able to insert into view if I hard code dates in the constarint definition.
CHECK (ES_DATE >= ‘1/1/05’ and ES_DATE <= ‘3/31/05’) How do I get this working without hard coding dates? CREATE TABLE [dbo].[PT_Q1_AV] (
[OWNER] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ES_DATE] [datetime] NOT NULL ,
[TYPE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NUM] [int] NULL ,
[CT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ML] [smallint] NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[PT_Q1_AV] ADD
CONSTRAINT [PK_PT_Q1_AV] PRIMARY KEY CLUSTERED
(
[OWNER],
[ES_DATE],
[TYPE]
) ON [PRIMARY] ,
CHECK (month(ES_DATE) >= 10 and month(ES_DATE) <= 12)
GO this syntax
CHECK (month(ES_DATE) >= 1 and month(ES_DATE) <= 3) does not let me insert into view.
this syntax
CHECK (ES_DATE >= ‘1/1/05’ and ES_DATE <= ‘3/31/05’) does allow me to insert into view

---

]]>