SQL Server Performance

Partitioned View Not updatable ???

Discussion in 'Performance Tuning for DBAs' started by seanlo@jdg.co.za, Dec 20, 2005.

  1. seanlo@jdg.co.za New Member

    Hi There

    Ok i have searched on this error and seems to be a bug.
    All i need to know is can this be done or not, am i making a mistake?

    I have created 2 tables.
    --------------------------------------------------------------
    Table_A_Jan (
    Col1 int not null,
    Col2 datetime not null,
    Col3 char(5) not null,
    CHECK (DATEPART(mm, Col2) = 1),
    CONSTRAINT [PK] PRIMARY KEY CLUSTERED
    ([Col1], [Col2], [Col3]) WITH FILLFACTOR = 95 ON [PRIMARY]
    )

    Table_A_Feb (
    Col1 int not null,
    Col2 datetime not null,
    Col3 char(5) not null,
    CHECK (DATEPART(mm, Col2) = 2),
    CONSTRAINT [PK] PRIMARY KEY CLUSTERED
    ([Col1], [Col2], [Col3]) WITH FILLFACTOR = 95 ON [PRIMARY]
    )
    --------------------------------------------------------------
    The rules for updatable view, check constraint, part of primary key, no overlapping values etc all seem to be met.

    I then created the view

    CREATE VIEW A
    AS
    SELECT * FROM Table_A_Jan
    UNION ALL
    SELECT * FROM Table_A_Feb

    When i then try to inert into the view:
    INSERT INTO A
    (Col1, Col2, Col3)
    VALUES (45, '2006-01-21 23:55:34.000', '00073')

    I get the following error:
    UNION ALL view 'A' is not updatable because a partitioning column was not found.

    I am not sure what i have done wrong as there is a partitioned column (Col2), and from books online i seem to adhere to the updatable rules.

    Like i said searches on the net seem to point to a bug, can or can this not be done ?

    Thanx
  2. satya Moderator

    It cannot be done. See here from BOL:


    If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:

    The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.

    select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

    The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:
    CREATE VIEW NoTable AS
    SELECT GETDATE() AS CurrentDate,
    @@LANGUAGE AS CurrentLanguage,
    CURRENT_USER AS CurrentUser


    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. seanlo@jdg.co.za New Member

    Hi Satya

    Ok i still dont get it what is wrong with my situation i seem to adhere to the criteria.
    What am i doing wrong thats makes the view not updatable?

    "If a view does not have INSTEAD OF triggers, or if it is not a partitioned view"

    This is a partitioned view or is it not ?

    Thanx
  4. Luis Martin Moderator

  5. mmarovic Active Member

    It looks like it is privileges on remote server problem as described in an article referenced by Luis.
  6. seanlo@jdg.co.za New Member

    Hi Guys

    Satya your first link only applies to distributed partitioned views , this is a local partitioned view, the second link applies to sql server 2005 this is sql server 2000.

    Do you know specifically what is wrong with my situation? I mean am i missing something local paritioned view should be updatable especially when it is such a simple setup ??

    Thanx
  7. mmarovic Active Member

    Not sure that's the reason, but I have partitioning column as a first column of primary key and it works fine.
  8. seanlo@jdg.co.za New Member

    Thanx mmarovic

    Worth trying but i still get the same error.
    It must be something obvious it is as simple partitioned view and partitioned tables.
    I cant believe that the view cannot be udatable ?
  9. mmarovic Active Member

    Actually I must say I had partititioning column... I don't work for that company anymore and we did it a few years ago. Maybe someone with recent experience could help.
  10. seanlo@jdg.co.za New Member

    Satya i still dont understand why you say it is not possible, sorry to be a pain but could you perhaps explain in your own words what makes my example not updatable ?

    Thanx
  11. seanlo@jdg.co.za New Member

    Hi Everyone

    Ok i resolved the issue.
    Problem was the check constraint.

    CHECK (DATEPART(mm, Col2) = 1), this does not explicitly check Col2 rather an expression.

    This check constraint works:
    CHECK (ACCTG_CLOSE_DT = cast('20050201' as datetime)), as it explicitly references Col2.

    Not sure if this is what Satya meant, but anyway thats how i got it to work.

    Thanx for the feedback.
  12. satya Moderator

    Appreciate your feedback on the resolution.

    There are several items to note. First, a Check Constraint is required. The Query Optimizer uses the Check to determine on which server the data should be located. The check must allow data to be directed to one, and only server. For a view to be updatable, all the columns included in the primary key must be returned.

    For a complete list of view rules, see BOL "partitioned views."

    I guess thishttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179706&SiteID=1 could be one of yours too.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. berhanu New Member

    I have veiws called Ledger and this views has some tables which it uses and when i am inserting a data which are in the table but not in the views it say "Ledger(views) is not updatble because a column of the derived table is derived or constant.

Share This Page