HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to create a view that refresh automatically

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
createautomaticallyrefreshviewthathow

Problem

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

Solution

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)

Code Snippets

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

Context

StackExchange Database Administrators Q#6834, answer score: 9

Revisions (0)

No revisions yet.