CDC Guide for Linux, UNIX, and Windows

Back Next

SQL Server Datatypes Supported for CDC

PowerExchange supports most SQL Server datatypes for CDC, with some exceptions.

The following table indicates the SQL Server datatypes that PowerExchange supports and does not support for CDC:

Datatype	Supported for CDC?	Comments
bigint	Yes	-
binary	Yes	-
bit	Yes	-
char	Yes	-
date	Yes	In PowerCenter, when you import source metadata from PowerExchange to create a source definition, PowerExchange converts date columns to timestamp columns. This conversion is for consistency with PowerCenter datatype handling.
datetime	Yes	-
datetime2	Yes	-
datetimeoffset	Yes	PowerCenter treats this datatype as varchar.
decimal	Yes	-
float	Yes	-
geography	No	-
geometry	No	-
hierarchyid	No	-
image¹	No	Use varbinary(MAX) instead.
int	Yes	-
money	Yes	-
nchar	Yes	-
ntext¹	No	Use nvarchar(MAX) instead.
numeric	Yes	-
nvarchar	Yes	-
real	Yes	-
smalldatetime	Yes	-
smallint	Yes	-
smallmoney	Yes	-
sql_variant	No	PowerExchange does not capture change data for sql_variant columns but does capture change data for other columns in the same table.
text¹	No	Use varchar(MAX) instead.
time	Yes	-
timestamp	Yes	-
tinyint	Yes	-
uniqueidentifier	Yes	PowerCenter imports the uniqueidentifier datatype as a varchar datatype of 38 characters.
user-defined datatypes (UDTs)	Yes	PowerExchange treats a UDT in the same way as the datatype on which the UDT is based.
varbinary	Yes	-
varchar	Yes	-
xml	Yes	PowerExchange treats this datatype as varchar(MAX).
1. PowerExchange might not be able to capture change data for columns that have the image, ntext, or text datatype because of SQL Server transactional replication restrictions on these column types. Instead, use the alternative datatypes that Microsoft recommends, as indicated in the Comments column.