Microsoft® SQL Server® 2012 Bible

952

Part VII: Monitoring and Auditing

In this case, the Change Tracking table is HumanResources.Department and the begin-

ning version is 0 :

SELECT *

FROM ChangeTable

(Changes HumanResources.Department, 0 ) as CT;

Result:

SYS

SYS CHANGE SYS SYS SYS

CHANGE CREATION CHANGE CHANGE CHANGE

VERSION VERSION OPERATION COLUMNS CONTEXT DepartmentID

-------- --------- ---------- -------- -------- --------------

2 1 I NULL NULL 17

1 1 I NULL NULL 18

Since version number 0 , two rows have been inserted. The update to row 17 is still reported

as an insert because for the purposes of synchronization, row 17 must be inserted.

If version number 1 is passed to ChangeTable, then the result should show only change

version 2 :

SELECT *

FROM ChangeTable

(Changes HumanResources.Department, 1) as CT;

Result (formatted to include the syschangecolumns data):

SYS

SYS CHANGE SYS SYS SYS

CHANGE CREATION CHANGE CHANGE CHANGE

VERSION VERSION OPERATION COLUMNS CONTEXT DepartmentID

-------- --------- ---------- -------- ---------- --------------

2 1 U 0x0000000002000000 17

NULL

This time row 17 shows up as an update because when version 2 occurred, row 17 already

existed, and version 2 updated the row. A synchronization based on changes made since

version 1 would need to update row 17.

Note that as a table-valued function, ChangeTable must have an alias.

Synchronizing requires joining with the source table. The following query reports the

changed rows from HumanResources.Department since version 1. The left outer join is

necessary to pick up any deleted rows which, by defi nition, no longer exist in the source

table and would therefore be missed by an inner join:

SELECT CT.SYS_CHANGE_VERSION as Version,

CT.DepartmentID, CT.SYS_CHANGE_OPERATION as Op,

d.Name, d.GroupName

c41.indd 952c41.indd 952 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM

http://www.it-ebooks.info