问题描述
我在源事务表中使用标识列或日期时间列从事务系统加载数据仓库时实现了增量检测。当下次需要提取数据时，在提取查询的过滤器中使用上次提取的最大日期时间值来标识新记录或更改的记录。这足够好，除非在同一毫秒内有多个事务。
I have implemented delta detection while loading data warehouse from transaction systems using an identity column or date-time column in source transaction tables. When data needs to be extracted next time, the maximum date-time value extracted last time is used in the filter of extraction query to identify new or changed records. This was good enough except when there were multiple transactions at the same milli second.
但是现在我们有了带有SQL Server 2008的Change Data Capture（CDC），它提供了一个新的称为LSN（日志序列号）的东西，它是长度为10的二进制文件。现在我很困惑。应该存储哪些数据用于开窗，LSN或日期时间。当然，LSN消除了在大型交易表中存储其他日期时间值的需求，但这有什么缺点吗？我应该使用哪一个？我觉得，将LSN映射到日期时间，然后再存储日期时间不是一个可靠的方法。您的意见是什么？
But now we have Change Data Capture (CDC) with SQL Server 2008 and it provides a new stuff called LSN (Log Sequence Number) which is binary of length 10. Now I am confused. Which data should be stored for windowing purpose, the LSN or the date-time. Of course LSN eliminates the need for storing additional date-time values in large transaction tables, but does this have any disadvantages? Which one should I use? I feel, the mapping of LSN to date-time and then storing date-time is not a reliable method. What is your opinion?
PS：致非BI专业人士，对不起。
PS: To, non-BI professionals, Sorry.
推荐答案
经过大量等待，我在这里看不到任何进一步的答案。我在当前项目中使用LSN进行开窗，我发现它比日期时间值更好，因为它更精确，过程也很简单。我建议使用LSN。如果有人不同意，请让我知道...
After a lot of wait I don't see any further answers here. I have used LSN in my current project for windowing and I find it better than date time values as it is more precise and the process is simple. I recommend using LSN. If anyone out there disagree, please let me know...
这篇关于在带有SSIS的SQL Server CDC中，应存储哪些数据用于开窗（LSN或日期）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！