February 26, 2018 at 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
February 26, 2018 at 6:29 am
ben.brugman - Monday, February 26, 2018 6:18 AMTracking 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 26, 2018 at 6:52 am
Phil Parkin - Monday, February 26, 2018 6:29 AMDo 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
February 26, 2018 at 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2018 at 7:42 am
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
February 26, 2018 at 7:45 am
Grant Fritchey - Monday, February 26, 2018 7:33 AMBest 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
February 26, 2018 at 8:34 am
ben.brugman - Monday, February 26, 2018 7:45 AMGrant Fritchey - Monday, February 26, 2018 7:33 AMBest 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2018 at 9:36 am
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
February 26, 2018 at 9:47 am
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
February 27, 2018 at 1:11 am
ben.brugman - Monday, February 26, 2018 6:18 AMTracking 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.
February 27, 2018 at 1:31 am
subramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AMben.brugman - Monday, February 26, 2018 6:18 AMTracking 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,
BenCan'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
February 27, 2018 at 1:43 am
subramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AMCan'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
February 27, 2018 at 2:05 am
GilaMonster - Tuesday, February 27, 2018 1:31 AMsubramaniam.chandrasekar - Tuesday, February 27, 2018 1:11 AMben.brugman - Monday, February 26, 2018 6:18 AMTracking 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,
BenCan'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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply