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?

  • 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. May 28, 2014 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. May 28, 2014 at 16:34

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:


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™.

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 https://stackoverflow.com/questions/163246/sql-server-equivalent-to-oracles-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.


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

  • well never give this suggestion .. when you recreates view it will effect the view performance too.
    – Dhaval
    May 28, 2014 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... May 28, 2014 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, 2014 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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