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!