Invalid metadata in views and user defined functions

This week a question on the Twitter #sqlhelp hash tag reminded me of a detail of SQL Server that I learned the hard way, but forgot to blog about.

The question was:

“Is there a good reason to have a SQL view that is just select * from a table with no filtering?”

Why Shouldn’t You Do That?

There are things you should know about how SQL Server handles metadata. When you update the schema for an underlying object (such as adding or removing a column in a table), the metadata for referencing objects is not automatically updated.

This can impact you whether or not you are using SELECT *. However it is far more likely to impact you, and to impact you more widely, when you have a practice of  using SELECT * in your object definitions for views, functions, etc.

Once Upon a Time…

There was a team of people working on optimizing their schema. Columns were being added and removed from several tables. The changes were checked in, tested, and handed off to operations for deployment. The operations DBA verified which changes were to replicated tables and validated that changes were approved by owners of subscriber systems. The change was put through pre-production and the change management system  appropriately.

Thirty minutes after the change went through to production, users of tools on a downstream system began to complain about large volumes of errors in the tool. It was discovered that many databases on the instance with the replication subscriber  had views and table valued functions referencing the article, using the “Select * syntax”. With the removal of the columns, literally hundreds of these views were returning 5402 errors, even though the users didn’t specifically need the columns which had been removed.

After the issue was identified, the operations team was able to resolve the incident by updating the metadata on the views and functions with the sp_refreshsqlmodule stored procedures.

For future schema changes to replicated tables, a script looping through all views and functions was used. This tended to be a bit problematic occasionally, as many users had longrunning queries using the impacted views and functions.

Learn More

To learn more, check out my sample script below, and also these MSDN articles on the stored procedures you use to update metadata:

• sp_refreshsqlmodule: http://msdn.microsoft.com/en-us/library/bb326754.aspx
• sp_refreshview http://technet.microsoft.com/en-us/library/ms187821.aspx

Note that sp_refreshsqlmodule can also be used for views, so you likely want to just standardize with it.

Remember:

• Check your replication subscrbiers: If you’re changing the schema of replicated objects, you may need to update metadata for referencing objects in your subscriber databases, and in other databases on all instances where objects may be referencing the tables by three part names.
• Prepare to be blocked: Your request to update metadata can and will be blocked by running processes using those objects. Make sure to plan for this and determine if and whether you should kill running processes or not to update the metadata.

Why Many People Don’t Know About This

If you’re used to controlling  your data access with stored procedures or ORM tools such as nHibernate, it’s easy to never quite learn this detail, or to forget it.

Stored procedures won’t usually have this issues because they recompile after the schema is changed on the underlying object, which automatically refreshes their metadata.

Alternatives

If you need to reference an object by a different name, create a synonym!

Example Script: views, functions, sprocs, synonyms, and metadata.

Here’s some sql code to walk through some basic examples of how this all behaves.

This script shows a simple example of how schema updates aren’t reflected in the metadata for views and table valued functions unless they are explicitly edited, and it also slows a basic example of a 5402 error.

--Create a base table for testing
create table dbo.LookAtMe (
    i int identity primary key,
    Msg nvarchar(128)
)
GO

--Add a row
insert dbo.LookAtMe (Msg) VALUES('Hi, how are you?')
GO

--Create a view looking at the table using select *
create view dbo.vImLookinAtYou AS
    SELECT *
    FROM dbo.LookAtMe
GO

--Create a table valued function looking at the table with select *
create function dbo.tvfImLookinAtYou ()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM dbo.LookAtMe
)
GO

--Create a procedure looking at the table with select *
create procedure dbo.prcImLookinAtYou
AS
    SELECT *
    FROM dbo.LookAtMe
GO

--create a synonym for dbo.LookAtMe
create synonym dbo.synImLookinAtYou
FOR dbo.LookAtMe

--Our view, function, and proc each return two columns, i and Msg
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
exec dbo.prcImLookinAtYou
GO
select * from dbo.synImLookinAtYou
GO

--Now, let's add a column
alter table dbo.LookAtMe
add lookTime datetime
GO

--Our view and functions still work
--but they aren't returning the new column!
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
--Our procedure, however does return the new column!
exec dbo.prcImLookinAtYou
GO
--Our synonym also lets us see the new column.
select * from dbo.synImLookinAtYou
GO

--Let's refresh the metadata for the view and function
exec sp_refreshview 'dbo.vImLookinAtYou'
GO
exec sp_refreshsqlmodule 'dbo.tvfImLookinAtYou'
GO

--Now we can see the new column in the view and function
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO

--Now let's remove a column from the table
alter table dbo.LookAtMe
drop column lookTime
GO

--Oh no!
--Our view and function both return error 4502:
--"View or function ... has more column names specified than columns defined."
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO
--Our procedure is fine, though
exec dbo.prcImLookinAtYou
GO
--Our synonym is also fine. It's just a pointer.
select * from dbo.synImLookinAtYou
GO

--Let's refresh the metadata for the view and function.
exec sp_refreshview 'dbo.vImLookinAtYou'
GO
exec sp_refreshsqlmodule 'dbo.tvfImLookinAtYou'
GO

--Now they work again.
select * from dbo.vImLookinAtYou
GO
select * from dbo.tvfImLookinAtYou()
GO

--Clean up our objects
drop table dbo.LookAtMe
drop view dbo.vImLookinAtYou
drop procedure dbo.prcImLookinAtYou
drop function dbo.tvfImLookinAtYou
drop synonym dbo.synImLookinAtYou
GO