Microsoft SQL Server 2008 has introduces new feature for data change tracking on tables called CDC (Change Data Capture). below are steps to enable change tracking on specific table "dbo.Users"
1) First enable CDC on database using below syntax:
EXEC sys.sp_cdc_disable_db
it will create two jobs
- cdc.DatabaseName_capture
- cdc.DatabaseName_cleanup
2) Enable CDC on table "dbo.Users"
IF NOT EXISTS (SELECT Name FROM sys.tables WHERE Name = 'Users' AND is_tracked_by_cdc = 1)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Users',
-- @captured_column_list = N'[UserID], [Name], [IsDeleted]',
@role_name = NULL
END
it will create below objects in database
- Tracking table cdc.dbo_Users_CT
- Table-valued Functions
cdc.fn_cdc_get_all_changes_dbo_Users - Returns one row for each change applied to the source table within the specified log sequence number (LSN) range. If a source row had multiple changes during the interval, each change is represented in the returned result set.
cdc.fn_cdc_get_net_changes_dbo_Users - Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.
3) Finish CDC Enabled on dbo.Users table. Now, You can get changes on "dbo.Users" table using below syntax
select * from cdc.dbo_Users_CT
DECLARE @Begin_lsn BINARY(10);
DECLARE @End_lsn BINARY(10);
DECLARE @Operation_Time DATETIME=Getdate();
DECLARE @LastUpdatedDate DATETIME=Getdate()-2
SET @Begin_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@LastUpdatedDate);
SET @End_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@Operation_Time);
SELECT * FROM Cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_lsn,@End_lsn,'all')