Daily Archives: March 3, 2011

Views in Microsoft SQL Server (Part 2 – Schemabinding and things to avoid)

Last week we saw a few potential problems that views can introduce because of the extra IO and joins. Today I’ll show you the biggest “bad practice” with views and the simple way how to avoid it. All of us are lazy and sometimes it really backfires..

Let’s use the same tables (Clients and Orders) we had last week. Let’s add another column to Orders table and create the simplest view.

Now let’s query the view.

Simple, don’t you think? Now let’s add another column to the view and repeat the query.

As you can see, it does not appear in the result set. Now let’s do the opposite action and drop both columns.

As you can see nothing prevents us from doing that. Although, if we run the same select again, we will get the following error.

If we check the catalog views, we can see that Col1 is still there. Unfortunately this is only the part of the problem. You can “recompile” view with sp_refreshview stored procedure but still…

Now let’s do another test. Let’s create another view first and next add and drop columns.

Now, if we run the select again, it would work although results would be completely screwed up. It returns Placeholder data as Amount (dropped column) and new Col1 data under Placeholder column. Can you imagine all wonderful side effects you can have on the client side?

So speaking about that, first rule is never ever use “select *” in the view definition. This is the source of all potential problems. Second, the good practice is to use schemabinding option. With this option SQL Server links the view and underlying schema and does not allow any changes that can break the views.

Source code is available for download