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.
Best Answer
Here is the MSDN
Large Object Data Types Columns of data type
image
,text
, andntext
are always assigned aNULL
value when__$operation = 1
or__$operation = 3
. Columns of data typevarbinary(max)
,varchar(max)
, ornvarchar(max)
are assigned aNULL
value when__$operation = 3
unless the column changed during the update. When__$operation = 1
, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value ofNULL
.By default, the maximum size that can be added to a captured column in a single
INSERT
,UPDATE
,WRITETEXT
, orUPDATETEXT
statement is65,536 bytes
or64 KB
. To increase this size to support larger LOB data, use the Configure themax text repl size
Server Configuration Option to specify a larger maximum size. For more information, see Configure themax text repl size
Server Configuration Option.You are using a
nvarchar(max)
. I've got the same problem. I fix it by changing myvarchar(max)
withvarchar(255)
. For an address255
should be enough.Hope this help