2

After applying my database changes on the test environment, the test team starts testing their processes, one of the things I get is a truncate table error because the table is used in an indexed view.

    (1 row(s) affected)
    ProductIconTypes ending
    ProductImageCategories starting
    Msg 3729, Level 16, State 2, Line 1
    Cannot TRUNCATE TABLE 'dbo.ProductImageCategories' 
because it is being referenced by object 'VWProductImageGetByStyle'.

Questions:

1) next time I want to put an indexed view on one of my databases what exactly I should be checking?

2) Can I get this done through my favourite route (T-SQL)?

I would not mind powershell either.

Unfortunately on these servers in question we are still on SQL Server 2005 AND SQL Server 2008 R2.

A Comment:

These versions impose a lot of limitations on things that we can do as alternatives. Filtered indexes for instance are not available on SQL Server 2005.

I am actually looking for a way if possible to find out what would stop working if I created this indexed view. In my example, the truncate tables could no longer work.

Improve this question
2
  • 1
    Requirement is that the view must be created with WITH SCHEMABINDING. Queries should be using NOEXPAND View Hint. Remember there is a cost of maintaining indexed views ! Read : technet.microsoft.com/en-us/library/dd171921(v=sql.100).aspx
    – Kin Shah
    Commented Oct 4, 2016 at 13:23
  • 1
    One thing you should check is that all connections to the database that perform updates to tables underlying the indexed view have the correct set options.
    – Martin Smith
    Commented Oct 4, 2016 at 15:54

1 Answer 1

Reset to default
4

Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In this case TRUNCATE (a DDL operation) is viewed as view-affecting.

Detecting any queries that would break when you create views with schemabinding would equal detecting queries that execute DDL statements against the underlying tables and that depends on where your queries are coming from, but you basically need to look for ALTER TABLE|VIEW <myobject> for all objects used in your view or TRUNCATE TABLE <mytable>

If all your access is coming from stored procedures you could search them using this method.

If all your queries are generated from your application and you have the source code of it, you could search the source code or source control repository.

If you have a precompiled 3rd party app you could get lucky by using strings which also has a windows build.

If your applications are using ORM like frameworks you could be out of luck.

Improve this answer
1
  • at the end the solution was removing the indexes on the indexed views and altering the stored procedures that were using them - removing the hint WITH(NOEXPAND). In some of the servers I also had to remove the stored procedures involved from the cache. All good now.
    – Marcello Miorelli
    Commented Oct 4, 2016 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.