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.
UPDATE
statements instead of describing them? (2) how are you determining "first" and "second"? What query are you using to pull the CDC data? Does it have anORDER BY
?