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:
For those without images enabled (you anarchic luddites), that’s “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.
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.
- 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.
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