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:

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.V1', 'V') IS NOT NULL DROP VIEW dbo.V1;

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;

GO

CREATE TABLE dbo.T1

(

  col1 INT NOT NULL PRIMARY KEY,

  col2 INT NOT NULL,

  col3 NUMERIC(12, 2) NOT NULL

);

INSERT INTO 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

CREATE VIEW dbo.V1 WITH SCHEMABINDING

AS

SELECT col2, SUM(col3) AS total , COUNT_BIG(*) AS cnt

FROM dbo.T1

GROUP BY col2;

GO

CREATE UNIQUE CLUSTERED INDEX idx_col2 ON 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:

TRUNCATE TABLE 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