Video: SQL Server 2008 - Change Tracking

Presenter:Mike Taulty

SQL Server 2008 has two facilities for monitoring changes to a table to facilitate (e.g.) loading of a data warehousing or building an online/offline application with syncronisation capabilities.In this video we take a look at "Change Tracking" which provides notification of net changes made to data during a period of time and is a good basis for building of an application requiring one or two way synch.

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video


Minute 0

Hello and welcome to short video to Conservatives as an innate money is my told him however Microsoft in the UK with a feature secret is as legal change tracking essentially what this allows you to do is switch on change tracking thread table and then at the point where you make any changes to that table I need to make changes to that table we will write change tracking records am secretly off into the database elsewhere doesn t modify your table in any way and you can later come back and we have functions which allow you to find out the list of changes that have been made to that table since some previous point in time that you can control at Italy are as simple as that it s great for building synchronised application so the canonical example from the would be something like outlook where you know server side e mails arriving in changing client side e mails arriving in change and you have multiple clients and you need to somehow sync between the various devices that use tax is still to undergo a

Minute 1

Is in sequel server 2008 carries that a change tracking how it muses functions to see the changes have been made to data time here in single server that is as the latest of November 60 p a sitting of the new query window and here and there more greater database such as go ahead and create database record a video deeply and let s just go out and put ourselves into a database without the first hour says you have to switch change tracking arm for a particular database of so the way we can see that we can go and select as a function called change tracking current version just candidates most wide and we can see at the moment that it s Nile but if we go and enable change tracking from this database which it is your altar database and cassette change tracking

Minute 2

s track tracking vehicles on the two parameters we can set here change retention calendar want to keep the change tracking data forms were so one day in my case which will be very short period of time and whether or not this is one of the state about as well as anybody keep change data for one day which would mean that any climate have to sink at this one today in order to make this work but now select the change tracking current version which reduces 90 we have a current village within the database let s now go and create ourselves tables or greater table here my table and will put answers and ideally will have an identity that is my primary key and you need a primary key for change tracking to work will seem why in the second but you need a priority and that is that one column on his forgot my call make and have char 100

Minute 3

Regret that table with ASEAN table Nella Scott has become change tracking flat tables in the concern of the table my table enable change tracking this gondola celeriac change tracking flat table and we still at the point where the current version of trade change tracking in the databases zero we can also now use another function which is change tracking main valid version and we can pass as an object ID and we ll give it our table to score my table and within in valid version of right table is zero among a list about tableland insert a record into its or just go ahead and scenes at my table values one such a system of single row into ups which enter them in the table rack

Minute 4

So there was but one record in our table we can now go back up here and that s selected the change tracking current version notice has been implemented if I m in increment every time we make her a modification to the table that has changed tracking switch dancers in the database why we can also go and see the whistle at the minimum version here is what it is giving us is this is the only surgeon we can possibly use of this table and this is the current version of the whole database and any roles that exist between here and here century changes that we would be interested to how would we don t query those changes don t come at the bottom of script is we can select using a function to let so select staff wrong as the new function cup change table we can better a couple of different things here with an ass of the changes to our table call my table and we need to give it the previous version that we ve got banging away to start from is what would it here is with this girl as

Minute 5

Act that little coarse paste in here and will declare ourselves some integer that always previous version that is to sign in value in little over an hour news Valley here pre version never had a radius of stable soldiers they listen and will go in Rhondda to you can say that since we since the previous version which roses the minimum version possible right now there has been an insert as for the eye stands for and over here you ll see that there is a column called ID with a value of one that essentially the primary key values we can go and select from the Ceti ID that earlier is the primary key value where we have had their change at Iraq to staff one second the other guy we might be very interested in at the moment would be this change operation and I also be interested in this change version to a

Minute 6

Own handsets change operation that RCTs since change version and associated your ID must select the three things and letter spaces as we can all too is going on face an hour ago we select so very I was saying at the moment ID one has had an insert since the last time we am did anything with this table which Rose was the minimum valid version and is available but put some more records into table saw this at my table values to pounds three councillors are now in our table and I do not find what you want to do is synchronise this data from the server wallet as you what you want to do is you want to get all the data from that table so that said you had to be selected from my table and season enough but you are also undergo away with

Minute 7

In an current version number the database so that you would not have been any changes when he came back so he will properly want to say let s declare ourselves version number is an integer of some sort and let s select version number equals and we said it was change tracking current version number and you want to make sure that whatever date you selected here was in line with what date you selected here and the press wearily to do that for this is the user a snapshot transaction in order to make it work so I will do is we ll set transaction at an excuse me isolation level the snapshot and begin a transaction will commit at that point and will select here the version number that we got back that is if we manage to get any of that rice

Minute 8

