Truncate the underlying table in a view

Question

I'm currently in a situation where we are creating a "facade" database which basically consists of a set of views which are simply a select from an identically named table in another database. The idea is that the application can be repointed to the facade database with minimal changes to the actual code.

This seems to work ok for inserts, update, deletes, and obviously selects. Unfortunately, some of the stored procedures use TRUNCATE TABLE in places. It's very limited and our plan right now is to just replace that code with a call to a "TRUNCATE" stored procedure which will actually handle the table truncation behind the scenes. Before going forward with that though, I wanted to see if there were any other suggestions on how to handle this.

Thanks for any suggestions or advice!

Answer 1

Your approach (using special stored procedures) is the only way the do it because TRUNCATE TABLE (Transact-SQL) only works on tables, not views. I guess you have a specific reason (faster and uses fewer system and transaction log resources) to use TRUNCATE over DELETE, since you have the DELETEs working on the views. You might be able to do something with a DELETE trigger, and detect if all rows are being removed use a truncate. I think you approach with a stored procedure is the cleanest way to go.