Supported DDL Operations for Microsoft SQL Server Sources
Supported DDL Operations for Microsoft SQL Server Sources
The Extractor for Microsoft SQL Server sources can process the following DDL operations:
ALTER TABLE
table_name
ADD
column_name
datatype
[IDENTITY] [NULL|NOT NULL] [UNIQUE]
ALTER TABLE
table_name
ADD CONSTRAINT
constraint_name
PRIMARY KEY {CLUSTERED|NONCLUSTERED}
ALTER TABLE
table_name
ALTER COLUMN
column_name
datatype
[NULL|NOT NULL]
ALTER TABLE
table_name
DROP COLUMN
column_name
ALTER TABLE
table_name
DROP CONSTRAINT
constraint_name
CREATE [UNIQUE] [CLUSTERED] INDEX
index_name
ON
table_name
(
column_name
)
CREATE TABLE
table_name
(
column_name
datatype
[IDENTITY] [NULL|NOT NULL] [UNIQUE])
DROP INDEX
index_name
ON
table_name
DROP TABLE
table_name
SELECT * INTO
new_table_name
FROM
existing_table_name
TRUNCATE TABLE
table_name
Data Replication incorrectly replicates a SELECT * INTO operation if the source columns have LOB datatypes such as IMAGE, NTEXT, NVARCHAR(MAX), TEXT, VARBINARY(MAX), and VARCHAR(MAX).
Data Replication can replicate DDL operations that include identity columns. When replicating these DDL operations to Oracle targets, which do not use identity columns, Data Replication replaces identity columns with standard table columns.
Microsoft SQL Server allows TRUNCATE TABLE operations only on tables for which SQL Server Change Data Capture (CDC) is disabled. If you need to truncate a source table from which Data Replication extracts data, you must perform the following steps:
Stop the Extractor task.
Manually disable SQL Server CDC for the table that you want to truncate. Do not disable CDC at the database level.
While SQL Server CDC is disabled, Data Replication does not process DML changes that occur on the table.
Perform the TRUNCATE TABLE operation.
Enable SQL Server CDC again.
Start the Extractor task.
After a CREATE TABLE operation occurs on the source, you must manually enable Change Data Capture for this table to replicate change data to the target.