Readily my database to alter this database corruption and drug equals one under one as regards harsh isolation switch on here but it is again so what we die to synchronise her initial three rows of the data from this database and then we know the previous version that will need to know about when we come back and synchronising the future is five okay so we go away where clients and you know maybe a half a day passes we want to be synchronise again so what we would do is to come along and we will declare a mezzo previous version into journalist Mickey was five subtle decline were remembered in the meantime it will make some changes to data

Minute 9

Lets them insert my table values for certain insert let s update my table that set and I think it was called Mike hole equals new about where I d equals to suss an update and I still eat my table where I do fostering that suddenly it certain changes are The server side whereas we are version 5 is what we were going to it is once again if you want to the synchroniser ourselves we would gold and done your transaction we were once again want to select in fact we can be used ago that we had previously a guest of Cisco and in a previous versions five as the scrollable user code so I do here is we will declare ourselves previous version integer that ll remember from the last time we did this and then what we want is to find any changes that happened to the data since we

Minute 10

Last year and so what we would do is we wouldn t use the current version of the maximum value and service select star from my table is rather simplistic waited at the first time around what we want to do is we want to go into change table but if any changes to my table based on the previous version and after show is all the changes have happened since a previous version and so once again at the end of this will remember the version number of the next time such that we can synchronise again we wrap the whole thing in a snapshot transaction or sorry forgot to them in his letter states that gets under our case we can see that since we were an master the database they has an insert update and delete and those that affect records for three and two respectively and so we could now go and find that data by joining back to the main table

Minute 11

And getting the latest version out of there would want to do that to the same transactions of we made sure the beast is influencing lives will be that of a mess we can see the next family come to the database we should be using this value of eight in order to fear then what would be now the previous versions Will be using item 8 and of course because it not made any changes don t be anything there at the moment and that s what we do next time and in the doorway with another version number which Vulgate again but will change over time and we come back and synchronise again okay so just one other thing here there is another way we can use this change table function and it really relates to the idea where our client is gone away and updated a row and it is worried quite naturally that the same role as an updated server side I some other client in the meantime to have an update conflict and what we can do is we can use this change table function such to select staff and change table again

Minute 12

And ask if the changes in our subversion and we given the table we give it to the primary key values so am we tell at the primary Key s idea and the value that safer is out of value of the row one I can select that thank and we have to read it again and then we can see the current version of this thing is one sort that allows us to do is build an update statement that checks if we gun away from the database in our case with the previous version 8 and let s imagine we wanted to change the value of this role number one we can do it if they update my table with you so set out my column equals in some new value doesn t really matter and from my table but were empty and has joined that this is one way of doing it up anywhere to get this done it to change they will version my table and ID the primary key value it is one here has got seat he must join on empty eyed Eagles

Minute 13

TRT and let s say where CD sys change version is less than or equal to whatever the previous change version was that we left the database lives of residents if we left with eight like the show before there was revealed to update that and that works fine however as we now did the update today because has been an update now which will be nine then that Simon will affect any rose any more and we can detect that a change conflict and presumably get a user involved would try to resolve and the differences between the two the thing to say is that one of easily get in the change table that is copy that either code that one is included in the change table with massive changes and that is gone put them eight in there again and secure system that is you do get this idea off the change columns to one of the options you got when you switch on change tracking is whether you want the columns reported if you put that on them until he makes an update you can use a bit mask

Minute 14

Function to identify which columns have been changes might help you with resolving things up a conflict because you can say to the user pay and you made some changes as are the users made some changes but actually they re different so it might not matter in my matting and paying what you want it to fear whether this those other two uses and the version syntax allows you to find out about a specific role based on a specific primary key gate is a quick summary of what the senior change tracking and enables us to capture changes made to tables change tracking data is written at the point where your DML statement are committed to the database it on the tax net changes so that the value goes from a to B to see and you queried at a uneven comeback and query at sea you will not be able to see me because that s and intermediate changes not the net change they only get see the net changes need to develop database lovingly to switch it on a table level we then have this

Minute 15

In stable function we can walk up to effectively say tell me about all the changes to this table since a previous point in time it will return to us whether that and particular rowers have been inserted updated deleted and it will give us the primary key of those roads we can join back to the original table fun at what s going on we also have the other use of the change table function which tells us these specific role version is like adding a roll version to a table so that we can tell whether the role has then updated client and server side at the same time and is also a function making use and a colony can use to determine which of the columns in the source table were updated as part of an update to this is a nice frame of Irish framework forums starting to build and synchronise applications where we can have two way synchronisation between client and server if you want more information on developing sequel server and the developers enter MSDN it s anything Microsoft com sequel is or a

Minute 16

An all purpose data access centre at data and if you are more information on this video feel free to mail me at my e mail address is just on screen other than that of ex lover listening given it useful and hope to catch up with you I do virtually all in person at a UK event in the future

Click here to go to original video page