Re: Partitioned view on two columns

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Scott Morris (bogus_at_bogus.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 11:53:50 -0500

Partitioning can only be done on a SINGLE column. You are attempting to
partition on two columns.

"Pederb" <pederb@ofir.dk> wrote in message
news:6273A219-4193-466B-BE9A-6C3735A4F9EC@microsoft.com...
> Hello,
> I'm trying to make a view that is partitioned on 4(or more) tables using
> constraints on two coulms.
>
> Constraints:
> tbl1:
> intSite=1
> datDate between convert(datetime, '01-01-1900', 103) and convert(datetime,
> '01-01-2000', 103)
>
> tbl2:
> intSite=1
> datDate between convert(datetime, '01-01-2000 00:00:01', 103) and
> convert(datetime, '01-01-2070', 103)
>
> tbl3:
> intSite=2
> datDate between convert(datetime, '01-01-1900', 103) and convert(datetime,
> '01-01-2000', 103)
>
> tbl4:
> intSite=2
> datDate between convert(datetime, '01-01-2000 00:00:01', 103) and
> convert(datetime, '01-01-2070', 103)
>
> --Now that should be enough to differentiate the tables from each other,
so
> I create the view:
> create view view1 as select * from tbl1 union all select * from tbl2 union
> all select * from tbl3 union all select * from tbl4
>
> --And try to insert a row into the view:
> insert into view1(intId, intSite, datDate)
> values (1 ,1 , convert(datetime, '01-01-1999', 103))
>
> --But then a get the error:
> Server: Msg 4436, Level 16, State 12, Line 1
> UNION ALL view 'view1' is not updatable because a partitioning column was
> not found.
>
> So is it possible to do it?
>
> The script that does it all are below!
> --
> Chill and thanx, Peder
>
> --tbl1
> CREATE TABLE [tbl1] (
> [intId] [int] NOT NULL ,
> [intSite] [int] NOT NULL ,
> [datDate] [datetime] NOT NULL ,
> CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED
> (
> [intId],
> [intSite],
> [datDate]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_tbl1] CHECK ([intSite] = 1),
> CONSTRAINT [CK_tbl1_1] CHECK ([datDate] >
> convert(datetime,'01-01-1900',103) and [datDate] <=
> convert(datetime,'01-01-2000',103))
> ) ON [PRIMARY]
> GO
>
> --tbl2
> CREATE TABLE [tbl2] (
> [intId] [int] NOT NULL ,
> [intSite] [int] NOT NULL ,
> [datDate] [datetime] NOT NULL ,
> CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED
> (
> [intId],
> [intSite],
> [datDate]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_tbl2] CHECK ([intSite] = 1),
> CONSTRAINT [CK_tbl2_1] CHECK ([datDate] > convert(datetime,'01-01-2000
> 00:00:01',103) and [datDate] < convert(datetime,'01-01-2070',103))
> ) ON [PRIMARY]
> GO
> --tbl3
> CREATE TABLE [tbl3] (
> [intId] [int] NOT NULL ,
> [intSite] [int] NOT NULL ,
> [datDate] [datetime] NOT NULL ,
> CONSTRAINT [PK_tbl3] PRIMARY KEY CLUSTERED
> (
> [intId],
> [intSite],
> [datDate]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_tbl3] CHECK ([intSite] = 2),
> CONSTRAINT [CK_tbl3_1] CHECK ([datDate] >
> convert(datetime,'01-01-1900',103) and [datDate] <=
> convert(datetime,'01-01-2000',103))
> ) ON [PRIMARY]
> GO
> --tbl4
> CREATE TABLE [tbl4] (
> [intId] [int] NOT NULL ,
> [intSite] [int] NOT NULL ,
> [datDate] [datetime] NOT NULL ,
> CONSTRAINT [PK_tbl4] PRIMARY KEY CLUSTERED
> (
> [intId],
> [intSite],
> [datDate]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_tbl4] CHECK ([intSite] = 2),
> CONSTRAINT [CK_tbl4_1] CHECK ([datDate] > convert(datetime,'01-01-2000
> 00:00:01',103) and [datDate] < convert(datetime,'01-01-2070',103))
> ) ON [PRIMARY]
> GO
>
> create view view1 as select * from tbl1 union all select * from tbl2 union
> all select * from tbl3 union all select * from tbl4
>
> insert into view1(intId, intSite, datDate)
> values (1 ,1 , convert(datetime, '01-01-1999', 103))
>

Relevant Pages

  • Re: Check constraints not being replicated properly
    ... I DO want to enforce the constraints at the ... DML during replication because of the partitioned view requirements. ... We have a date_created partitioning ... How can I make sure the snapshot does not create the tables on the ...
    (microsoft.public.sqlserver.replication)
  • Re: Umstieg von MySQL auf Oracle: Wo lagen die Probleme?
    ... >> Partielle Indizes gab's IIRC schon als Postgres noch kein SQL konnte, ... >> und Tabellenpartitionierung nennt sich Constraint Exclusion und kann ... Diese Constraints werden in den Kind-Tabellen so gesetzt, ... Oracle's partitioning allows either LIST or RANGE partitioning. ...
    (de.comp.datenbanken.misc)
  • Re: Check constraints not being replicated properly
    ... Do you want this constraint enforced on the subscriber when the DML is ... occurring due to a replication process? ... Please post your schema of the table with the constraints and I'll show you ... We have a date_created partitioning column in ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Replication with only a subset of data at BOTH subscriber and publisher
    ... what you are referring to is called an indexed view, ... Basically they are 2 or more selects with union all ... data have check constraints to ensure partitioning of data. ...
    (microsoft.public.sqlserver.replication)