What is Change Data Capture (CDC) feature?
Submitted by: AdministratorChange Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.
Step 1 - Creation of a database
The database name is MyDataBase
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO
/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
Submitted by: Administrator
Step 1 - Creation of a database
The database name is MyDataBase
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO
/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
Submitted by: Administrator
Read Online MS SQL Server 2008 Job Interview Questions And Answers
Top MS SQL Server 2008 Questions
☺ | What is Table Value Parameters (TVP)? |
☺ | Explain filtered indexes? |
☺ | What is Intellisense? |
☺ | What is Compound Operators? |
☺ | What is MERGE? |
Top New MS SQL Server Categories
☺ | SQL Server Database Administrator Interview Questions. |
☺ | SQL Server Service Broker Interview Questions. |
☺ | SQL Server Locks Interview Questions. |
☺ | SQL Server Constraints Interview Questions. |
☺ | MS SQL Data Mining Interview Questions. |