Does Change Data Capture (CDC) capture TRUNCATE operation?

One of the sessions we had with SQL Server Sri Lanka User Group Meet-up yesterday was on Change Data Capture which is widely used for capturing changes on table modifications. At the end of the session, there were few questions on it for the sepaker, and I was asked a question on it too. The question was "Does CDC capture TRUNCATE operation?".

Considering the factors related to CDC, I gave the answer as "It should be" but later I realized that it may not possible because TRUNCATE does not log records immediately in the log file. In this case, what is the response from CDC for TRUNCATE?

Following code creates a database and a table, and enable CDC. The it inserts records and see how CDC has captured them.

-- create a database for testing
CREATE DATABASE TestDatabase;
GO

-- Connect with the database and create a table
USE TestDatabase;
GO

CREATE TABLE dbo.Product
(
 Id int PRIMARY KEY
 , Code char(5) NOT NULL
 , Name varchar(50) NOT NULL
 , Color varchar(20) NOT NULL
);
GO

-- Enable CDC for the database
EXEC sys.sp_cdc_enable_db;
GO

-- Enable CDC for the table
-- Make sure Agent is running before executing this
EXEC sys.sp_cdc_enable_table 
   @source_schema = N'dbo'
 , @source_name   = N'Product'
 , @role_name     = NULL
 , @supports_net_changes = 1;

-- Inserting records
INSERT INTO dbo.Product
VALUES
  (1, 'P0004', 'Product 1', 'Black')
, (2, 'P0004', 'Product 2', 'White')
, (3, 'P0003', 'Product 3', 'Red')
, (4, 'P0004', 'Product 4', 'Black');
GO


--Define the LSN range 
DECLARE @from_lsn binary(10), @to_lsn binary(10) 
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Product') 
SET @to_lsn   = sys.fn_cdc_get_max_lsn() 

-- Get all the changes for the table 
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product (@from_lsn, @to_lsn, N'all') ;

Now let's delete all records and see how it captures.

-- deleting all records
DELETE dbo.Product;
GO

--Define the LSN range 
DECLARE @from_lsn binary(10), @to_lsn binary(10) 
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Product') 
SET @to_lsn   = sys.fn_cdc_get_max_lsn() 

-- Get all the changes for the table 
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product (@from_lsn, @to_lsn, N'all') ;

Let's insert all records again and truncate the table.

-- Inserting records
INSERT INTO dbo.Product
VALUES
  (1, 'P0004', 'Product 1', 'Black')
, (2, 'P0004', 'Product 2', 'White')
, (3, 'P0003', 'Product 3', 'Red')
, (4, 'P0004', 'Product 4', 'Black');
GO

-- truncating the table
TRUNCATE TABLE dbo.Product;

As you see, truncation is not possible with this table. The error message clearly says that truncate operation is not allowed with CDC enabled tables.

For the question I was asked, the answer I gave should be corrected. The answer should be "truncate operation is not possible with CDC enabled tables".