Careful with VIEWs with SCHEMABINDING in Microsoft SQL Server
Tags: programming, database, mssql
Published: 2022-07-14 09:48:03

---
title: Careful with VIEWs with SCHEMABINDING in Microsoft SQL Server
published: true
date: 2022-07-14 09:47:15 UTC
tags: programming,database,mssql
canonical_url: https://siderite.dev/blog/careful-with-views-with-schemabinding-in-microsoft/
---

  Original URL: https://siderite.dev/blog/careful-with-views-with-schemabinding-in-microsoft

  I was attempting to optimize an SQL process that was cleaning records from a big table. There are a multitude of ways of doing this, but the pattern that I had adopted for the last similar tasks were to delete rows in batches using the [TOP (@rowCount) syntax](https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql#arguments "Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order."). And it had all worked fine until then, but now my "optimization" increased the run time from 6 minutes to 2 hours! Humbled (or more like humiliated) I started to analyze what was going on.

  First thing I did was to [SET STATISTICS IO ON](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql "Causes SQL Server to display information about the amount of physical and logical IO activity generated by Transact-SQL statements. Physical IO is related to accessing data pages on disk and logical IO is related to accessing data pages in memory (data cache)."). Then I run the cleaning task again. And [lo and behold](https://en.wiktionary.org/wiki/lo_and_behold "Fancy for "look and see""), there was a row reporting accessing an object that was not part of the query itself. What was going on? At first I thought that I was using a VIEW somewhere, one that I had thought was a table, but no, there was no reference to that object anywhere. But when I looked for that object is was a view!

  The VIEW in question was a [view with SCHEMABINDING](https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views "This article describes how to create indexes on a view. The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution."), to which several indexes were then created. That explained it all. If you ever attempted to create an index on a view you probably got the error "_Cannot create index on view, because the view is not schema bound_" and then you investigated what that entailed (and probably gave up because of all the restrictions) but in that first moment when you thought "_all I have to do is add WITH SCHEMABINDING and I can index my views!_" it seemed like a good idea. It might even be a good idea for several scenarios, but what it also does is create a reverse dependency on the object you are using. Moreover, if you look more carefully at the Microsoft documentation it says: "The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution." So you may find yourself querying a table and instead the engine queries a view instead!

  You see, what happens is that every time when you delete 4900 rows from a table that is used by a view that has indexes on it is those indexes are being recreated, so not only your table is affected, but potentially everything that is being called in the view as well. If it's a complicated view that integrates data from multiple sources, it will be run after every batch delete and indexed. Again. And again. And again again. It also prohibits you from some operations, like [TRUNCATE TABLE](https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql "Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources."), where you get a funny message saying it's referenced by a view and that is why you can't truncate it. What?!

  Now, I deleted the VIEW and run the same code. It ran faster, but it still took ages because finding the records to delete was a much longer operation than the deletion itself. This post is about this reverse dependency that an indexed view introduces.

  So what is the solution? What if you have the view, you need the view and you also need it indexed? You can [disable the indexes](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints "This topic describes how to disable an index or constraints in SQL Server by using SQL Server Management Studio or Transact-SQL. Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data. The index definition remains in metadata, and index statistics are kept on nonclustered indexes. Disabling a clustered index on a view or a nonclustered index physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.") before your operation, then enable them again. I believe this will solve most issues, even if it's not a trivial operation. Just remember that in cleaning operations, you need some indexes to find the records to delete as well.

  That's it. I hope it helps. Get out of here!