在sql_sever 中不能truncate某表

truncate table  t_test 訊息 4711，層級 16，狀態 1，第 1 行
無法截斷表 't_test'，因為該表已為複製而發布。 錯誤錶轉自http://technet.microsoft.com/zh-cn/library/cc645613(v=sql.105).aspx 

4711

16

否

無法截斷表 '%.*ls'，因為該表已為複製發布或者已啟用了變更資料擷取。

 查了老半天,有下面一個解決方案,不過好像是08才可以,現在在用05好像有點沒戲...  轉自:http://blog.sqlauthority.com/2009/12/25/sql-server-cdc-and-truncate-cannot-truncate-table-because-it-is-published-for-replication-or-enabled-for-change-data-capture/   « SQL Authority News – Training MS SQL Server 2005/2008 Query Optimization And Performance TuningSQL SERVER – Whitepaper SQL Server 2008 Full-Text Search: Internals and Enhancements »SQL SERVER – CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

December 25, 2009 by pinaldave

Few days ago, I got the great opportunity to visit Bangalore Infosys. Please read the complete details for the event here: SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. I mentioned during the session that CDC is asynchronous and it reads the log file to populate its data. I had received a very interesting question during the session.

The question is as follows: does CDC feature capture the data during the truncate operation? Answer: It is not possible or not applicable. Truncate is operation that is not logged in the log file, and if one tries to truncate the table that is enabled for CDC, it will right away throw the following error.

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table because it is published for replication or enabled for Change Data Capture.

Let us create the scenario that will generate the above error.

/***** Set up TestTable *****/
USE AdventureWorks
GO
-- Create Table
CREATE TABLE dbo.TestTable (ID INT)
GO
-- Insert One Hundred Records
-- INSERT 1
INSERT INTO dbo.TestTable (ID)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
/***** Enable CDC *****/
--The following script will enable CDC in AdventureWorks database.
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO
--Following script will enable CDC on dbo.TestTable table.
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TestTable',
@role_name = NULL
GO
/* Attempt to Truncate Table will thrown following error
Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'TestTable' because it is published for replication or enabled for Change Data Capture.
*/
TRUNCATE TABLE TestTable
GO
/***** Clean up *****/
--Disabling Change Data Capture on a table
USE AdventureWorks;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'TestTable',
@capture_instance = N'dbo_TestTable';
GO
--Disable Change Data Capture Feature on Database
USE AdventureWorks
GO
EXEC sys.sp_cdc_disable_db
GO
-- Drop Table
DROP TABLE TestTable
GO

The workaround for this is to either use DELETE statement instead of TRUNCATE or to disable CDC first and then enable the CDC on the table after the TRUNCATE operation is completed.

I am interested to know if you have any interesting fact of above CDC. I will publish the information on this blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

 

另外還有一篇.

http://www.sqlservercentral.com/blogs/jeffrey_yao/2008/04/22/difference-between-truncate-and-delete/

• Posted on 22 April 2008
• Comments
•   Briefcase
•   Print

This is an old topic and well documented, just google it and I believe you will get a lot,

However, there is one difference that seems not being mentioned and actually I find it out myself only two hours ago. So here is the point

If a table is published for replication, it cannot be truncated, while it can still be deleted.

Otherwise, you will get the following msg

Msg 4711, Level 16, State 1, Line 1

Cannot truncate table 'xxx' because it is published for replication.

My environment is SQL Server 2K5 SP2 (developer edition)

Next time, when you are asked this question in an interview, just give this answer to impress your interviewer. [:)]