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:

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

 

Discuss this Blog Entry 5

on Aug 1, 2011
Hi Itzik, simply droping index instead of view? drop index dbo.v1.idx_col2 truncate table dbo.t1 create unique clustered index idx_col2 on dbo.v1(col2) marc.
on Jul 31, 2011
Very easy with partitioning: USE [master] GO ALTER DATABASE yourdb ADD FILEGROUP [fg1] GO ALTER DATABASE yourdb ADD FILE ( NAME = N'fg1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\fg1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) GO USE [yourdb] GO CREATE PARTITION FUNCTION [PR1](int) AS RANGE RIGHT FOR VALUES (100) go CREATE PARTITION SCHEME Ps1 AS PARTITION Pr1 TO ([primary],fg1); go CREATE TABLE dbo.T2 ( col1 INT NOT NULL PRIMARY KEY, col2 INT NOT NULL, col3 NUMERIC(12, 2) NOT NULL ) on ps1(col1); go alter table t1 add constraint check_maxvalue check (col1<100) go alter table t1 switch to t2 partition 1 That's all, Cheers, István Sáfár
on Aug 3, 2011
Thanks all for your suggestions; Peso was the first to send the correct solution privately. I posted a new entry describing the solution I was aiming at: http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solution-tsql-table-truncation-challenge-140061. Cheers, BG
on Jul 30, 2011
How important is it that the view V1 reports the correct values throughout the (fast clearing) operation?
on Aug 1, 2011
Can you just alter the view to switch of schema binding and then truncate. After truncate reinstate view with schema binding.

Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×