Professional Web Applications Themes

partitioned view doesn't work! - Microsoft SQL / MS SQL Server

Ive just been setting up partitioned views. If you could post your table schema and view I think i might be able to help.  ...

  1. #1

    Default partitioned view doesn't work!

    Ive just been setting up partitioned views.

    If you could post your table schema and view I think i
    might be able to help.
     
    mike Guest

  2. #2

    Default Re: partitioned view doesn't work!

    Thank you for the reply.
    Hereis my mamber table script:

    CREATE TABLE [dbo].[Ledger_0202] (
    [REGDATE] [smalldatetime] NOT NULL ,
    [IDNO] [char] (10) NOT NULL ,
    [CUSTDATE] [smalldatetime] NOT NULL ,
    [AN1000] [char] (7) NULL ,
    [EN2004] [char] (1) NULL ,
    [EN2005] [decimal](8, 0) NULL ,
    [EN2008] [decimal](8, 0) NULL ,
    [EN2009] [char] (1) NULL ,
    [EN2011] [char] (7) NULL ,
    [EN2012] [char] (7) NULL ,
    [RES005] [char] (10) NULL ,
    [RES006] [char] (2) NULL ,
    [RES007] [varchar] (30) NULL ,
    [STARTDATE] [smalldatetime] NULL ,
    [ENDDATE] [smalldatetime] NULL ,
    [LOADDATE] [smalldatetime] NULL ,
    [COUNTID] [varchar] (30) NULL
    ) ON [Data]
    GO

    ALTER TABLE [dbo].[Ledger_0202] WITH NOCHECK ADD
    CONSTRAINT [PK_Ledger_0202] PRIMARY KEY CLUSTERED
    (
    [REGDATE],
    [IDNO],
    [CUSTDATE]
    ) ON [Data] ,
    CONSTRAINT [CK_Ledger_0202] CHECK ([custdate] >= '
    2002 - 2 - 1' and [custdate] <= ' 2002 - 2 - 28')
    GO

    ALTER TABLE [dbo].[Ledger_0202] ADD
    CONSTRAINT [FK_Ledger_0202_ANSOK] FOREIGN KEY
    (
    [REGDATE],
    [IDNO]
    ) REFERENCES [dbo].[ANSOK] (
    [REGDATE],
    [IDNO]
    )
    GO

    We have 30 of these member tables.
    And the view is:

    CREATE VIEW Ledger_All AS
    SELECT * FROM Ledger_0201
    UNION ALL
    SELECT * FROM Ledger_0202
    UNION ALL
    SELECT * FROM Ledger_0203
    --continues--

    Did I miss any important part?
     
    that they actually 
    tables. 
    keys and 
    (see 
    help us to spot the [/ref]
    of [/ref]
    partitioned 
    >
    >
    >.
    >[/ref]
    didi Guest

  3. #3

    Default Re: partitioned view doesn't work!

    didi,

    I initially tried the same sort of thing with no success.

    I discovered that if you do not name your contraint as in
    this script of my table it works.

    Hope this helps


    CREATE TABLE [traffic_200206] (
    [TrafficID] [uniqueidentifier] NOT NULL ,
    [NuaDirectionID] [int] NOT NULL ,
    [MobileID] [int] NOT NULL ,
    [Reference] [bigint] NOT NULL ,
    [AcceptTime] [datetime] NOT NULL ,
    [SubmitTime] [datetime] NOT NULL ,
    [NodeID] [tinyint] NOT NULL ,
    [CompletionTime] [datetime] NULL ,
    [CompletionEventTypeID] [tinyint] NULL ,
    [FailureReasonID] [tinyint] NULL ,
    [DeliveryErrorID] [tinyint] NULL ,
    [SubmitBatchID] [int] NOT NULL ,
    [CompletionBatchID] [int] NOT NULL ,
    [Phantom] [int] NOT NULL ,
    [Good] [bit] NULL ,
    CONSTRAINT [PK_traffic_200206] PRIMARY KEY
    NONCLUSTERED
    (
    [TrafficID],
    [SubmitTime]
    ) ON [PRIMARY] ,
    CHECK ([SubmitTime] >= '01 Jun 2002' and
    [SubmitTime] < '01 Jul 2002')
    ) ON [PRIMARY]
    GO
     
    >that they actually 
    >tables. 
    >keys and [/ref]
    data 
    >help us to spot the [/ref]
    >of [/ref][/ref]
    Server [/ref]
    >partitioned [/ref][/ref]
    PK 
    >>
    >>
    >>.
    >>[/ref]
    >.
    >[/ref]
    Mike Guest

  4. #4

    Default partitioned view doesn't work!


    Here is one of the member tables definition.
    And I have about 30 of them with different check
    constraints
    ******************
    CREATE TABLE [dbo].[Ledger_0201] (
    [REGDATE] [smalldatetime] NOT NULL ,
    [IDNO] [char] (10) NULL ,
    [CUSTDATE] [smalldatetime] NOT NULL ,
    [AN1000] [char] (7) NULL ,
    [EN2004] [char] (1) NULL ,
    [EN2005] [decimal](8, 0) NULL ,
    [EN2008] [decimal](8, 0) NULL ,
    [EN2009] [char] (1) NULL ,
    [EN2011] [char] (7) NULL ,
    [EN2012] [char] (7) NULL ,
    [RES005] [char] (10) NULL ,
    [RES006] [char] (2) NULL ,
    [RES007] [varchar] (30) NULL ,
    [STARTDATE] [smalldatetime] NULL ,
    [ENDDATE] [smalldatetime] NULL ,
    [LOADDATE] [smalldatetime] NULL ,
    [COUNTID] [varchar] (30) NULL
    ) ON [Data]
    GO

    ALTER TABLE [dbo].[Ledger_0201] WITH NOCHECK ADD
    CONSTRAINT [PK_Ledger_0201] PRIMARY KEY CLUSTERED
    (
    [REGDATE],
    [IDNO],
    [CUSTDATE]
    ) ON [Data] ,
    CONSTRAINT [CK_Ledger_0201] CHECK ([custdate] >= '
    2002 - 1 - 1' and [custdate] <= '2002 - 1 - 31')
    GO

    ALTER TABLE [dbo].[Ledger_0201] ADD
    CONSTRAINT [FK_Ledger_0201_ANSOK] FOREIGN KEY
    (
    [REGDATE],
    [IDNO]
    ) REFERENCES [dbo].[ANSOK] (
    [REGDATE],
    [IDNO]
    )
    GO
    *********

    As PK is concatenated I used alter table, which maybe
    causing this problem?

    *********
    And this is the definition of the partitioned view.

    *********
    CREATE VIEW Ledger_All AS
    SELECT * FROM Ledger_0201
    UNION ALL
    SELECT * FROM Ledger_0202
    UNION ALL
    SELECT * FROM Ledger_0203
    --continues to Ledger_0412 table.
    *********

    Can you help me??




     [/ref]
    of [/ref]
    partitioned 
    >.
    >[/ref]
    didi Guest

  5. #5

    Default Re: partitioned view doesn't work!

    Try WITH CHECK instead of WITH NOCHECK.
    Think about it -- you wouldn't want the partition to
    make a false assumption.

    ----

    Not that it matters to this thread,
    realize you instead of adding 2 things then 1 thing
    you could have added 1, 1, 1
    (or all 3 three at once if you really prefer "doubling" up).

    Bye,
    Delbert Glass

    "didi" <com> wrote in message
    news:1d5201c360d2$8f3527d0$gbl... 
    > that they actually 
    > tables. 
    > keys and 
    > (see 
    > help us to spot the [/ref]
    > of [/ref]
    > partitioned 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Delbert Guest

  6. #6

    Default Re: partitioned view doesn't work!

    never use EM to tweak partitioned views -- you are asking for trouble. there
    is an article in SQL Mag (by Itzik Ben-Gan) that shows why. try to use
    strict inequality (<) instead of inclusive and use more portable datetime
    literals like this:

    ALTER TABLE Ledger_0201 ADD
    CONSTRAINT CK_Partition_Ledger_0201
    CHECK ([custdate] >= '20020101' and [custdate] < '20020201')

    in your impl custdate of '20020131 13:45' has no table to be partitioned to.
    sure, i suppose you are not storing time part but DB Engine doesn't know
    (and there is no way you can tell it).

    HTH,
    </wqw>

    "didi" <com> wrote in message
    news:1d6301c360d7$303f3630$gbl... [/ref]
    > of [/ref]
    > partitioned 
    > >.
    > >[/ref][/ref]


    Vlad Guest

  7. #7

    Default Re: partitioned view doesn't work!

    Thank you Mile!
    I tried the different way to create member tables as you
    suggested.
    **
    CREATE TABLE [dbo].[Y0202] (
    [REGDATE] [smalldatetime] NOT NULL ,
    [IDNO] [char] (10) NOT NULL ,
    [CUSTDATE] [smalldatetime] NOT NULL ,
    [AN1000] [char] (7) NULL ,
    [EN2004] [char] (1) NULL ,
    [EN2005] [decimal](8, 0) NULL ,
    [EN2008] [decimal](8, 0) NULL ,
    [EN2009] [char] (1) NULL ,
    [EN2011] [char] (7) NULL ,
    [EN2012] [char] (7) NULL ,
    [RES005] [char] (10) NULL ,
    [RES006] [char] (2) NULL ,
    [RES007] [varchar] (30) NULL ,
    [STARTDATE] [smalldatetime] NULL ,
    [ENDDATE] [smalldatetime] NULL ,
    [LOADDATE] [smalldatetime] NULL ,
    [COUNTID] [varchar] (30) NULL,
    CONSTRAINT [PK_Y0202] PRIMARY KEY CLUSTERED
    (
    [REGDATE],
    [IDNO],
    [CUSTDATE]
    ) ,

    CHECK (datepart(year,[custdate])= '2002' and datepart
    (date,[custdate])='02'),
    CONSTRAINT [FK_Y0202_ANSOK] FOREIGN KEY
    (
    [REGDATE],
    [IDNO]
    ) REFERENCES [dbo].[ANSOK] (
    [REGDATE],
    [IDNO]
    )
    ) ON [Data]
    GO
    **
    I made a same tables without using "alter table".

    I made a partitioned view which include these new member
    tables and send a query with custdate condition.
    Like
    Select count (*) from view_all where custdate = '2001-01-
    22'
    I see logical read happened to all member tables and in
    execution I see all table were accessed.

    **
    Mike! It is not working for me.

    Is there anyone who got this partitioned view work ?
    Please contact me!!!




     
    >>that they actually 
    >>tables. 
    >>keys and [/ref]
    >data 
    >>help us to spot the [/ref][/ref]
    key [/ref]
    >Server 
    >>partitioned [/ref]
    >PK 
    >>.
    >>[/ref]
    >.
    >[/ref]
    didi Guest

  8. #8

    Default Re: partitioned view doesn't work!

    it's normal to see all the partitions to participate in the execution plan.
    if SQL Server is creating the plan before knowing the actual values of the
    params it has to include every possibility.

    if you SET STATISTICS IO ON you will see that irrelevant tables are table
    scanned excatly zero times -- this is what the benefit of partitioned views
    is.

    HTH,
    </wqw>

    "didi" <com> wrote in message
    news:1b5301c36174$05fd41d0$gbl... 
    > >data [/ref]
    > key 
    > >Server 
    > >PK 
    > >.
    > >[/ref][/ref]


    Vlad Guest

Similar Threads

  1. FlashVideo playhead doesn't work on first view
    By f.flori@gmail.com in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 10th, 10:48 AM
  2. Updateable partitioned view problem
    By Rekha in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 31st, 01:14 PM
  3. distributed partitioned view??
    By chet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 05:34 AM
  4. Code view doesn't work - DW4 Mac
    By Mac-Test in forum Macromedia Dreamweaver
    Replies: 3
    Last Post: July 12th, 09:33 PM
  5. 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