Tracking changes on Views.

  • ben.brugman

    SSChampion

    Points: 13285

    Tracking changes in/on Views.

    Are there any good scripts/documents/advises/tips on Tracking changes on Views?

    I asume that I am not the first person to try this.
    Thanks for your time and attention,
    Ben

  • Phil Parkin

    SSC Guru

    Points: 243186

    ben.brugman - Monday, February 26, 2018 6:18 AM

    Tracking changes in/on Views.

    Are there any good scripts/documents/advises/tips on Tracking changes on Views?

    I asume that I am not the first person to try this.
    Thanks for your time and attention,
    Ben

    Do you mean view definition changes, or changes to the data returned by the view?


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this [/url]link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.

  • ben.brugman

    SSChampion

    Points: 13285

    Phil Parkin - Monday, February 26, 2018 6:29 AM

    Do you mean view definition changes, or changes to the data returned by the view?

    Changes in the content which have to be tracked. (Sorry, 🙁 I was not clear about that).
    (When the view definition does NOT change).

    Ben

  • Grant Fritchey

    SSC Guru

    Points: 395157

    Best way to do this is to put your database code into source control. You can track it and version it that way. Further, once you start treating your database as code, you can start to automate deployments since builds from source control allow you to go from a known state.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Triggers on the tables that make up the view (trigger on the view doesn’t fire unless the view itself is the target of the modification)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ben.brugman

    SSChampion

    Points: 13285

    Grant Fritchey - Monday, February 26, 2018 7:33 AM

    Best way to do this is to put your database code into source control. You can track it and version it that way. Further, once you start treating your database as code, you can start to automate deployments since builds from source control allow you to go from a known state.

    Thanks Grant Fritchey.
    Tracking changes of the content. Or changes of the data returned by the view.
    Not the ‘source’ of the view.

    I think that your anwser reveres to changes in the ‘definition’. Or am I misunderstanding your anwser ?
    Thanks for your time and attention,
    Ben

  • Grant Fritchey

    SSC Guru

    Points: 395157

    ben.brugman - Monday, February 26, 2018 7:45 AM

    Grant Fritchey - Monday, February 26, 2018 7:33 AM

    Best way to do this is to put your database code into source control. You can track it and version it that way. Further, once you start treating your database as code, you can start to automate deployments since builds from source control allow you to go from a known state.

    Thanks Grant Fritchey.
    Tracking changes of the content. Or changes of the data returned by the view.
    Not the 'source' of the view.

    I think that your anwser reveres to changes in the 'definition'. Or am I misunderstanding your anwser ?
    Thanks for your time and attention,
    Ben

    Sorry, you said changes in “content” not data or the code. I misunderstood what content meant. Gail has the better answer for this. It’s all about tracking the data in the tables. There is no data in a view. It’s just a query.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ben.brugman

    SSChampion

    Points: 13285

    Sorry for the confusion,  I should have paid more attention to the situation. (English is not my first language, so although I do check the things that I have written. I tend to read what I think, I have written.)

    From another database we get (with an ODBC connection) tables and thereafter the mutations on the tables into a SQLserver database. The source database is not exactly relational. The mutations from the source get processed in the tables (in SQLserver). So at that point we have the tables and the mutations (in tables).

    The tables are not totally ‘logical’ and do not really fit into the ‘model’. So we are using views to create a beter model of the data.
    Persisting the views is easy, just create a tables of the views. But now we want the mutations on the view. So the Inserts/Updates and Deletes needed to have the persisted table to contain the same data as the view. (The updates can also be done with only inserts/deletes).

    As usual this is a simplified representation, everything is done within economical constraints and the usual ‘management’ constraints, it has to be good/cheap/quick. 

    Offcourse; I told the management that we can do good/cheap/quick, but only two of them at the same time. For me this is a challenging puzzle, but I do like that. But do not mind scripts/documents/advises/tips to get a better/faster/cheaper 🙂 result.
    Ben

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Triggers on the tables beneath the views, with logic to determine whether the changes that fired the trigger would affect the views or not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • subramaniam.chandrasekar

    SSCarpal Tunnel

    Points: 4108

    ben.brugman - Monday, February 26, 2018 6:18 AM

    Tracking changes in/on Views.

    Are there any good scripts/documents/advises/tips on Tracking changes on Views?

    I asume that I am not the first person to try this.
    Thanks for your time and attention,
    Ben

    Can’t we create a copy of the view as a table and compare it daily using an user defined function. In user defined function you can check and compare both source and destination tables.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    subramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AM

    ben.brugman - Monday, February 26, 2018 6:18 AM

    Tracking changes in/on Views.

    Are there any good scripts/documents/advises/tips on Tracking changes on Views?

    I asume that I am not the first person to try this.
    Thanks for your time and attention,
    Ben

    Can't we create a copy of the view as a table and compare it daily using an user defined function. In user defined function you can check and compare both source and destination tables.

    Why would you need a UDF for that?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ben.brugman

    SSChampion

    Points: 13285

    subramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AM

    Can't we create a copy of the view as a table and compare it daily using an user defined function. In user defined function you can check and compare both source and destination tables.

    Yes you can,


    SELECT * FROM (
    select ‘REMOVE’ Action, * from Ben_Old_table
    except
    select ‘REMOVE’ Action,* from Ben_New_table
    UNION
    select ‘Insert’ Action,* from Ben_New_table
    except
    select ‘Insert’ Action,* from Ben_Old_table
    ) XXX

    This will give you the changes made to two versions of a table.
    But it is not ‘fast’, and does take up space in the cache, so this does not conform to the requirements of the users.

    Thanks for your suggestion,
    Ben

  • subramaniam.chandrasekar

    SSCarpal Tunnel

    Points: 4108

    GilaMonster - Tuesday, February 27, 2018 1:31 AM

    subramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AM

    ben.brugman - Monday, February 26, 2018 6:18 AM

    Tracking changes in/on Views.

    Are there any good scripts/documents/advises/tips on Tracking changes on Views?

    I asume that I am not the first person to try this.
    Thanks for your time and attention,
    Ben

    Can't we create a copy of the view as a table and compare it daily using an user defined function. In user defined function you can check and compare both source and destination tables.

    Why would you need a UDF for that?

    I was replied to suggest on daily basis, we can create an UDF and proceed for imports if any.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply