How to create a view that refresh automatically

Question

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

Use SoccerDB;
GO
CREATE VIEW ExampleDBaseII
AS
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,
FROM Team
GO

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

Answer 1

Use WITH SCHEMABINDING in the view

CREATE VIEW ExampleDBaseII
WITH SCHEMABINDING 
AS
SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City,
FROM Team T
GO

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)

Answer 2

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.

Answer 3

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.