SCHEMABINDING – use or not to use

One developer from my team has made the following suggestion: why not to alter all views we have in ERP system with SCHEMABINDING option? His main argument was: this would help us to set additional control for changes in tables’ structures, thus increasing system stability.

The idea was interesting, so I decided to make some investigation in this sphere to prepare reasoned response.

At the beginning I would like to say what is SCHEMABINDING and how it can be used.

MSDN says: SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. All referenced objects must be in the same database. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition. Taken from http://msdn.microsoft.com/en-us/library/ms187956.aspx.

Now, a few words about database that is expected to change:

  • Database size: 150 Gb;
  • Number of views: 170;
  • Number of tables: about 1650;
  • Number of changes in database structure a week: 7-10 in average.

So, I’ve started to analyze the suggestion, and now I can set the list of advantages and disadvantages.

I’ve placed them into two separate columns with some comments for each point.

Plus Minus
Stability of the system, as view cannot be broken by changes in tables’ structure. It would take more time to make changes in tables’ structure. If I just want to change the size of one column, I will need to modify before and after several views.
SCHEMABINDING can also be applied to functions. The automated test of the application can catch all broken views and functions. This will not increase the amount of developers’ work.
Performance: in functions SCHEMABINDING would avoid the spool operator that is used to protect from underlying data changes. We have about 500 procedures, and they would not have any protection against changes in tables’ structure.

 

To sum up, the implementation of SCHEMABINDING would increase stability and performance of the system, but also increase the amount of developers’ work. I tend to agree that implementing of SCHEMABINDING is a good suggestion. Stability and performance are always on the first place. Also we do not implement huge amounts of database changes, so it will not for sure stop development.

But it would be great if you let me know your ideas and share your experience of using SCHEMABINDING.

About these ads
This entry was posted in Database development, SQL Server, Technology and tagged , . Bookmark the permalink.

2 Responses to SCHEMABINDING – use or not to use

  1. Pingback: Avoid broken Views in SQL Server with Schemabinding | Passion for Coding

  2. Carl Rochlitz says:

    Next to the stability I believe most of the time schemabinding is used to be able to create indexed views.
    But when using this in a ERP context (as is suggested here) one will face the issue that the ERP packages (SAP, Siebel, AX, …) will not tolerate limitations to their data-structure changing prerogative.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s