Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm selecting from a view in SQL Server. If I right-click on it and choose "Select Top n Rows", it runs and shows me 30 columns. If I open the view in ALTER mode (right-click, Script View As, etc...), highlight the code and run it, I get 50 columns. I should get 50 columns, because I just added 20 new columns to the table feeding the view, and inside the view I'm using a "SELECT * FROM..." statement.

I've refreshed the Views folder, but that didn't help.

Anyone know why the discrepancy, and how to correct it?

share|improve this question

migrated from stackoverflow.com May 28 at 15:50

This question came from our site for professional and enthusiast programmers.

    
Tough call here. First of all, I never use *, which is why I ran into this. I've been given this pre-existing project with some shoddy code and don't have time to do it from scratch. Aaron gave a great answer which is 100% corect, but Tripp gave me the key: The fields of the view are defined when the view is created. Re-creating the view solved it. –  Johnny Bones May 28 at 16:26
    
Re-creating the view isn't a good solution in general though, because you may have other dependencies, you may have set up permissions against the view (typically views are implemented for security reasons, not performance reasons), etc. –  Aaron Bertrand May 28 at 16:34
add comment

3 Answers 3

First, stop using SELECT * in your views. I talk about this quite a bit here:

Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.:

EXEC sp_refreshview N'dbo.viewname';

If you want to generate a script that refreshes all of the views in the system:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  EXEC sp_refreshsqlmodule N''' 
  + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''';'
FROM sys.schemas AS s 
INNER JOIN sys.views AS v 
ON s.[schema_id] = v.[schema_id]; 

EXEC sp_executesql @sql;

(You may want to run it twice in case there are circular or out-of-order dependencies.)

But this is a temporary fix. You will have to repeat this every time you change any table that might have views that reference it, then go find out which views are involved. This is not the right way to approach schema design. AT ALL. I wrote about a DDL trigger that would help automate this for you, but this is really the wrong approach - just stop using SELECT * in your views!

You may also want to consider adding WITH SCHEMABINDING to the definition of all of your views. This does two things:

  1. Prevents you from using SELECT * in the view. This is A GOOD THING™.
  2. Prevents you from changing underlying tables without knowing exactly which views would be affected by the change. This is also A GOOD THING™.
share|improve this answer
    
Is there anything that will refresh all of the views at once? Similar to the way sp_recompile 'tablename' will recompile all SPs that reference tablename. –  Kenneth Fisher May 28 at 16:29
1  
@KennethFisher I don't know of any magic, no. The real solution is to stop using SELECT * in the views in the first place. :-) Pretty trivial, though, to use dynamic SQL to refresh them all at once (see update). –  Aaron Bertrand May 28 at 16:45
add comment

I highly recommend not using SELECT * in code embedded... well... pretty much anywhere. The fields of the view are being defined when the view is created. You need to recreate the view.

Also, look at the accepted answer to Sql Server equivalent to Oracle's CREATE OR REPLACE VIEW which shows how to use ALTER VIEW rather than DROP VIEW and CREATE VIEW in order to do things like preserve permissions.

share|improve this answer
add comment

In SQL Server, views are not bound to the schema of the base tables by default. Try dropping and recreating the view.

share|improve this answer
    
well never give this suggestion .. when you recreates view it will effect the view performance too. –  Dhaval May 28 at 15:43
1  
I don't know how that affects performance directly - if anything, queries against the new copy of the view will likely be more optimal, since they will be based on the current state of the underlying objects. More importantly, dropping the view and re-creating it will blow away any permissions applied to it... –  Aaron Bertrand May 28 at 15:48
    
The only performance issue would be the plan being dropped from the cache. That's probably going to happen either way if 20 fields are being added to the view. The permissions I wasn't aware of, so thanks for pointing that out! –  Dave.Gugg May 28 at 15:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.