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

share|improve this question
1  
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

3 Answers 3

up vote 7 down vote accepted

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)

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
1  
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

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.