As shown below, Connect CDC supports distribution of most MS SQL Server datatypes. In the tables of supported datatypes in the following pages, the Numeric Length column shows the maximum supported number of numeric digits, while Display Length is the maximum number of characters, or the numeric length plus an included sign, if any.
An asterisk (*) means the length is taken from the column definition in the DBMS catalog. For decimal numeric types, the length is the number of positions to the left of the decimal point. For example, a NUMERIC(10,2) column has a length of 8.
|
Datatype |
Category |
Numeric Length |
Display Length |
|---|---|---|---|
|
DATE |
Date/Interval |
|
10 |
|
TIME a |
Date/Interval |
|
8-16 |
|
DATETIME2 |
Date/Interval |
|
19-27 |
|
DATETIMEOFFSET |
Date/Interval |
|
26-34 |
|
BIGINT |
Integral Numeric |
19 |
20 |
|
BINARY bc |
Binary |
|
* |
|
BIT |
Integral Numeric |
1 |
1 |
|
CHARACTER, CHAR |
Character |
|
d |
|
DATETIME e |
Date/Interval |
|
20 |
|
DEC, DECIMAL |
Decimal Numeric |
* |
|
|
DOUBLE PRECISION |
Approximate Numeric |
|
|
|
FLOAT |
Approximate Numeric |
|
|
|
IMAGE bcf |
Long Binary |
|
|
|
INT, INTEGER |
Integral Numeric |
10 |
11 g |
|
MONEY |
Decimal Numeric |
h |
|
|
NCHAR |
Unicode |
|
|
|
NTEXT cf |
Long Unicode |
|
|
|
NUMERIC |
Decimal Numeric |
* |
|
|
NVARCHAR |
Unicode |
|
|
|
NVARCHAR(MAX) |
Long Unicode |
2 GB |
|
|
REAL |
Approximate Numeric |
|
|
|
SMALLDATETIME e |
Date/Interval |
|
20 |
|
SMALLINT |
Integral Numeric |
5 |
6 |
|
SMALLMONEY |
Decimal Numeric |
i |
|
|
TEXT cf |
Long Character |
|
|
|
TINYINT |
Integral Numeric |
3 |
3 |
|
UNIQUEIDENTIFIER j |
Character |
36 |
36 |
|
VARBINARY bc |
Binary |
|
* |
|
VARCHAR,CHARACTER VARYING |
Character |
|
d |
|
VARCHAR(MAX) |
Long Unicode |
2 GB |
|
|
XML |
Long Unicode |
2 GB |
|
|
a Could vary in length because the scale can vary from 0 to 7. b Cannot be a distribution key. c Cannot be part of data enhancement, expressions or gate conditions. d For MS SQL Server, <=8000. However, if defined length is greater than 255, column cannot be a distribution key or Expression column. e See the table for the supported range of dates. f For more information, see Distribute long column data. g 10 for IDENTITY. h These values have a DBMS-defined implicit precision of 19 and scale of 4. i These values have a DBMS-defined implicit precision of 10 and scale of 4. j MS SQL Server targets only. See Distribute UNIQUEIDENTIFIER columns. |
User-defined Datatypes
There are cases where Connect CDC discovers new datatypes at runtime, for example, user-defined datatypes in MS SQL Server.
If such datatypes are used, they must be defined at all instances of the DBMS. For example, you cannot have two MS SQL Server instances that have different lists of user-defined datatypes.
Datatypes discovered at runtime are assumed to be compatible with any other datatype. The Connect CDC Director does not prevent you from mapping a user-defined datatype to any datatype supported by Connect CDC. If datatypes are in fact incompatible, you will receive runtime errors. Table registration and data distribution may fail.
To distribute a column that has a user-defined datatype, define the same datatype in the Connect CDC metabase database (omnirep), exactly as it is in the original database. Do this before you register the table in the Connect CDC Director.
If a user-defined datatype is not defined in the metabase, when a request is started and the XML metadata loaded, Connect CDC Kernel fails to populate the SQL metabase.
create table qatest.dbo.UDT (
key1 int primary key,
col1 varchar(20),
col2 udtvc20,
col3 udtvc30,
col4 udtvb20,
col5 udtnum42,
col6 udtnvc20,
col7 udtint);EXEC sp_addType UDTVB20, "varbinary(20)";
EXEC sp_addType UDTNUM42, "numeric(4,2)";
EXEC sp_addType UDTNVC20, "nvarchar(20)";
EXEC sp_addType UDTVC20, "varchar(20)";
EXEC sp_addType UDTVC30, "varchar(30)";
EXEC sp_addType UDTINT, "int";
These datatypes display in the Datatypes column on the Table Properties dialog box, Mapping Tab, as the following example shows:
Similarly, they are displayed on the Table Properties dialog box, Columns Tab.
