SQL Server Performance

Views and Table Definition

Discussion in 'SQL Server 2005 Analysis Services' started by sql_jr, Nov 15, 2007.

  1. sql_jr New Member

    There seems to be some problems with SQL Server 2005. Its driving me nuts.
    And what I can figure out till now is if there is a change in the table definition(added a column) then you need to recompile the view dependent on that table (if you are using select *), otherwise you will witness bizarre problems.
    (column not found even though it was there). I added a column in the fact table and ran the update query (didn't rcompile the view), this query updated 4 million records and set all the values to 0 (it took more than half an hour).
    And then instead of using the view I changed it to fact, it ran in couple of seconds updated 22k records and set the right value.And factLeverage also failed. I spent half an hour but couldn't find any reason. It was showing PK duplication error though there were no duplicate records in the recordset (I have even ran the select distinct * from #tmp statement to check the count). Though finally it worked after adding distinct clause (that’s redundant).
    I am not sure whether the problem is related to using "select *" in the views, though I believe it used to run fine in 2000. If you define the column names then you have to recompile anyway.Now, I am little afraid whether its updating the data properly esp. where we have modified the table definitions.
    Anybody has got any clues about this or heard of something related to this?Anyway I am going to modify my all the views and use the column names.
  2. sudiptakr New Member

    I reported this problem to Microsoft.. but yet not get any update. this is a real problem
  3. FrankKalis Moderator

    Since you are speaking of fact tables, I suppose you're talking about Analysis Services. Therefore I moved this thread to the dedicated forum.
  4. sql_jr New Member

    Thanks all for replies. So sudiptakr , you have experienced this behavior before? Is the workaround to recompile all views and procs?

  5. satya Moderator

    You haven't clarified our question whether you are talking about Analysis services or Relational database engine?
  6. sql_jr New Member

    Sorry! Actually, we are talking about a data warehouse, but this is a relational database - NOT AS.
    Can you answer now? THX!
  7. satya Moderator

    Yes this should be clear whether you are trying to do in Analysis services or SQL Server.
    By default whenever a schema change occurs for any of the objects referenced by a batch, the batch is recompiled. In addition to the above few operations flush the plan cache entries that refer to a particular database, and cause fresh compilations afterwards such as:
    When a view is created with CHECK OPTION, the plan cache entries of the database in which the view is created is flushed.
    Adding or dropping columns to a table or view
    Adding or dropping constraints, defaults, or rules to/from a table

Share This Page