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.

view plainprint?

1. -- create a database for testing  
2. CREATE DATABASE TestDatabase;  
3. GO  
4.   
5. -- Connect with the database and create a table  
6. USE TestDatabase;  
7. GO  
8.   
9. CREATE TABLE dbo.Product  
10. (  
11.  Id int PRIMARY KEY  
12.  , Code char(5) NOT NULL  
13.  , Name varchar(50) NOT NULL  
14.  , Color varchar(20) NOT NULL  
15. );  
16. GO  
17.   
18. -- Enable CDC for the database  
19. EXEC sys.sp_cdc_enable_db;  
20. GO  
21.   
22. -- Enable CDC for the table  
23. -- Make sure Agent is running before executing this  
24. EXEC sys.sp_cdc_enable_table   
25.    @source_schema = N'dbo'  
26.  , @source_name   = N'Product'  
27.  , @role_name     = NULL  
28.  , @supports_net_changes = 1;  
29.   
30. -- Inserting records  
31. INSERT INTO dbo.Product  
32. VALUES  
33.   (1, 'P0004', 'Product 1', 'Black')  
34. , (2, 'P0004', 'Product 2', 'White')  
35. , (3, 'P0003', 'Product 3', 'Red')  
36. , (4, 'P0004', 'Product 4', 'Black');  
37. GO  
38.   
39.   
40. --Define the LSN range   
41. DECLARE @from_lsn binary(10), @to_lsn binary(10)   
42. SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Product')   
43. SET @to_lsn   = sys.fn_cdc_get_max_lsn()   
44.   
45. -- Get all the changes for the table   
46. SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product (@from_lsn, @to_lsn, N'all') ;  

-- 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.

view plainprint?

1. -- deleting all records  
2. DELETE dbo.Product;  
3. GO  
4.   
5. --Define the LSN range   
6. DECLARE @from_lsn binary(10), @to_lsn binary(10)   
7. SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Product')   
8. SET @to_lsn   = sys.fn_cdc_get_max_lsn()   
9.   
10. -- Get all the changes for the table   
11. SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product (@from_lsn, @to_lsn, N'all') ;  

-- 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.

view plainprint?

1. -- Inserting records  
2. INSERT INTO dbo.Product  
3. VALUES  
4.   (1, 'P0004', 'Product 1', 'Black')  
5. , (2, 'P0004', 'Product 2', 'White')  
6. , (3, 'P0003', 'Product 3', 'Red')  
7. , (4, 'P0004', 'Product 4', 'Black');  
8. GO  
9.   
10. -- truncating the table  
11. TRUNCATE TABLE dbo.Product;  

-- 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".