2

Does schemabinding views affect performance in SQL Server or is just to prevent accidental changes?

  • 1
    Schema binding a view will allow you to create index that will speed up data retrival. Also, creating a function with schema binding makes it deterministic. I normally tend to create views with schema binding. – Kin Shah May 9 '16 at 22:43
  • Totally forgot about the ability to create an index after ensuring schemabound that is great! – SheldonH May 9 '16 at 22:47
5

With schemabinding can improve performance in some cases.

I couldn't recall all the details, but found an excellent blog post that goes through this in detail:

With user defined functions that may need to update data. You might benefit from improved performance that would prevent an Eager Table Spool. SQL Server SCHEMABINDING

Edit: Paul White indicated that this doesn't actually impact view table spools just used for joins.

Schema binding definitely adds some additional maintenance when changing schema. This could be considered a good thing, since it prevents underlying schema changes without handling the binding. This doesn't bother me as I'm using Red Gate SQL Compare and it handles this, but if you are not using a tool to generate scripts this could be a factor to consider... (solved by getting a tool to generate the scripts!)

See also Improving query plans with the SCHEMABINDING option on T-SQL UDFs on the Microsoft SQL Programmability & API Development Team Blog.

Additionally, the comment by Kin on your original question brings up a great additional point regarding the potential to index the view once you have schema binding included. This can be considered an additional benefit that might help.

  • I'm user SQL Server Data Tools, so there's no maintenance cost for me. I just need to make sure I'm at least breaking even by using it. – Jonathan Allen May 10 '16 at 4:39
  • 1
    It won't make any difference for a view regarding the table spool. The view is inlined into the query anyway so SQL Server can tell if it references something that would require Haloween protection to be added. – Martin Smith May 10 '16 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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