sql - To find updated column list and data using CDC -

i creating setup maintain table historical data using cdc. below script have written:

create table dbo.name (id int not null primary key clustered identity(1,1), col1 nvarchar(50) not null constraint df_col1 default 'unknown', col2 nvarchar(50) not null constraint df_col2 default 'unknown', col3 nvarchar(50) not null constraint df_col3 default 'unknown', col4 nvarchar(50) not null constraint df_col4 default 'unknown', col5 nvarchar(50) not null constraint df_col5 default 'unknown', createddate datetime not null default(getdate()), modifieddate datetime  ) go  exec sys.sp_cdc_enable_db go exec sys.sp_cdc_enable_table @source_schema = n'dbo', @source_name = n'name', @capture_instance = 'name',  @supports_net_changes = 1,  @role_name = null go  insert dbo.name  values('a','b','c','d','e',getdate(),null), ('f','g','h','i','j',getdate(),null), ('k','l','m','n','o',getdate(),null) go  select * cdc.name_ct  update name set col1 = col1 + '_u', modifieddate = getdate() id = 1  update name set col2 = col2 + '_v', modifieddate = getdate() id = 2   update name set col3 = col3 + '_a', col4 = col4 + '_b', modifieddate = getdate() id = 3 go  select * cdc.name_ct 

the above script return values of columns data has changed. looking below output i.e. column fieldname contain column list data updated , value column contain previous , new value. createddate value become startdate of row before updation , modifieddate become enddate of before update row , startdate of after update row.

id    fieldname    value    startdate                 enddate ================================================================= 1     col1                2014-08-18 15:56:08       2014-08-18 15:59:44 1     col1         a_u      2014-08-18 15:59:44       null 2     col2         g        2014-08-18 15:56:08       2014-08-18 15:59:44 2     col2         g_v      2014-08-18 15:59:44       null 3     col3         m        2014-08-18 15:56:08       2014-08-18 15:59:44 3     col3         m_a      2014-08-18 15:59:44       null 3     col4         n        2014-08-18 15:56:08       2014-08-18 15:59:44 3     col4         n_b      2014-08-18 15:59:44       null 

i checked query in table , able result same rows updated multiple times.

with cte_cdc (select id,fieldname,fieldvalue,startdate=createddate,enddate   =modifieddate  ( select id,col1,col2,col3,col4,col5,createddate,modifieddate  cdc.mssqlserver2012_ct ) unpivot ( fieldvalue fieldname in (col1,col2,col3,col4,col5) ) upt)   select distinct a.id,a.fieldname,a.fieldvalue,isnull(a.enddate,a.startdate) startdate,b.enddate cte_cdc join cte_cdc b on a.id=b.id , a.fieldname=b.fieldname , a.fieldvalue<>b.fieldvalue order 1,2  id  fieldname   fieldvalue  startdate   enddate 1   col1      2014-08-20 15:09:40.560 2014-08-20 15:11:34.863 1   col1      2014-08-20 15:09:40.560 2014-08-20 15:12:46.117 1   col1      2014-08-20 15:09:40.560 2014-08-20 15:18:15.973 1   col1    a_u 2014-08-20 15:11:34.863 null 1   col1    a_u 2014-08-20 15:11:34.863 2014-08-20 15:12:46.117 1   col1    a_u 2014-08-20 15:11:34.863 2014-08-20 15:18:15.973 1   col1    a_u_uv  2014-08-20 15:12:46.117 null 1   col1    a_u_uv  2014-08-20 15:12:46.117 2014-08-20 15:11:34.863 1   col1    a_u_uv  2014-08-20 15:12:46.117 2014-08-20 15:18:15.973 1   col1    a_u_uv_uvx  2014-08-20 15:18:15.973 null 1   col1    a_u_uv_uvx  2014-08-20 15:18:15.973 2014-08-20 15:11:34.863 1   col1    a_u_uv_uvx  2014-08-20 15:18:15.973 2014-08-20 15:12:46.117 2   col2    g   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877 2   col2    g_v 2014-08-20 15:11:34.877 null 3   col3    m   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877 3   col3    m_a 2014-08-20 15:11:34.877 null 3   col4    n   2014-08-20 15:09:40.560 2014-08-20 15:11:34.877 3   col4    n_b 2014-08-20 15:11:34.877 null 

if still not getting, cross check tracking table weather populating or not each update , verify arguments enabling cdc table.