Professional Web Applications Themes

Updateable partitioned view problem - Microsoft SQL / MS SQL Server

Can I use multiple constraints to partition my tables and if so, can I insert into the partitioned view? Here's my script: --Energy July 2003 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PMO_Energy_07_2003]') and OBJECTPROPERTY (id, N'IsUserTable') = 1) drop table [dbo].[PMO_Energy_07_2003] GO CREATE TABLE [dbo].[PMO_Energy_07_2003] ( [Sample_ID] [bigint] NOT NULL , [Group_Constraint] [int] Check(Group_Constraint = 1) NOT NULL , [Month_Constraint] [int] Check (Month_Constraint = 7)NOT NULL , [Year_Constraint] [int] Check (Year_Constraint = 2003)NOT NULL , [Timestamp_ID] [bigint] NOT NULL , [Timestamp] [datetime] NOT NULL , [msec] [int] NOT NULL , [Device_ID] [bigint] NOT NULL , [Topic_ID] [int] ...

  1. #1

    Default Updateable partitioned view problem

    Can I use multiple constraints to partition my tables and
    if so, can I insert into the partitioned view?

    Here's my script:
    --Energy July 2003
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[PMO_Energy_07_2003]') and OBJECTPROPERTY
    (id, N'IsUserTable') = 1)
    drop table [dbo].[PMO_Energy_07_2003]
    GO

    CREATE TABLE [dbo].[PMO_Energy_07_2003] (
    [Sample_ID] [bigint] NOT NULL ,
    [Group_Constraint] [int] Check(Group_Constraint =
    1) NOT NULL ,
    [Month_Constraint] [int] Check (Month_Constraint =
    7)NOT NULL ,
    [Year_Constraint] [int] Check (Year_Constraint =
    2003)NOT NULL ,
    [Timestamp_ID] [bigint] NOT NULL ,
    [Timestamp] [datetime] NOT NULL ,
    [msec] [int] NOT NULL ,
    [Device_ID] [bigint] NOT NULL ,
    [Topic_ID] [int] NOT NULL ,
    [Sample_Type_ID] [smallint] NOT NULL ,
    [Calculated_Value] [float] NOT NULL ,
    [Original_Value] [float] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[PMO_Energy_07_2003] ADD
    CONSTRAINT [PK_PMO_Energy_07_2003] PRIMARY KEY
    NONCLUSTERED
    (
    [Sample_ID],
    [Group_Constraint],
    [Month_Constraint],
    [Year_Constraint]
    ) ON [PRIMARY]
    GO

    CREATE INDEX [IX_PMO_Energy_07_2003] ON [dbo].
    [PMO_Energy_07_2003]([Timestamp_ID], [Device_ID],
    [Topic_ID], [Sample_Type_ID]) ON [PRIMARY]
    GO


    --Energy Aug 2003
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[PMO_Energy_08_2003]') and OBJECTPROPERTY
    (id, N'IsUserTable') = 1)
    drop table [dbo].[PMO_Energy_08_2003]
    GO

    CREATE TABLE [dbo].[PMO_Energy_08_2003] (
    [Sample_ID] [bigint] NOT NULL ,
    [Group_Constraint] [int] Check(Group_Constraint =
    1) NOT NULL ,
    [Month_Constraint] [int] Check (Month_Constraint =
    8)NOT NULL ,
    [Year_Constraint] [int] Check (Year_Constraint =
    2003)NOT NULL ,
    [Timestamp_ID] [bigint] NOT NULL ,
    [Timestamp] [datetime] NOT NULL ,
    [msec] [int] NOT NULL ,
    [Device_ID] [bigint] NOT NULL ,
    [Topic_ID] [int] NOT NULL ,
    [Sample_Type_ID] [smallint] NOT NULL ,
    [Calculated_Value] [float] NOT NULL ,
    [Original_Value] [float] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[PMO_Energy_08_2003] ADD
    CONSTRAINT [PK_PMO_Energy_08_2003] PRIMARY KEY
    NONCLUSTERED
    (
    [Sample_ID],
    [Group_Constraint],
    [Month_Constraint],
    [Year_Constraint]
    ) ON [PRIMARY]
    GO

    CREATE INDEX [IX_PMO_Energy_08_2003] ON [dbo].
    [PMO_Energy_08_2003]([Timestamp_ID], [Device_ID],
    [Topic_ID], [Sample_Type_ID]) ON [PRIMARY]
    go




    --Demand July 2003
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[PMO_Demand_07_2003]') and OBJECTPROPERTY
    (id, N'IsUserTable') = 1)
    drop table [dbo].[PMO_Demand_07_2003]
    GO

    CREATE TABLE [dbo].[PMO_Demand_07_2003] (
    [Sample_ID] [bigint] NOT NULL ,
    [Group_Constraint] [int] Check(Group_Constraint =
    2) NOT NULL ,
    [Month_Constraint] [int] Check (Month_Constraint =
    7)NOT NULL ,
    [Year_Constraint] [int] Check (Year_Constraint =
    2003)NOT NULL ,
    [Timestamp_ID] [bigint] NOT NULL ,
    [Timestamp] [datetime] NOT NULL ,
    [msec] [int] NOT NULL ,
    [Device_ID] [bigint] NOT NULL ,
    [Topic_ID] [int] NOT NULL ,
    [Sample_Type_ID] [smallint] NOT NULL ,
    [Calculated_Value] [float] NOT NULL ,
    [Original_Value] [float] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[PMO_Demand_07_2003] ADD
    CONSTRAINT [PK_PMO_Demand_07_2003] PRIMARY KEY
    NONCLUSTERED
    (
    [Sample_ID],
    [Group_Constraint],
    [Month_Constraint],
    [Year_Constraint]
    ) ON [PRIMARY]
    GO

    CREATE INDEX [IX_PMO_Demand_07_2003] ON [dbo].
    [PMO_Demand_07_2003]([Timestamp_ID], [Device_ID],
    [Topic_ID], [Sample_Type_ID]) ON [PRIMARY]
    GO


    --Demand Aug 2003
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[PMO_Demand_08_2003]') and OBJECTPROPERTY
    (id, N'IsUserTable') = 1)
    drop table [dbo].[PMO_Demand_08_2003]
    GO

    CREATE TABLE [dbo].[PMO_Demand_08_2003] (
    [Sample_ID] [bigint] NOT NULL ,
    [Group_Constraint] [int] Check(Group_Constraint =
    2) NOT NULL ,
    [Month_Constraint] [int] Check (Month_Constraint =
    8)NOT NULL ,
    [Year_Constraint] [int] Check (Year_Constraint =
    2003)NOT NULL ,
    [Timestamp_ID] [bigint] NOT NULL ,
    [Timestamp] [datetime] NOT NULL ,
    [msec] [int] NOT NULL ,
    [Device_ID] [bigint] NOT NULL ,
    [Topic_ID] [int] NOT NULL ,
    [Sample_Type_ID] [smallint] NOT NULL ,
    [Calculated_Value] [float] NOT NULL ,
    [Original_Value] [float] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[PMO_Demand_08_2003] ADD
    CONSTRAINT [PK_PMO_Demand_08_2003] PRIMARY KEY
    NONCLUSTERED
    (
    [Sample_ID],
    [Group_Constraint],
    [Month_Constraint],
    [Year_Constraint]
    ) ON [PRIMARY]
    GO

    CREATE INDEX [IX_PMO_Demand_08_2003] ON [dbo].
    [PMO_Demand_08_2003]([Timestamp_ID], [Device_ID],
    [Topic_ID], [Sample_Type_ID]) ON [PRIMARY]
    go


    --Create View
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[vw_Sample_Data]') and OBJECTPROPERTY
    (id, N'IsView') = 1)
    drop view [dbo].[vw_Sample_Data]
    GO

    --use the four part name for the view in case it is ever a
    distributed partitioned view
    CREATE VIEW vw_Sample_Data AS
    SELECT * FROM
    LABPC68.SMS_ONDEMAND.dbo.PMO_Demand_07_2003
    UNION ALL
    SELECT * FROM
    LABPC68.SMS_ONDEMAND.dbo.PMO_Demand_08_2003
    UNION ALL
    SELECT * FROM
    LABPC68.SMS_ONDEMAND.dbo.PMO_Energy_07_2003
    UNION ALL
    SELECT * FROM
    LABPC68.SMS_ONDEMAND.dbo.PMO_Energy_08_2003


    --test insert
    --insert into vw_sample_data values(1, 2, 7, 2003,
    1, '8/1/2003 12:00:00 am', 0, 1, 1, 0, 1, 3)

    Thanks
    Rekha Guest

  2. #2

    Default RE: Updateable partitioned view problem

    Hi Rekha,,

    My name is Michael and I would like to thank you for using Microsoft
    newsgroup.
    Currently, I am performing some initial research. I will update you later.
    In the mean time, if you have any other useful information, please feel
    free to let me know.

    Regards,

    Michael Shao
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.

    Michael Guest

  3. #3

    Default Re: Updateable partitioned view problem

    Hello!

    It seems that you need INSTAED OF INSERT trigger on your view which
    will route DML operation into the appropriate table.
    E.g.
    CREATE TRIGGER Ins_vw_Sample_Data
    ON vw_Sample_Data
    INSTEAD OF INSERT
    AS
    BEGIN
    INSERT INTO LABPC68.SMS_ONDEMAND.dbo.PMO_Demand_07_2003 (... column
    list ...)
    SELECT ... values list ...
    FROM inserted
    WHERE ... PMO_Demand_07_2003 condition ....
    ......
    INSERT INTO LABPC68.SMS_ONDEMAND.dbo.PMO_Energy_07_2003 (... column
    list ...)
    SELECT ... values list ...
    FROM inserted
    WHERE ... PMO_Energy_07_2003 condition ....
    END
    GO 
    Pavel Guest

  4. #4

    Default Re: Updateable partitioned view problem

    A partitioned view can only have one partitioning column. Try combining
    year, month and group into a single column.

    CREATE TABLE [dbo].[PMO_Energy_07_2003] (
    [Sample_ID] [bigint] NOT NULL ,
    [Partition_ID] [int] NOT NULL default(20030701) check (Partition_ID =
    20030701),
    ....
    PRIMARY KEY (Sample_ID, Partition_ID)
    )

    "Rekha" <com> wrote in message
    news:012e01c356c7$cac22e70$gbl... 


    Anthony Guest

Similar Threads

  1. Replies: 5
    Last Post: October 6th, 11:43 PM
  2. distributed partitioned view??
    By chet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 05:34 AM
  3. Partitioned View Question
    By Jane Kelly in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 12th, 11:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139