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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was triying to create a View by the simplest way like this:

Use SoccerDB;
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,

How can I do, so that view keeps its link to the table so if a change the table the view also changes, without creating it again or creating a new one. Is that possible? Im working with Sql Server 2008 R2 thanxs

share|improve this question
I will point out that it is dangerous to cast to varchar without a length. Cast will limit this to the first 30 characters. And if you declare a variable as varchar with no length, the length will be 1. – HLGEM Sep 24 '12 at 17:24
up vote 8 down vote accepted


SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City,

This will disallow any changes to the underling tables that could affect the view

It also requires the use of qualifiers (schema, alias) and disallows the use of SELECT *.
Which is a good thing (SO link)

share|improve this answer
Wow +1 I never knew it prevented select star. That's pretty cool. – Thomas Stringer Oct 15 '11 at 12:08

Not possible unfortunately, you'll have to change the view as the table changes. If you didn't need the casts, you could SELECT * but you really shouldn't.

Edit: It pains me to suggest it but it is possible you could do this with SQL2008+. You could script up a very nasty DDL trigger, to drop and recreate the view if the table changes.

share|improve this answer
yeah indeed, i know i can use Select*, but i must cast. Well the trigger idea is very cool, i may used it, thanxs man!! – Guillelon Oct 14 '11 at 20:59
Although true, you can use WITH SCHEMABINDING to prevent table changes that affect the view – gbn Oct 15 '11 at 14:07
I would most definitely prefer your suggested route of preventing changes but the question was phrased as looking for a way to maintain the view automatically. – Mark Storey-Smith Oct 15 '11 at 20:06
Select * in a view can cause problems in a view if the underlying structure changes. – HLGEM Sep 24 '12 at 17:28

Using a trigger to update the view could be a big performance hit. You should never use triggers unless absolutely necessary. They are monsters that drag your performance down.

When you use WITH SCHEMABINDING you must specify the schema along with the tablename otherwise you get the following error:

Cannot schema bind view 'viewXXX' because name 'tableXXX' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."

If you use two-part-naming and your script has to be run on multiple databases you will run into a problem when there are different Schemas across databases. In that scenario you have to use dynamic SQL to create the scripts.

So, if you're dealing with just one database then use the tablename with the schema like Schema.TableName. When I says two-part-naming this is what I mean.

share|improve this answer

Not automatic, but you could use sp_refreshview, either for one specific view, or even for all views having dependencies on a given object. See sample code here.

share|improve this answer

Your Answer


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.