SQL Server Performance

setting a partitioned view constraint

Discussion in 'General DBA Questions' started by lsamp, Sep 22, 2004.

  1. lsamp New Member

    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


Share This Page