In my Sql Server 2012 I have the following table in a database
CREATE TABLE [dbo].[HumanResource]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IsHired] [bit] NULL, [Address] [nvarchar](max) NULL, [Score] [bigint] NULL, [LastUpdate] [datetime] NULL, CONSTRAINT [PK_HumanResource] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
when I update the Address field (nvarchar, not null), it is captured as below
NULL 3 2 mahmood True here 111 2014-09-01 14:42:28.913
NULL 4 2 mahmood True there 111 2014-09-01 14:42:28.913
but when I update the Score field (nvarchar, null), this is captured
NULL 3 2 mahmood True NULL 111 2014-09-01 14:42:28.913
NULL 4 2 Mahmood True there 111 2014-09-01 14:42:28.913
**Edit (the update and select queries) :
update HumanResource set Address = 'blah blah blah' where id = 2 update HumanResource set Score = Score + 1 where id = 2 SELECT TOP (10000) __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, ID, Name, IsHired, Address, Score, LastUpdate FROM cdc.dbo_HumanResource_CT
Considering that sql server Update is a chained Delete and Insert, the first row is the delete row capture and the second one is the insert row capture.
But I don't understand why the address is tracked as NULL when I update other fields, I would appreciate any information on this issue.