Something that came out of a recent session I gave at SQL Saturday in Iowa was a discussion on views and the Meta Data that comes along with them. The discussion came about when I had commented, during a session that views were a pain spot for me. Misuse by means of over using them as well as seeing over the years, very little attention to the Meta Data of them. I wanted to give a little more information on the discussion here.

Non-schema Bound Views in SQL Server rely on returning accurate and reliable results based from the underlying metadata. This is mostly in part to dependencies. With Schema-Bound Views this isn’t as much of a concern since underlying objects cannot be altered without errors being generated. This is the definition of binding them together. With Non-schema Bound Views however, this isn’t the case. In fact, we can create views without the underlying objects ever being in existence in some cases. Before we go into views and maintaining them, let’s dig into the underlying object references we just mentioned.

How to look at your dependencies?

In order to review dependencies in SQL Server 2008+, we can use sys.sql_expression_dependencies. This Catalog View returns each dependency on an object created by a user. From BOL, this falls under the following listing

• Schema-bound entities.
• Non-schema-bound entities.
• Cross-database and cross-server entities. Entity names are reported; however, entity IDs are not resolved.
• Column-level dependencies on schema-bound entities.
• Column-level dependencies for non-schema-bound objects can be returned by using sys.dm_sql_referenced_entities
• Server-level DDL triggers when in the context of the master database.

Reference: http://msdn.microsoft.com/en-us/library/bb677315.aspx

Of course seeing this means much more. Let’s take a look at AdventureWorks2008. The view HumanResources.vEmployee as the following definition

Looking at this view’s definition we can see that several columns are referred to from the tables. SQL Server tracks these dependencies between the objects by name.

For example, take the view vEmployee. As the definition shows, the tables Employee, Contact, EmployeeAddress, Address, StateProvince and CountryRegion. Using sys.sql_expression_dependencies this can also be reviewed with a short query.

Resulting in

The same tables listed in the referenceing_id and using the OBJECT_NAME to return the objects name for more meaningful information. Reversing this, in a sense, and adding the sys.objects catalog view, we can start by looking deeper into the dependencies and focus on one of the tables the view is referencing.

We now see that relationship in the results between the table and the view in a reverse reference based on the same dependency view. This can be taken a bit further in looking to the columns that are only EmployeeID.

The results of this query can also be quickly obtained by using sys.objects, sys.schemas and the same sys.dm_sql_referenced_entities. All of these catalog views expose the metadata that has been stored on this view. The above shows the referenced_minor_id a numbering system in the view and relationship to the tables. This is where we get into trouble.
Take this short and the most common problem with this metadata and dependency issue. Run the statements as they are, in order.

The results would be expected to show 2, test2 but they show the opposite, test2, 2. We will talk about the query in the middle of this batch looking at the results of sysdepends in a minute.

So we can see the INT value is in the place holders of the varchar value. Imagine this with calculations being run on this view. The view is replying on the metadata already created basedoff the original ordinal order of the columns in tbl. This exact example is only possible with the use of the wildcard * but other metadata oddities can happen do to the naming resolutions.

Look at the table and view just created with the queries from earlier and verify the dependencies.

Nothing is jumping out until we look at the system view sysdepends and the results that were shown from the batch earlier. Those results are shown below

Now, run the sysdepends check again
e.g. SELECT * FROM sysdepends where id = OBJECT_ID('dbo.vTbl')

WHOA!!! Yes, we have a problem here and actually one of the points where the problem starts with what we tested above. Now we’ve identified this one reaction to the underlying table changes but there are more. Several issues can come from a non-schema bound view and the changes to underlying tables. Mostly the wildcard usage is the worst case practice. Like we said earlier, image calculations being run off the wrong column when the view is using an underlying definition that has been set to calculate things on specific columns. We could fall into sales reporting incorrectly, shipments going to the wrong locations and a mess of other possibilities.

How to protect from this?

In the case where views are used heavily, or even at all, coupled by developers or DBAs having the ability to change and alter anything on the database, get in the habit of refreshing the metadata for views weekly (even nightly if heavy usage is on your database). To do this we can use sp_refreshview. This procedure will force a validation and update to the persistent metadata of the view. Sp_refreshview takes a parameter of the view name to update.

Run the following sp_refreshview and recheck sysdepends

To make this more usable in a large system or where many views are managed, a script like below can be used.

Reference: http://wiki.lessthandot.com/index.php/Sp_refreshview_for_all_views_in_a_database

Closing

Don’t forget the views! They can be overused and become an extreme pain point for a DBA but they can be managed. Now if the view usage becomes to the point you have developers nesting view after view, rethink the designs and how you are obtaining your data.