TSQL Challenge - Table Truncation - 29 Jul 2011

This puzzle is from a customer scenario I had recently. You are working with SQL Server 2008 and have a table called T1 and an indexed view called V1 based on it. Here’s code to create the objects and sample data:

SETNOCOUNT ON;

USEtempdb;

IFOBJECT_ID('dbo.V1','V')IS NOTNULL DROPVIEW dbo.V1;

IFOBJECT_ID('dbo.T1','U')IS NOTNULL DROPTABLE dbo.T1;

GO

CREATETABLE dbo.T1

(

  col1 INT NOTNULL PRIMARYKEY,

  col2 INT NOTNULL,

  col3 NUMERIC(12, 2) NOTNULL

);

INSERTINTO dbo.T1(col1,col2,col3)VALUES

  ( 2, 10,  200.00),

  ( 3, 10,  800.00),

  ( 5, 10,  100.00),

  ( 7, 20,  300.00),

  (11, 20,  500.00),

  (13, 20, 1300.00);

GO

CREATEVIEW dbo.V1WITH SCHEMABINDING

AS

SELECTcol2,SUM(col3)AS total, COUNT_BIG(*)AS cnt

FROMdbo.T1

GROUPBY col2;

GO

CREATEUNIQUE CLUSTEREDINDEX idx_col2ON dbo.V1(col2);

GO

In practice the table can have a very large number of rows (say, hundreds of millions). You are tasked with creating a solution that clears the table T1 very fast. You do not want to use a DELETE statement without a WHERE clause because it’s very slow. You try the following TRUNCATE statement:

TRUNCATETABLE dbo.T1;

But then you get the following error indicating that TRUNCATE isn’t allowed because it’s being referenced by the view:

Msg 3729, Level 16, State 2, Line 1

Cannot TRUNCATE TABLE 'dbo.T1' because it is being referenced by object 'V1'.

Your first thought is to drop the view, truncate the table, then recreate the view, but you are told that it’s not an option because there cannot be even a fraction of a second when the view doesn’t exist. Can you think of a solution for the fast clearing of the table without dropping the view?

I’ll post an entry with the solution next week. In the meanwhile, good luck!

Cheers,

BG