Microsoft SQL Server 2008 R2 Unleashed- P169:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1624 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 For typical ETL-type applications querying for change data is an ongoing process making periodic requests for all the changes that occurred since the last request which need to be applied to the target. For these types of queries you can use the function to determine the next lowest LSN boundary that is greater than the max LSN boundary of the previous query. To demonstrate this let s first execute some additional data modifications against the MyCustomer table Insert MyCustomer PersonlD StorelD TerritorylD AccountNumber rowguid ModifiedDate Values 20779 null 12 AW1 RIGHT 00000000 convert varchar 8 IDENT_Current MyCustomer 8 NEWID GETDATE delete MyCustomer where CustomerID 30119 The max LSN from the previous examples is 0x000000390000144C0004. We want to increment from this LSN to find the next set of changes. In Listing you pass this value to the to set the min LSN value you ll use with the function as the lower bound. LISTING Using to Return the Net Changes to the MyCustomer CDC Capture Table Since the Last Retrieval --declare variables to represent beginning and ending lsn DECLARE @from_lsn BINARY 10 @to_lsn BINARY 10 -- get the Next lowest LSN after the previous Max LSN SELECT @from_lsn 0x000000390000144C0004 -- get the last LSN for table changes SELECT @to_lsn -- get all changes in the range using all with_merge parameter SELECT FROM @from_lsn @to_lsn all with merge GO __ start_lsn__ operation update_mask CustomerID PersonID StoreID TerritoryID AccountNumber rowguid ModifiedDate Change Data Capture 1625 0X00000039000017D30004 5 NULL 30120 20779 NULL 12 AW00030120 CE8BBAA1-04C0-4A81-9A7E-85B4EDB5C36D 2010-04-27 23 52 CCC0X00000039000017E50004 1 NULL .
